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



"Peter Jamieson" wrote in message
...
: 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" wrote in message
: news:1113522089.4c172862e624f7319657b98b23356b2d@t eranews...
: 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
:
:
:
: