VB for mailmerge that worked in 97

[Micah]

I changed computers and upgraded from Office 97 to Office 2003. The
following lines worked in a Excel 97 document and will not run in 2003. Any
suggestions would be appreciated.

With AppFile
'Changes the Current Directory.
.ChangeFileOpenDirectory "G:\Capital Projects\Invoice Cover
Sheets\"
'Opens the desired file from Current Directory.
.Documents.Open Filename:="""PPR Template.doc""",
ConfirmConversions:=False, ReadOnly:=False, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="",
Format:=wdOpenFormatAuto
'Runs MailMerge to fill in CONSTRUCTION DATA.
ActiveDocument.MailMerge.OpenDataSource Name:= _
"G:\Capital Projects\Invoice Cover Sheets\Current
Projects\Construction\Payment Management.xls" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=G:\Capital Projects\Invoice Cover Sheets\Current
Projects\Engineering\Payment Management ENG.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:" _
, SQLStatement:="SELECT * FROM `'Monthly Payment$'` WHERE `Payment
Due` <> 0", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
'Shows the Data rather than Fields.
.ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
'Merge the Data to the printer.
.ActiveDocument.MailMerge.Destination = wdSendToPrinter
.ActiveDocument.MailMerge.SuppressBlankLines = True
.ActiveDocument.MailMerge.DataSource.FirstRecord =
wdDefaultFirstRecord
.ActiveDocument.MailMerge.DataSource.LastRecord =
wdDefaultLastRecord
.ActiveDocument.MailMerge.Execute Pause:=False
End With

The error occurs with the following statement
WHERE `Payment Due` <> 0",

 

[Peter Jamieson]

Unfortunately, the OpenDataSource code that Word generates when you record
your macros does not always work. In this case I can see the following
potentiqaol problems...

The pathname you have in the Name parameter of OpenDataSource:

G:\Capital Projects\Invoice Cover Sheets\Current
Projects\Construction\Payment Management.xls

is different from the one you have in the Connection string:

G:\Capital Projects\Invoice Cover Sheets\Current
Projects\Engineering\Payment Management ENG.xls

The Connection String is incomplete (not obvious). But in this case I do not
think you need it anyway.

What I suggest you try is

ActiveDocument.MailMerge.OpenDataSource _
Name:="G:\Capital Projects\Invoice Cover Sheets\Current
Projects\Construction\Payment Management.xls", _
SQLStatement:="SELECT * FROM [Monthly Payment$] WHERE [Payment due] <> 0"

You obviously need to use the correct pathname in the Name parameter. I tend
to use [ ] rather than ` ` to surround names in the SQL starement simply
because it makes things clearer. If that does not work, try using a table
alias, e.g.

ActiveDocument.MailMerge.OpenDataSource _
Name:="G:\Capital Projects\Invoice Cover Sheets\Current
Projects\Construction\Payment Management.xls", _
SQLStatement:="SELECT * FROM [Monthly Payment$] MP WHERE MP.[Payment due] <>
0"

although tests here suggest you do not need one.