Using mailmerge code in Word 2007

[TFTAJLLYMXZP]

I'm trying to "update" my Word 2003 data connection code to Word 2007,
but I cannot connect to my source file. I'd appreciate any hints on
what's wrong. My data source file is a macro-enabled workbook (.xlsm)
with a single worksheet named "Parcels". Within that sheet, there is
a range named "Database" that includes the header row (Row 6,
actually) and all the data rows below it.

The code breaks on the MailMerge.OpenDataSource statement and throws
up a "Confirm data source" dialog that displays the single entry of
"OLE DB Database Files".

Anyone else getting this problem?

Here is the code:

Public Sub DoPackageMerge(gstrPackageDataSource As String)

Dim mrgPkg As MailMerge
Dim rngHeaderFooter As Range
Dim strSQL As String
Dim strSaveAs As String
Dim j As Integer
Dim lngError As Long
Dim strMsg As String

On Error GoTo Package_Err

Set mrgPkg = ActiveDocument.MailMerge
mrgPkg.MainDocumentType = wdCatalog

With mrgPkg

.OpenDataSource _
Name:=gstrRoot & Application.PathSeparator &
gstrPackageDataSource, _
AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"User ID=Admin;" & _
"Data Source=" & gstrRoot & Application.PathSeparator
& gstrPackageDataSource & ";" & _
"Mode=Read;" & _
"Extended Properties=""HDR=YES;IMEX=1;"";" & _
"Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";" & _
"Jet OLEDB:Engine Type=37;" & _
"Jet OLEDBatabase Locking Mode=0;" & _
"Jet O", _
SQLStatement:="SELECT * FROM `Parcels$Database`", _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
. . .
.. . .
End Sub

Thanks,
Terry

 

[TFTAJLLYMXZP]

Within that sheet, there is
a range named "Database" that includes the header row (Row 6,
actually) and all the data rows below it.

Correction: the 'Database' range is now named 'DataList', since I was
noticing during some manual mail merge setups, that the range named
'Database' was showing up as '_xlnmDatabase' in the mail merge dialog
asking for the data source. The word 'DataList' appears as it should.

 

[TFTAJLLYMXZP]

On Mar 28, 3:48 pm, (e-mail address removed) wrote:

Update (Connection works, but subquery fails):

I can now successfully connect to my Excel data source with the
following syntax in the Connection string of the
MailMerge.OpenDataSource method:

Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"User ID=Admin;" & _
"Data Source=" & gstrRoot & Application.PathSeparator
& gstrPackageDataSource & ";" & _
"Mode=Read;" & _
"Extended Properties=""HDR=YES;IMEX=1;"";" & _
"Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";" & _
"Jet OLEDB:Engine T", _
SQLStatement:="SELECT * FROM `DataList`"

Later in my code, I use the QueryString method of the DataSource
object to filter the results obtained by the code above. Again, the
issue seems to boil down to syntax, but all the variations I've tried
have failed with Error 4198. Here's the code I want to use:

strSQL = "SELECT PARCEL, MAP1, MAP2, MAP3, MAP4 " & _
"FROM `DataList` " & _
"WHERE (`" & gastrAgency(j, 0) & "` In ('y','Y'))"
With ActiveDocument.MailMerge.DataSource ' of mrgPkg
object
.QueryString = strSQL

What I found by building the SQL step-by-step is that the WHERE clause
is causing the #4198 error. The syntax you see here is what worked in
Office 2003 when I had used a DSN to connect with the workbook, but
the new ACE connector seems to want a different syntax. Anyone know
what syntax it would expect under the circumstances>?

Thanks for your time,

Terry

 

[Peter Jamieson]

1. There may still be a way to do this using querystring but I have to
say that I tend to do a .close and re-open the data source using the
query I want.

2. Although you should not have to do this, Word (or the "Office Data
Source Object" that it uses) often needs you to alias the tablename,
when you select individual field names rather than using *, and perhaps
in other circumstances. e.g. try the following string in an Opendata source



"SELECT [DL].PARCEL, [DL].MAP1, [DL].MAP2, [DL].MAP3, [DL].MAP4 " & _
"FROM `DataList` [DL]" & _
"WHERE (`" & gastrAgency(j, 0) & "` In ('y','Y'))"

and you may need to qualify gastrAgency as well, e.g.

"SELECT [DL].PARCEL, [DL].MAP1, [DL].MAP2, [DL].MAP3, [DL].MAP4 " & _
"FROM `DataList` [DL]" & _
"WHERE ([DL].`" & gastrAgency(j, 0) & "` In ('y','Y'))"

Peter Jamieson

http://tips.pjmsn.me.uk

 

[TFTAJLLYMXZP]

On Mar 29, 10:43 am, Peter Jamieson <[email protected]>
wrote:

Peter, thanks very much for your ideas.

1. There may still be a way to do this using querystring but I have to
say that I tend to do a .close and re-open the data source using the
query I want.

No doubt from past experience bashing your fists against the screen.

To echo your comments, you shouldn't have to do this, either. Having
discovered the QueryString method in the documentation some years ago,
it never would have occurred to me to try the .close/.reopen
datasource approach. Thank you for bumping me out of my rut.

In the end, this approach works for me, and I can now stop gnashing my
teeth over QueryString syntax. As an added bonus, the performance
seems about the same.

Thanks again, Peter.

 

[Peter Jamieson]

To echo your comments, you shouldn't have to do this, either.

Agreed. Although Querystring sometimes appears not to complain if you
simply re-execute the same query with a more restricted column list, it
does not actually appear to do anything.

I forgot to mention some stuff that may help if you like to remove
unnecessary stuff from your code. When you are doing an OLE DB
connection like this, for Excel .xlsx, you ought to be able to reduce
your OpenDataSource so it only has the Name parameter and SQLStatement
parameter(s).
a. When you specify a .xslx in the Name parameter and leave out
Subtype, Word/ODSO will construct its own connection string anyway. It
may use some of your string (e.g. the provider name) if you supply it -
I don't know, but you can verify that it ignores your Data Source info.
by putting some rubbish in there and checking .Datasource.Connectstring
afterwards. To get Word/ODSO to use /your/ connectstring you have to
connect to a .odc and put your string in the Connection parameter or in
the .odc. And even then, Word/ODSO may truncate your string. AFAIK the
main way to alter this bhaviour is to specify a Subtype of
wdMergeSubtypeWord2000, but even that may not do anything useful with a
..xlsx
b. If you do use your own Connection string and you start with the one
you get from .DataSource.Connectstring, bear in mind that it's probably
truncated. That does not usually seem to do any harm but I can imagine
that an unlucky truncation might do the wrong thing. But in any case, a
lot of the values in the ConnectString are probably irrelevant for most
Excel connections
c. Most of the other parameters Word records in an OpenDataSource are
are not honoured at all, or not honoured for data sources that are not
Word documents.

Peter Jamieson

http://tips.pjmsn.me.uk