Prev: Runtime Error 509
Next: manipulating pdf files from vba
From: Laurel on 6 Feb 2006 16:39
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" <pjj(a)KillmapSpjjnet.demon.co.uk> wrote in message
news:%231WQDB1KGHA.2668(a)tk2msftngp13.phx.gbl...
> Did you manage to make any progress on this one?
>
> Peter Jamieson
>
>
> "Laurel" <FakeMail(a)Hotmail.com> wrote in message
> news:%23nog7F6IGHA.3224(a)TK2MSFTNGP09.phx.gbl...
>> 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
>>
>> "Peter Jamieson" <pjj(a)KillmapSpjjnet.demon.co.uk> wrote in message
>> news:eMGXQ12IGHA.1832(a)TK2MSFTNGP11.phx.gbl...
>>> 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$`")
>>>
>>>
>>>
>>>
>>>
>>>
>>> "Laurel" <FakeMail(a)Hotmail.com> wrote in message
>>> news:epjBYD2IGHA.1676(a)TK2MSFTNGP09.phx.gbl...
>>>> 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?
>>>>
>>>> "Peter Jamieson" <pjj(a)KillmapSpjjnet.demon.co.uk> wrote in message
>>>> news:O0PccjuIGHA.596(a)TK2MSFTNGP10.phx.gbl...
>>>>>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
>>>>>
>>>>> "Laurel" <FakeMail(a)Hotmail.com> wrote in message
>>>>> news:OYQAFRsIGHA.2012(a)TK2MSFTNGP14.phx.gbl...
>>>>>> 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?
>>>>>>
>>>>>> "Peter Jamieson" <pjj(a)KillmapSpjjnet.demon.co.uk> wrote in message
>>>>>> news:uTx$zFsIGHA.3728(a)tk2msftngp13.phx.gbl...
>>>>>>> Already answered in a previous thead.
>>>>>>>
>>>>>>> Peter Jamieson
>>>>>>>
>>>>>>> "Laurel" <FakeMail(a)Hotmail.com> wrote in message
>>>>>>> news:%237GSx3rIGHA.2040(a)TK2MSFTNGP14.phx.gbl...
>>>>>>>> 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
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


From: Peter Jamieson on 6 Feb 2006 19:27
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" <FakeMail(a)Hotmail.com> wrote in message
news:OPylLW2KGHA.3728(a)tk2msftngp13.phx.gbl...
> 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" <pjj(a)KillmapSpjjnet.demon.co.uk> wrote in message
> news:%231WQDB1KGHA.2668(a)tk2msftngp13.phx.gbl...
>> Did you manage to make any progress on this one?
>>
>> Peter Jamieson
>>
>>
>> "Laurel" <FakeMail(a)Hotmail.com> wrote in message
>> news:%23nog7F6IGHA.3224(a)TK2MSFTNGP09.phx.gbl...
>>> 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
>>>
>>> "Peter Jamieson" <pjj(a)KillmapSpjjnet.demon.co.uk> wrote in message
>>> news:eMGXQ12IGHA.1832(a)TK2MSFTNGP11.phx.gbl...
>>>> 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$`")
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> "Laurel" <FakeMail(a)Hotmail.com> wrote in message
>>>> news:epjBYD2IGHA.1676(a)TK2MSFTNGP09.phx.gbl...
>>>>> 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?
>>>>>
>>>>> "Peter Jamieson" <pjj(a)KillmapSpjjnet.demon.co.uk> wrote in message
>>>>> news:O0PccjuIGHA.596(a)TK2MSFTNGP10.phx.gbl...
>>>>>>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
>>>>>>
>>>>>> "Laurel" <FakeMail(a)Hotmail.com> wrote in message
>>>>>> news:OYQAFRsIGHA.2012(a)TK2MSFTNGP14.phx.gbl...
>>>>>>> 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?
>>>>>>>
>>>>>>> "Peter Jamieson" <pjj(a)KillmapSpjjnet.demon.co.uk> wrote in message
>>>>>>> news:uTx$zFsIGHA.3728(a)tk2msftngp13.phx.gbl...
>>>>>>>> Already answered in a previous thead.
>>>>>>>>
>>>>>>>> Peter Jamieson
>>>>>>>>
>>>>>>>> "Laurel" <FakeMail(a)Hotmail.com> wrote in message
>>>>>>>> news:%237GSx3rIGHA.2040(a)TK2MSFTNGP14.phx.gbl...
>>>>>>>>> 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
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


From: Laurel on 7 Feb 2006 11:39
Thanks. Lots of good stuff here. See question below.
>
> 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
>

This looks like you were actually working in a Word 2003 environment,
specifying 2002 as a subtype. I would like to try this in my PB
application, using different sets of parameters, depending on whether I'm
running in Word 9 or Word 11. My problem is I can't find the values for the
constants available for the last parameter, "SubType"). I don't know how to
display them in the browser, and HELP in my copy of Word 2003 leaves it off.
Here is "HELP" for OpenDataSource in Word 2003. Could you give me the value
for "wdMergeSubTypeWord2000"?

I stumbled on a way to see a list of acceptable parameter constants and
their values, but I can't figure it out now. Could you also help me out
with this?

FROM HELP
Attaches a data source to the specified document, which becomes a main
document if it's not one already.

expression.OpenDataSource(Name, Format, ConfirmConversions, ReadOnly,
LinkToSource, AddToRecentFiles, PasswordDocument, PasswordTemplate, Revert,
WritePasswordDocument, WritePasswordTemplate, Connection, SQLStatement,
SQLStatement1, OpenExclusive)




From: Peter Jamieson on 7 Feb 2006 13:10
> This looks like you were actually working in a Word 2003 environment,

Yes. I can also test in Word 2002 if necessary. But there have been changes
in this area in the various Word Service Packs as well.

> specifying 2002 as a subtype.

"Word 2000" in fact. The official documentation suggests that setting
Subtype to wdMergeSubtypeWord2000 means "use DDE." However, a reliable
source said that what it actually does is forces Word to use the same path
through code as Word 2000 uses, i.e. if the parameters make Word 2000 would
open using DDE, that's what Word 2003 will do, but if they make Word 2000
open using ODBC, that's what Word 2003 will do.

<<
> My problem is I can't find the values for the constants available for the
> last parameter, "SubType"
>>

You are not alone, as this particular parameter is poorly documented. In the
Word VBA Editor (VBE), you can see the documented values and the associated
integers using View|Object Browser (or press F2) then type "subtype"
(without the quotes) in the second text box (you can leave the first text
box saying "<All Libraries>" or change it to "Word"). Then click the
binocular button to the right of the text box. Everything should be
reasonably self-evident from that point, except for the fact that the
meanings of the various subtype values are not spelled out. The main point
is that to open an Excel sheet using OLEDB you probably need
wdMergeSubtypeOther (value 0) and for DDE or ODBC, use
wdMergeSubTypeWord2000.

AFAIK the object browser works directly from Word's type library.
Incidentally, if you have a look in the list of Classes for MailMerge you
should be able to select OpenDataSource and click the definition. If you
right-click in the Object Browser and select Show Hidden Members, you will
see that there is actually a version of OpenDataSource with the same
parameters as OpenDataSource in Word 2000, called OpenDataSource2000. I have
never tried to use it, but maybe that help in this situation.

Peter Jamieson

"Laurel" <FakeMail(a)Hotmail.com> wrote in message
news:OyGCHTALGHA.2992(a)tk2msftngp13.phx.gbl...
> Thanks. Lots of good stuff here. See question below.
>>
>> 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
>>
>
> This looks like you were actually working in a Word 2003 environment,
> specifying 2002 as a subtype. I would like to try this in my PB
> application, using different sets of parameters, depending on whether I'm
> running in Word 9 or Word 11. My problem is I can't find the values for
> the constants available for the last parameter, "SubType"). I don't know
> how to display them in the browser, and HELP in my copy of Word 2003
> leaves it off. Here is "HELP" for OpenDataSource in Word 2003. Could you
> give me the value for "wdMergeSubTypeWord2000"?
>
> I stumbled on a way to see a list of acceptable parameter constants and
> their values, but I can't figure it out now. Could you also help me out
> with this?
>
> FROM HELP
> Attaches a data source to the specified document, which becomes a main
> document if it's not one already.
>
> expression.OpenDataSource(Name, Format, ConfirmConversions, ReadOnly,
> LinkToSource, AddToRecentFiles, PasswordDocument, PasswordTemplate,
> Revert, WritePasswordDocument, WritePasswordTemplate, Connection,
> SQLStatement, SQLStatement1, OpenExclusive)
>
>
>
>


From: Laurel on 8 Feb 2006 11:11
> You are not alone, as this particular parameter is poorly documented. In
> the Word VBA Editor (VBE), you can see the documented values and the
> associated integers using View|Object Browser (or press F2) then type
> "subtype" (without the quotes) in the second text box (you can leave the
> first text box saying "<All Libraries>" or change it to "Word"). Then
> click the binocular button to the right of the text box. Everything should
> be reasonably self-evident from that point, except for the fact that the
> meanings of the various subtype values are not spelled out. The main point
> is that to open an Excel sheet using OLEDB you probably need
> wdMergeSubtypeOther (value 0) and for DDE or ODBC, use
> wdMergeSubTypeWord2000.

Thanks. The value is 8. I'll let you know what happens. Just wanted to
post this quick question. I was able to get the list of valid constants for
the SubType parameter just fine, but I wasn't able to find out what their
actual numeric values were. To do that I had to write a little macro, put a
break at the last statement and examine the value of itest. Is there
another way? As I mentioned before, I had stumbled on a method to find the
value of a constant.... But maybe this was what I did. It's all such a blur
right now. Is there a simpler way?

Sub wtest()
'
' wtest Macro
' Macro created 2/8/2006 by Laurel
'
Dim itest As Integer
Dim sdebug As String

itest = wdMergeSubTypeWord2000

sdebug = "stop"
End Sub


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Runtime Error 509
Next: manipulating pdf files from vba