Offen I got calls from customers asking me to mail merge excel files with calculated data on one excel file and data feed on another sometimes with printed area on other workbook of template excel file. The best way to accumplish this task would be creating micro or small UI form with vba code accessing second excel file with ADO (ActiveX data object). In order to that we need to add reference to ADO object in VBA. Click on tools and references on menu. See screenshot below how I did it.
After that we would need to write vba micro which will access data feed excel file and read line by line into appropriate excel cells then print workbook3 selected range. See code below.
Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim rsT As New ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rsC As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer
Set cn = New ADODB.Connection
With cn
.Provider = “Microsoft.Jet.OLEDB.4.0″
.ConnectionString = “Data Source=c:\ProspectList.xls;Extended Properties=Excel 8.0;”
.CursorLocation = adUseClient
.Open
End With
rsT.Open “select * from [sheet1$];”, cn
Do Until rsT.EOF
Worksheets(1).Range(“C16″) = rsT.Fields(3).Value
Worksheets(1).Range(“C18″) = rsT.Fields(4).Value
Worksheets(1).Range(“C19″) = rsT.Fields(5).Value & ” ” & rsT.Fields(6).Value & ” ” & rsT.Fields(7).Value
Worksheets(1).Range(“E89″) = rsT.Fields(11).Value
Worksheets(1).Range(“C30″) = rsT.Fields(23).Value
Worksheets(1).Range(“C27″) = rsT.Fields(24).Value
Worksheets(3).Range(“A1:J248″).Select
Selection.PrintOut Copies:=1, Collate:=True
rsT.MoveNext
Loop
rsT.Close
cn.Close
Set rsT = Nothing
Set cn = Nothing
End Sub
The last step is to let form open on start. We need to add one line of code in ThisWorkbook
UserForm1.Show
I hope this post give clear idea how to merge 2 or more excel files and send output to printer.
