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
Link Label doc to Excel
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Link Label doc to Excel
Am I correct in thinking that you want to use an Excel worksheet as the data source for a mailmerge to labels?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 35
- Joined: 20 Feb 2010, 05:05
Re: Link Label doc to Excel
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
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Link Label doc to Excel
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.
(I recorded a macro and streamlined the code a bit)
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
Best wishes,
Hans
Hans