Looking at your post in the "beginners" forum, one problem is that you're
using
Activedocument.Mailmerge.OpenDataSource (filename,0)
when VBA wants
Activedocument.Mailmerge.OpenDataSource filename,0
because you are making a method call, not a function call.
The reason
Activedocument.Mailmerge.OpenDataSource (filename)
works is because (filename) is a valid expression. (Actually I'm not an
expert on this stuff so don't take my word for it, but I think you will
discover that the following call works as well:
Activedocument.Mailmerge.OpenDataSource (filename),(0)
The call I made when testing was more like:
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.MailMerge.OpenDataSource _
"C:\mywb\Book1.xls", _
wdOpenFormatAuto, _
False, False, False, False, _
"", "", _
False, _
"", "", _
"Entire Spreadsheet", _
"", "", False, wdMergeSubTypeWord2000
That opens using DDE. To open using OLEDB, you might use:
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.MailMerge.OpenDataSource _
"C:\mywb\Book1.xls", _
wdOpenFormatAuto, _
False, False, False, False, _
"", "", _
False, _
"", "", _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=C:\mywb\Book1.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";", _
"SELECT * FROM `Sheet1$`", _
"", False, wdMergeSubTypeOther
The "_" characters are simply "continued on next line" markers and there is
no significance in the fact that some lines conain one parameter and others
contain several parameters.
However, I almost always precede an OpenDataSource call with the following
code:
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
This is to ensure that the OpenDataSource call does not fail if the document
is already connected to the data source in a way that would prevent the
method call from working. That may happen if, for example, the excel
document is already open in Excel (because word has already opened it as a
data source via DDE) and you open using OLEDB - actually, I forget the exact
circumstances, but another thing to beware of is the situation where the
document is attached to a template that has been set up as a mail merge main
document. In that case, if the template has opened the data source, when the
document also tries to open it, the open can fail. Unfortunately I think
that behaviour also changed between Word 2000 and 2002.
Unfortunately, when you disconnect the data source in this way, the document
forgets what type of mail merge main document it is (letter merge, catalog
merge, label merge etc.), and forgets any sort or filter options, so if you
do this you will have to restore the mail merge main document type.
Typically sort/filter options can be specified using SQL, but in the case
where the user has selected individual records in the Mail MErge Recilpients
box, Word uses another method to remember which records should be merged
(let's not get into that).
<<
displays two dialogue boxes before displaying the error message I
originally posted in Word 11. They are both entitled "Select Table." The
first displays 4 column headings, "Name," "Description," "Modified,"
"Created" and "Type,: one row of data Name: "XJM0001096$", Description:
"", Modified: "1/26/2006 11:07:05AM", Created: "1/26/2006 11:07:05 AM",
"TABLE". I think this must come from the registry, as the file name is
structured the same as a bunch of .XLS files in my temp location that were
created when I ran the larger PowerBuilder application. The dates are
from when I ran that application last. This info I'm giving you now is
from Feb 6, 2006. At the bottom is a checkbox "First row of data contains
column headers," and OK and CANCEL.
If you don't supply specific connection and/ior query information to
OpenDataSource in Word 2002/2003, Word tries to open the data source using
OLEDB first, then if that fails, it may try ODBC, then if that fails, it may
try DDE.
The first dialog box you are seeing is an OLEDB dialog box intended to let
you select a sheet. The data comes from the workbook, not the registry. it
suggests that in this case you would need to use the SQL
SELECT * FROM `XJM0001096$`
if you were trying to open using OLEDB.
<<
The second dialogue box has another "grid," but with only one "column"
and the label for that colun is outside the grid. It is "Table." There
is one empty row, disitinguishable by being outlined in dots. At the
bottom of the grid is a dropdown labeled "Workbook," with only one value
in it, which is the name of the Word document I have just opened, complete
with location. On the bottom left is an "Options" button, and on the right
a disabled "OK" and a "Cancel" button. If Options is clicked, a dialogue
box appears titled "Table Options." Beneath a "Show" label are 4
checkboxes "Tables, Vieiws, System Tables and Synaonyms" and an OK and
Cancel.
The second dialog box is an ODBC dialog box. Typically, you have to click
Options... and select at least "Tables" and "System tables" before you see
the list of sheets in the workbook. Also, it's advisable to check that the
filename in the Workbook filed is actually the name you specified, but
unfortunately typical pathnames are too long to see and there's no simple
way to show the full pathname. You can't even copy/paste the value into a
Notepad document to have a better look at it.
My guess is that Powerbuilder is getting the number or types of the
parameters needed wrong. That might happen if Powerbuilder is not querying a
type library dynamically or some such.
Anyway, I doubt if that's going to get you any further but at least it may
clarify a few things.
I really wish this stuff was simpler and more consistent, but there it is.
Peter Jamieson
Laurel said:
Just a little bit.
First, I got the same error with your suggested OLEDB version. I tried
playing around with the parameter you said I might have to also populate,
but didn't get anything but dialogue boxes.
Second, I decided I'd probably have to send a defect report to Sybase. To
that end I wanted to make a demo application that showed that the syntax
worked OK with Word 11 outside of Powerbuilder, as you had told me you
were able to demonstrate. But I couldn't use this syntax in a Word VBA
environment. What environment did you use? See my posting "Syntax Error
calling MailMerge.OpenDatasource" in the ....Word.VBA.Beginners forumn."
I was able to use named syntax, but not positional.
Third: I made a tiny app in PowerBuilder to show the problem so I could
send it to Sybase. The code is exactly the same as the code in the
original application (diferent file names), and it works OK in Word 9, but
it displays two dialogue boxes before displaying the error message I
originally posted in Word 11. They are both entitled "Select Table." The
first displays 4 column headings, "Name," "Description," "Modified,"
"Created" and "Type,: one row of data Name: "XJM0001096$", Description:
"", Modified: "1/26/2006 11:07:05AM", Created: "1/26/2006 11:07:05 AM",
"TABLE". I think this must come from the registry, as the file name is
structured the same as a bunch of .XLS files in my temp location that were
created when I ran the larger PowerBuilder application. The dates are
from when I ran that application last. This info I'm giving you now is
from Feb 6, 2006. At the bottom is a checkbox "First row of data contains
column headers," and OK and CANCEL.
The second dialogue box has another "grid," but with only one "column"
and the label for that colun is outside the grid. It is "Table." There
is one empty row, disitinguishable by being outlined in dots. At the
bottom of the grid is a dropdown labeled "Workbook," with only one value
in it, which is the name of the Word document I have just opened, complete
with location. On the bottom left is an "Options" button, and on the right
a disabled "OK" and a "Cancel" button. If Options is clicked, a dialogue
box appears titled "Table Options." Beneath a "Show" label are 4
checkboxes "Tables, Vieiws, System Tables and Synaonyms" and an OK and
Cancel.
I'm hoping that this might provide a clue as to which parameter I might
alter, but I haven't hit on anything useful yet.
Thanks for checking back in!
LAS
Peter Jamieson said:
Did you manage to make any progress on this one?
Peter Jamieson
Laurel said:
Could you give me an example of using positional parameters? Just to
eliminate that variable before I plunge into figuring out what a sheet
is.
Can you clue me in about what a sheet is, since it must not be the
actual file name, which was already used to generate the OLE object
ole_word_file?
Remember the last line in my original e-mail about "Ole for dummies"
If I don't hear from you tonight, I'll post a separate e-mail asking for
examples of positional parameters.
thanks for your help
Sorry, you are right - I wasn't looking at your complete message.
I have checked the parameters and tried them using a call with
positional parameters (instead of named parameters, which is how I
usually work in VBA) here. Everythng seems to be OK. 8 is the correct
value for the last parameter. So I'm stumped. It may be that Word 2003
is simply failing to connect to Excel 2003 via DDE (that is not an
uncommon problem).
It may be worth trying to open using OLEDB. To do that, you could try:
ole_word_file.MailMerge.OpenDataSource(is_excel_doc,0,FALSE,
FALSE,FALSE,FALSE,"","",FALSE,"","",
"","SELECT * FROM `Sheet1$`")
Although that works here,
a. you have to know the Sheet name of the sheet you want to open
(`Sheet1$` in this case)
If you must supply all the parameters, you would need
ole_word_file.MailMerge.OpenDataSource(is_excel_doc,0,FALSE,
FALSE,FALSE,FALSE,"","",FALSE,"","",
"","SELECT * FROM `Sheet1$`","",FALSE,0)
(0 is wdMergeSubtypeOther)
When recording a macro using OLEDB to open, Word also fills in the
Connection parameter - the one where you had "Entire Spreadsheet"
before. Word truncates it, which doesn't help, but if you need one, it
would have to be something like:
Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data
Source=C:\myxl\xl.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";
where you would need to substitute your own path name instead of
c:\myxl\xl.xls I don't know how you would deal with the " characters in
Powerbuilder. You can probably shorten that to
Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data
Source=C:\myxl\xl.xls;
The problem with using OLEDB to connect is that you then start seeing
the underlying data in the spreadsheet and you will probably need to
use field switches to format the results, especially for dates and
numebrs, and in some cases it's difficult to get it right.
Anyway, I am offline for a few days so let's hope that gets you
somewhere useful.
Peter Jamieson
ole_word_file.MailMerge.OpenDataSource(is_excel_doc,0,FALSE,
FALSE,FALSE,FALSE,"","",FALSE,"","",
"","SELECT * FROM `Sheet1$`")
I'm sorry, but I can't tell the difference between what you say I need
and what I am doing. I show two exampels, the old one first, with 14
parameters, and the new one, with 16, which looks just like the one
you give me. I got the second, one, with 16 parameters from an answer
you gave in a 2003 posting. I wish this forum supported color so I
could highlight what I'm talking about in my original posting. It
sounds like you may not have scrolled down enough.
You're pretty sure that 8 is the proper value for the last parameter
for 11.0? The answer you gave was in response to 10.0.
Bottom line - I'm already using what you suggest, and I get the error
I show. Any other ideas?
I can't be sure with Powerbuilder but...
In Word 2002/2003 there are 16 parameters. I think you need an extra
couple of strings, i.e.
I think you need
ole_word_file.MailMerge.OpenDataSource(is_excel_doc,0,FALSE,
FALSE,FALSE,FALSE,"","",FALSE,"","","Entire
Spreadsheet","","",FALSE,8)
(but actually I don't think the values of the first four booleans, if
any, will make any difference). The two strings are there to hold an
SQL statement that you don't need in this case.
That should open the Excel file using DDE, which is the default
method used in Word 2000 and which opens a copy of Excel. It would
also be possible to use OLEDB, which is the default method in Word
2002/2003 and is not available in Word 2000, and which has other
compatibility implications to do with the format of the data.
Peter Jamieson
You responded to my question about which parameter pattern Word 10
follows, but you didn't tell me what to do about the fact that I
still am not able to make a successfull call to OpenDataSource under
Word 2003. Below I show what I have tried - following the pattern
you suggested in 2003 for a similar problem under Office 2002. Can
you see what I might be doing wrong here? Perhpas the last
parameter should no longer be 8?
Already answered in a previous thead.
Peter Jamieson
In a PowerBuilder script, this line of code works fine in Word 9.0
(Office
2000). But it fails in Word 11.0 (Office 2003).
ole_word_file.MailMerge.OpenDataSource(is_excel_doc,0,FALSE,TRUE,FALSE,FALSE,"","",FALSE,"","","Entire
Spreadsheet")
The error is "Error 35 - Error calling external object function
OpenDataSource at line 52"
Two new parameters have been added in Word 11, [I found a 2003
thread in
this forum which suggesteed that the values should be as follows.
This does
not work for me, but I'm working with Word 11, and the author of
the 2003
thread was working with "Word 2002" - probably Office 2002? -
don't know
what version, but probably 10. Should I be entering a different
constant
value for the last parameter?
ole_word_file.MailMerge.OpenDataSource(is_excel_doc,0,FALSE,
TRUE,FALSE,FALSE,"","",FALSE,"","","Entire
Spreadsheet",FALSE,8)
Note that I have no experience working with OLE, so please use
"for dummies"
language when responding.
TIA
LAS
