Prev: Runtime Error 509
Next: manipulating pdf files from vba
From: Peter Jamieson on 27 Jan 2006 13:12
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 27 Jan 2006 19:28
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 28 Jan 2006 03:33
Positional parameters is what you are using already, i.e. the way parameters
are typically passed from procedural languages

call abc(x,y,z)
call abc(,y,z) (no value for "x")

In VB(A) you can also use name parameters, e.g. with OpenDataSource you can
do

OpenDataSource _
Name:="c:\mypath\myfile.xls", _
Connection:="some connection parameters", _
SQLStatement:="SELECT x FROM y"

I don't know if that approach is available in PowerBuilder,

> 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?

I'm assuming the file you are trying to open is an excel file as suggested
by your parameter name. If it isn't, or isn't necessarily, you may need to
do more.

In an Excel file or "workbook" there may be one or more worksheets. When you
create a new excel workbook 3 sheets are created by default. Their default
names are Sheet1, Sheet2, Sheet3, but they can be renamed and resequenced by
the user. There are also some kind of internal sheet name, hence the Sheet1$
etc. I used. You see these sheet names when you open an Excel workbook as a
data source manually in Word 2002/2003. When you connect to an Excel file
using DDE and specify "Entire Spreadsheet", you always get the first sheet.
When you connect to a data source from Word using OLEDB you have to specify
the sheet name (as far as I can remember) in an SQL SELECT statement, which
obviously complicates things for your code because previously you didn't
have to know the sheet name.

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 31 Jan 2006 14:44

"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).

Thanks for all your help. Breaking logjams for me on several fronts.

What environment are you using to check the parameters? I'm working on two
fronts now. One is to put together the info I need to send Sybase
(PowerBuilder) a defect report. To do that, I want to send them a script
that proves that the statement works outside of PowerBuilder. I'm trying to
do this in a Word macro. If, indeed, a word document with a macro is a
reasonable way to go to demonstrate the validity of my statement, would you
look at "Syntax Error Calling MailMerge.OpenDataSource" in the
.....vba.beginners forum?

TIA
LAS


From: Peter Jamieson on 6 Feb 2006 14:04
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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


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