Mail Merge SQL Syntax, variable

[corquando]

Howdy, all.

Hope this is an easy fix . . .

AppWd.ActiveDocument.MailMerge.OpenDataSource Name:= _
    "G:\DATA\Huge\Medium\Small\MyWorkbook.xlsm" _
    , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
    WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
    Format:=wdOpenFormatAuto, Connection:= _
    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\DATA\Huge\Medium\Small\MyWorkbook.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet" _
    , SQLStatement:="SELECT * FROM 'SheetNaem$'", SQLStatement1:="", SubType:= _
    wdMergeSubTypeAccess

Just wondering how to change the value "SheetNaem$" into a variable in the line

SQLStatement:="SELECT * FROM 'SheetNaem$'",

I have 3 different worksheets that 10 different templates need to draw from, and compiling the data in any other way won't work since upstream folder designations play a part in deciding which template is accessed. So there.

Thanks in advance - this is still the coolest site ever.

 

[xenou]

Assuming you can get the correct sheet names ...

[COLOR="RoyalBlue"]strSheetName [/COLOR]= "Sheet1"


AppWd.ActiveDocument.MailMerge.OpenDataSource Name:= _
    "G:\DATA\Huge\Medium\Small\MyWorkbook.xlsm" _
    , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
    WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
    Format:=wdOpenFormatAuto, Connection:= _
    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\DATA\Huge\Medium\Small\MyWorkbook.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet" _
    , SQLStatement:="SELECT * FROM '" & [COLOR="RoyalBlue"]strSheetName [/COLOR]& "$'", SQLStatement1:="", SubType:= _
    wdMergeSubTypeAccess

For instance, if the active sheet were the correct sheet:

[COLOR="RoyalBlue"]strSheetName [/COLOR]= ActiveSheet.Name


AppWd.ActiveDocument.MailMerge.OpenDataSource Name:= _
    "G:\DATA\Huge\Medium\Small\MyWorkbook.xlsm" _
    , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
    WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
    Format:=wdOpenFormatAuto, Connection:= _
    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\DATA\Huge\Medium\Small\MyWorkbook.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet" _
    , SQLStatement:="SELECT * FROM '" & [COLOR="RoyalBlue"]strSheetName [/COLOR]& "$'", SQLStatement1:="", SubType:= _
    wdMergeSubTypeAccess

Actually, we assume that the sheet names are changing but the name and filepath of the workbook they are in is the same. Otherwise, it is necessary to tinker with the file path in the connection string also...

 

[corquando]

Thanks! I had tried various combinations of single- and double-quotes, but there are probabaly a few dozen. I will emulate that and get back to work.

Thanks again!

Coolest.
Site.
Ever.

 

[xenou]

My preference is to never put spaces in file or folder names. Use underscores and camel casing or pascal casing, or combination thereof:
e.g.:

My_File_To_Be_Queried.xls

myFileToBeQueried.xls

MyFileToBeQueried.xls

MyFile_ToBeQueried.xls

This probably looks odd to the average office worker but it looks perfectly normal to me! Tab names should also not have spaces in them, if you want to simplify the syntax. In any case, this is how I treat files and folders that are (or may be) used as data sources, linked to other files, or used in vba procedures. Though it's irrelevant, I also try to stick to letters, numbers, underscores, hyphens, and periods in file names, as these seem to be supported across different OS's without difficulties (so far as I can tell).

Since I use different OS's and programming languages at different times it just helps me to keep my sanity this way - no worries about how spaces are handled in my names.

 

[corquando]

Cool - I use SQL code all the time in my SAS jobs, so I'm used to the underscore part. I just wasn't sure how to transplant the SQL statement into vba since it's rare I use them in tandem like that.

It's certainly a good habit to get into. The tricky part is forgetting to do that when you're sending an email home to the wife. She gets confused easily anyway, so it's been an occasional adventure . . . and sanity is always good. It does tend to get low at times.

Thanks for all the great advice - I brag about this site to the point of being obnoxious sometimes.