MailMerge.OpenDataSource not able to access a dynamic range name ?

[Bruce Cooley]

In Word 2002 I am automating a MailMerge that grabs label info from a named
range in an Excel 2002 file. As long as my range name "LabelData" looks
like this:

='Label Data'!$A$1:$I$75

this single line of code works beautifully:

ActiveDocument.MailMerge.OpenDataSource Name:=PFRGeneratorPathFileName,
Connection:="LabelData", SQLStatement:="SELECT * FROM `LabelData`",
SQLStatement1:=""

However, if I use a dynamic range name like this:

=OFFSET('Label Data'!$A$1,0,0,1+CountRespondents,9)

then the OpenDataSource resorts to a "Select Table" dialog, and even after I
choose the range name, Word fails to access the data.

Since I need this mail merge to grab however many names are in the Excel
file at that time, the dynamic range would be perfect. Am I asking for too
much? Are there any other ways to get around this?

Also, does anyone know why the SQLStatement in the OpenDataSource method
above requires those particular back-slanted "single-quote" marks that came
out of the macro recorder, when it won't work if I substitute ordinary
single quotes?

Thanks in advance,

Bruce

 

[Peter Jamieson]

Just guessing really as I'm not very familiar with Excel, but I suspect that
the reasons that this will never work however you try to get Word to connect
to Excel are that
a. it is Excel that understands how to resolve an OFFSET function
b. the only connection method Word has that actually uses Excel to get its
data (rather than just accessing data stored in the .xls file) is DDE.
c. However, as far as I know there is no way to specify an Excel /function/
in any of the parameters of the OpenDataSource method.

Am I asking for too
much?

Not in my view, but that doesn't mean to say that there is a simple way to
do what you want.

Are there any other ways to get around this?

A few things you might try:
a. use OpenDataSource to open the whole source, then manipulate
Activerecord and use the DataSource object to inspect the data before you
actually merge. Then specify the start and end records you need.
b. ensure the data source is closed, then open it using ADO and establish
the start and end records. then proceed as in (a), or if possible (can't
remember off the top of my head) issue an OpenDataSource with a range )not a
range name) constructed from the star/end record information you established
c. some combination of (a) and (b)

Also, does anyone know why the SQLStatement in the OpenDataSource method
above requires those particular back-slanted "single-quote" marks that
came
out of the macro recorder, when it won't work if I substitute ordinary
single quotes?

I believe it's because the back-slanted quotes are there to surround /names/
(table names, column names etc.) whereas the "straight" single quotes are
there to surround literal strings that are returned as data. When you get
data from Excel using either ODBC or OLEDB, the SQL dialect being used is
the Jet dialect, and as I understand it, when table/column names are used,
you can
a. leave them unquoted, but only if they do not contain special characters
such as spaces
b. quote them using back-single quotes ` `
c. quote them using square brackets [ ]

Peter Jamieson

 

[Bruce Cooley]

Thanks for your suggestions, Peter. You've provided me with some
understanding and a direction to go in, although some of it's new to me and
I would definitely have to do some research. I didn't even consider the
idea that I was trying to get Word to think like Excel.

Before I try what you suggest, I am going to go back over to the Excel side
and try using Excel VBA to assign a fixed range name from a string that is
created by looking at the range size. If I can somehow force Excel to show
='Label Data'!$A$1:$I$75 (or whatever it happens to be at the moment) as the
range name it should work.

If I can't figure this out soon, I may have to be realistic and think about
how
many seconds it will take me to assign a range name manually each time I
have a new set of data. Maybe 10 seconds? I wonder how many hours I've
spent on this already trying to be a VBA purist....

Bruce



: Just guessing really as I'm not very familiar with Excel, but I suspect
that
: the reasons that this will never work however you try to get Word to
connect
: to Excel are that
: a. it is Excel that understands how to resolve an OFFSET function
: b. the only connection method Word has that actually uses Excel to get
its
: data (rather than just accessing data stored in the .xls file) is DDE.
: c. However, as far as I know there is no way to specify an Excel
/function/
: in any of the parameters of the OpenDataSource method.
:
: > Am I asking for too
: > much?
:
: Not in my view, but that doesn't mean to say that there is a simple way to
: do what you want.
:
: Are there any other ways to get around this?
:
: A few things you might try:
: a. use OpenDataSource to open the whole source, then manipulate
: Activerecord and use the DataSource object to inspect the data before you
: actually merge. Then specify the start and end records you need.
: b. ensure the data source is closed, then open it using ADO and establish
: the start and end records. then proceed as in (a), or if possible (can't
: remember off the top of my head) issue an OpenDataSource with a range )not
a
: range name) constructed from the star/end record information you
established
: c. some combination of (a) and (b)
:
: > Also, does anyone know why the SQLStatement in the OpenDataSource method
: > above requires those particular back-slanted "single-quote" marks that
: > came
: > out of the macro recorder, when it won't work if I substitute ordinary
: > single quotes?
:
: I believe it's because the back-slanted quotes are there to surround
/names/
: (table names, column names etc.) whereas the "straight" single quotes are
: there to surround literal strings that are returned as data. When you get
: data from Excel using either ODBC or OLEDB, the SQL dialect being used is
: the Jet dialect, and as I understand it, when table/column names are used,
: you can
: a. leave them unquoted, but only if they do not contain special
characters
: such as spaces
: b. quote them using back-single quotes ` `
: c. quote them using square brackets [ ]
:
: Peter Jamieson
:
: : > In Word 2002 I am automating a MailMerge that grabs label info from a
: > named
: > range in an Excel 2002 file. As long as my range name "LabelData" looks
: > like this:
: >
: > ='Label Data'!$A$1:$I$75
: >
: > this single line of code works beautifully:
: >
: > ActiveDocument.MailMerge.OpenDataSource Name:=PFRGeneratorPathFileName,
: > Connection:="LabelData", SQLStatement:="SELECT * FROM `LabelData`",
: > SQLStatement1:=""
: >
: > However, if I use a dynamic range name like this:
: >
: > =OFFSET('Label Data'!$A$1,0,0,1+CountRespondents,9)
: >
: > then the OpenDataSource resorts to a "Select Table" dialog, and even
after
: > I
: > choose the range name, Word fails to access the data.
: >
: > Since I need this mail merge to grab however many names are in the Excel
: > file at that time, the dynamic range would be perfect. Am I asking for
: > too
: > much? Are there any other ways to get around this?
: >
: > Also, does anyone know why the SQLStatement in the OpenDataSource method
: > above requires those particular back-slanted "single-quote" marks that
: > came
: > out of the macro recorder, when it won't work if I substitute ordinary
: > single quotes?
: >
: > Thanks in advance,
: >
: > Bruce
: >
: >
:
: