Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MailMerge.OpenDataSource not able to access a dynamic range name ?
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 : : : : |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I Mail Merge from an Access Query? | Mailmerge | |||
why word 2000 mail merge opens multiple instances of access table. | Mailmerge | |||
Word 97 opening Access 2000 | Mailmerge | |||
Mailmerge data source in an Access database will not open | Mailmerge | |||
Word mail merge with Access | Mailmerge |