Macro For Merge and Filter

[Bob Collin]

I recently acquired Office 2003 Professional and have been trying to transfer
VB code from an older version of Office. A thread in this discussion group
helped me to get it running manually (many thanks) but the code still
doesn't work. I suspect it's the DDE conversion for the Excel worksheet
because when I record the steps in a macro and play it back I get a runtime
error when the program hits the filtering command. How do I get the
conversion to take place in Visual Basic code?

 

[Peter Jamieson]

1. Which version of Word/Office were you running before?

2. Do you mean VB or VBA?

3. Does your VB app. set up the mail merge data source (i.e. using
OpenDataSource) or is it already set up in the document, and are you just
setting MailMerge.DataSource.QueryString to apply the filtering?

4. Do you /want/ your connection to be via DDE or would you prefer it to be
via OLEDB (say) if that is a feasible option?

Apart from everything else, you will almost certainly need to apply the
following KB article unless you have had to do it before:

http://support.microsoft.com/?kbid=825765

Can you please post your OpenDataSource code (if any)?

Peter Jamieson

 

[Bob Collin]

Here are the answers to your questions:
1. The old versions are Word 2000 and Excel 2000. The new versions are in
Office 2003.
2. VBA
3. 'OpenDataSource' is used followed by 'Query'.
4. I would prefer to use the DDE conversion to keep the Office 2003
consistent with the the 2000 version. Baically I am trying to set up the
merge system at home(on Office 2003) so that I can make modifications in the
code that I can then bring into the version that is in use in the office
(Office 2000).
5. The code arouond the merge and filter operations is as follows:

ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\Owner\My Documents\My Documents\Red
Cross\Shelters\Work Area\MasterForMerge.xls" _
, ConfirmConversions:=True, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet",
SQLStatement _
:="", SQLStatement1:="", SubType:=wdMergeSubTypeOther
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM C:\Documents and Settings\Owner\My Documents\My
Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls WHERE
((ACTIVE__INACTIVE = 'ACTIVE') AND (TYPE_________CTcountyCOCooperativeFO =
'SD') AND (RENEWAL_MAILED IS NULL ) AND (UPDATE_STATUS" _
& " = 'UPDATE NOW'))"

When I try to run this I get a runtime error 4198.

Bob Collin

 

[Peter Jamieson]

Hi Bob,

you will need to do/check this, as mentioned before:

Apart from everything else, you will almost certainly need to apply the
I think you will need to change your OpenDataSource - off the top of my
head, you will probably need:

ActiveDocument.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\Owner\My Documents\My Documents\Red
Cross\Shelters\Work Area\MasterForMerge.xls", _
Connection:="Entire Spreadsheet", _
SQLStatement:="", _
SubType:=wdMergeSubTypeWord2000

to open using DDE. Word should use the SQL statement

SELECT * FROM C:\Documents and Settings\Owner\My Documents\My Documents\Red
Cross\Shelters\Work Area\MasterForMerge.xls

at that point. You should either be able to use exactly the same QueryString
assignment as you had before, or put the same query in the SQLStatement
parameter of the OpenDataSource if you prefer (if the SQL is longer than 255
characters, split it in two and put the first part in SQLStatement and the
second in SQLStatment1 so that the two strngs concatenated are exactly the
SQL string you want (i.e. be careful not to add/delete significant spaces).

Peter Jamieson

 

[Bob Collin]

I read the article you recommended. It scared me off from the DDE approach so
now I'll look into the other way you mentioned (OLEDB) and probably get back
after I've studied up a bit. Thanks for your help

 

[Peter Jamieson]

Hi Bob, unfortunately the article is relevant whichever connection method
you use - the trouble is that any VBA OpenDataSource will fail if you don't
apply it.

Peter Jamieson