Link Label doc to Excel

[Piglette]

I have some kludgy AutoHotkey code that uses sendkeys-type commands to link a Word 2007 label template doc to an Excel spreadsheet ("Opening this document will run the following SQL command" - passes a Y, passes the spreadsheet path/name, passes an "m" for Mailings and a "p" for Preview).

How would you accomplish the same result using VBS objWorksheet.QueryTables.Add or equivalent?

Thanks

Lukas

[HansV]

Am I correct in thinking that you want to use an Excel worksheet as the data source for a mailmerge to labels?

[Piglette]

Hans: That is exactly correct.

I have a label doc template already set up.
Manually: I would open it - get the SQL prompt mentioned above - Click Yes - navigate to the spreadsheet - the doc opens - then click Mailings>Preview Result
and voila - the data springs into view.
My code does the same thing using the corresponding keyboard actions, which works 99.9% of the time, but is not the right way to do it.

Thanks

Lukas

[HansV]

Here is a macro you could run from within Word, while the label document/template is open.
Modify the path and filename of the workbook and worksheet at the beginning of the macro as needed.

Code: Select all

Sub OpenSource()
  Const strSource = "C:\Excel\MyWorkbook.xlsx"
  Const strSheet = "Sheet1"
  ActiveDocument.MailMerge.OpenDataSource _
    Name:=strSource, _
    LinkToSource:=True, AddToRecentFiles:=False, _
    Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
      "Data Source=" & strSource & ";" & _
      "Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";" & _
      "Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" & _
      "Jet OLEDB:Engine Type=37;Jet OLEDB:Datab", _
    SQLStatement:="SELECT * FROM `" & strSheet & "$`", _
    SubType:=wdMergeSubTypeAccess
  ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
End Sub

(I recorded a macro and streamlined the code a bit)