Macro For Merge and Filter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Collin - 17 Sep 2006 20:50 GMT

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?

Signature

Bob Collin

Reply to this Message

Peter Jamieson - 18 Sep 2006 09:14 GMT

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

>I recently acquired Office 2003 Professional and have been trying to
>transfer
[quoted text clipped - 5 lines]
> error when the program hits the filtering command. How do I get the
> conversion to take place in Visual Basic code?

Reply to this Message

Bob Collin - 18 Sep 2006 16:11 GMT

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

> 1. Which version of Word/Office were you running before?
>
[quoted text clipped - 25 lines]
> > error when the program hits the filtering command. How do I get the
> > conversion to take place in Visual Basic code?

Reply to this Message

Peter Jamieson - 18 Sep 2006 17:16 GMT

Hi Bob,

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

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

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

> Here are the answers to your questions:
> 1. The old versions are Word 2000 and Excel 2000. The new versions are in
[quoted text clipped - 62 lines]
>> > error when the program hits the filtering command. How do I get the
>> > conversion to take place in Visual Basic code?

Reply to this Message

Bob Collin - 19 Sep 2006 01:04 GMT

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

Signature

Bob Collin

> Hi Bob,
>
[quoted text clipped - 95 lines]
> >> > error when the program hits the filtering command. How do I get the
> >> > conversion to take place in Visual Basic code?

Reply to this Message

Peter Jamieson - 19 Sep 2006 02:21 GMT

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

>I read the article you recommended. It scared me off from the DDE approach
>so
[quoted text clipped - 118 lines]
>> >> > error when the program hits the filtering command. How do I get the
>> >> > conversion to take place in Visual Basic code?

Reply to this Message

Rate this thread: