My first Word Macro

[bcjc18433]

Any one know what this won't work?
I have a .xlsm file that stores the source for my merge.
I connect to it using the "Select Recipients --> Use Existing List"
option on the "Mailings" Ribbon.
Connection works great.

But when I run the code below. I lose the connection to the .xlsm file
after getting "Run-time Error '4198': Command Failed"

ActiveDocument.MailMerge.DataSource.QueryString = "SELECT * FROM C:
\Documents and Settings\brian_summers\Favorites\Links\Account Tools\My
Space Files\Mail Merge Files\All Accounts (Excel 2007).xlsm WHERE
(rep_id = 12345)"

Thanks in advance,

Brian

 

[Peter Jamieson]

Word has several ways to connect to an Excel sheet. By default in Word 2007
it uses OLE DB. If you have already connected using that method then
a. I don't think you will be able to change the name of the workbook using
Querystring, if that is one of the things you are doing
b. your query probably needs to specify the sheet or names range name,
rather than the workbook pathname, e.g.

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM [mysheetname$] WHERE (rep_id = 12345)"

sustituting your sheetname for mysheetname$

However, I can't check here. It might help if you could use the VBA editor's
immediate window to issue the following commands /before/ you try to change
anything:
print ActiveDocument.MailMerge.DataSource.Name
print ActiveDocument.MailMerge.DataSource.ConnectString
print ActiveDocument.MailMerge.DataSource.QueryString

You may also eventually have to take account of the following article:

http://support.microsoft.com/kb/825765

Peter Jamieson