vbscript, Need help passing arguments to a COM method...

Need help passing arguments to a COM method...

Author	Message
Robert Stobe #1 / 5	Need help passing arguments to a COM method... Hi, I've been trying (and failing) for the last several hours to call an Excel method from my vbs script. The trouble is that the arguments are rather complicated. Here's a snippet of the Excel VBA code that I'm trying to move the into my vbs script (this code works) ------------ Begin working Excel VBA code ------------------------- Workbooks.OpenText _ fileName:="H:\licana\licuser\orders\" & orderNum & "\" & fileName, _ Origin:=xlWindows, _ StartRow:=1, _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=True, _ Semicolon:=False, _ Comma:=False, _ Space:=False, _ Other:=False, _ FieldInfo:=Array(Array(1, 5), Array(2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1)) ------------- End code --------------------------------------- Now, Here's the vbs code I've written to do the same thing. Before this code I've already created an excel object (xlApp) and added a workbook (xlWb). The latest error message is: "H:\licana\licuser\bin\mkorder2xls.vbs(78, 2) Microsoft Excel: OpenText method of Workbooks class failed Of course, I've seen just about every error in the book on the way to this stage. I thought they were due to the ":=" characters, which is why I set up a variable for each of the Opentext arguments. The examples I've found are quite simplistic, they don't deal with complex arguments to a COM method like this one. ---------- Begin broken VBS code ---------------------- For i = LBound(fNames) to UBound(fNames) fPath="H:\licana\licuser\orders\" & orderID & "\" & fNames(i) filename="fileName:=fPath" origin="Origin:=xlWindows" startrow="StartRow:=1" datatype="DataType:=xlDelimited" textqualifier="TextQualifier:=xlDoubleQuote" consecutivedelimiter="ConsecutiveDelimiter:=False" tab="Tab:=True" semicolon="SemiColon:=False" comma="Comma:=False" spaced="Space:=False" other="Other:=False" fieldinfo="FieldInfo:=Array(Array(1, 5), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))" xlApp.Workbooks.OpenText filename, origin, startrow, datatype, textqualifier, consecutivedelimiter, tab, semicolon, comma, spaced, other, fieldinfo Next ------------------------------ END CODE ----------------------------- Does anyone know how to do this? I sure could use a pointer... Thank you very much, Robert Stober
Sat, 10 Sep 2005 16:18:51 GMT

MV #2 / 5	Need help passing arguments to a COM method... Quote: > I've been trying (and failing) for the last several hours to call an Excel > method from my vbs script. The trouble is that the arguments are rather > complicated. Here's a snippet of the Excel VBA code that I'm trying to move > the into my vbs script (this code works) Hi VBScript doesn't support named argument syntax (e.g., ArgName:="argvalue"), you have to rewrite this. More on differences between VBA and VBS here: Subject: Re: insert toc in word .doc via script http://groups.google.com/groups?selm=uZwIMevaCHA.1332%40tkmsftngp11 Some examples on using the Excel object from VBScript: Read from Microsoft Excel http://www.rlmueller.net/Read%20from%20Excel.htm Write to Microsoft Excel http://www.rlmueller.net/Write%20to%20Excel.htm Automate Excel In Vbscript... http://cwashington.netreach.net/depo/view.asp?Index=142&ScriptType=vb... Import task from Outlook in order to create a Status report in an Excel Spreadsheet http://cwashington.netreach.net/depo/view.asp?Index=510&ScriptType=vb... Use Excel To Generate Charts... http://cwashington.netreach.net/depo/view.asp?Index=300&ScriptType=vb... Google newsgroup search in the microsoft.public.scripting.vbscript group http://groups.google.com/groups?as_epq=excel%20application&safe=image... Google newsgroup search in the microsoft.public.scripting.wsh group http://groups.google.com/groups?as_epq=excel%20application&safe=image... -- torgeir Microsoft MVP Scripting and WMI, Porsgrunn Norway Administration scripting examples and an ONLINE version of the 1328 page Scripting Guide: http://www.microsoft.com/technet/scriptcenter
Sat, 10 Sep 2005 19:51:04 GMT

Robert Stobe #3 / 5	Need help passing arguments to a COM method... Torgeir, Thank you, this is good stuff! But which of these examples shows how I should re-write the named arguments? Because I don't know how to rewrite them... And I don't see the answer in the links you provided. I think you mean I should change: xlWb.OpenText _ fileName:="H:\license\licensefile" ... To: xlWb.OpenText _ xlWb.OpenText.fileName="H:\license\licensefile" Am I getting warmer? Thank you very much, Robert Stober Quote: > > I've been trying (and failing) for the last several hours to call an Excel > > method from my vbs script. The trouble is that the arguments are rather > > complicated. Here's a snippet of the Excel VBA code that I'm trying to move > > the into my vbs script (this code works) > Hi > VBscript doesn't support named argument syntax (e.g., ArgName:="argvalue"), you Quote: > have to rewrite this. > More on differences between VBA and VBS here: > Subject: Re: insert toc in word .doc via script > http://groups.google.com/groups?selm=uZwIMevaCHA.1332%40tkmsftngp11 > Some examples on using the Excel object from VBScript: > Read from Microsoft Excel > http://www.rlmueller.net/Read%20from%20Excel.htm > Write to Microsoft Excel > http://www.rlmueller.net/Write%20to%20Excel.htm > Automate Excel In Vbscript... http://cwashington.netreach.net/depo/view.asp?Index=142&ScriptType=vb... Quote: > Import task from Outlook in order to create a Status report in an Excel > Spreadsheet http://cwashington.netreach.net/depo/view.asp?Index=510&ScriptType=vb... Quote: > Use Excel To Generate Charts... http://cwashington.netreach.net/depo/view.asp?Index=300&ScriptType=vb... Quote: > Google newsgroup search in the microsoft.public.scripting.vbscript group http://groups.google.com/groups?as_epq=excel%20application&safe=image... O-8859-1&as_ugroup=microsoft.public.scripting.vbscript&lr=&hl=en Quote: > Google newsgroup search in the microsoft.public.scripting.wsh group http://groups.google.com/groups?as_epq=excel%20application&safe=image... O-8859-1&as_ugroup=microsoft.public.scripting.wsh&lr=&hl=en - Hide quoted text - - Show quoted text - Quote: > -- > torgeir > Microsoft MVP Scripting and WMI, Porsgrunn Norway > Administration scripting examples and an ONLINE version of the 1328 page > Scripting Guide: http://www.microsoft.com/technet/scriptcenter
Sun, 11 Sep 2005 00:08:26 GMT

Bob Barrow #4 / 5	Need help passing arguments to a COM method... No. You need to get rid of the "filename :=" part entirely. This is what's known as a named argument, which is not supported in vbscript. This is all you need: xlWb.OpenText "H:\license\licensefile" The drawback to this is that ALL arguments must be passed, IN ORDER. Look up the OpenText method in VBA Help and make sure you provide all the listed arguments. Bob Barrows Quote: > Torgeir, > Thank you, this is good stuff! But which of these examples shows how I > should re-write the named arguments? Because I don't know how to rewrite > them... And I don't see the answer in the links you provided. > I think you mean I should change: > xlWb.OpenText _ > fileName:="H:\license\licensefile" > ... > To: > xlWb.OpenText _ > xlWb.OpenText.fileName="H:\license\licensefile" > Am I getting warmer? > Thank you very much, > Robert Stober > > > I've been trying (and failing) for the last several hours to call an > Excel > > > method from my vbs script. The trouble is that the arguments are rather > > > complicated. Here's a snippet of the Excel VBA code that I'm trying to > move > > > the into my vbs script (this code works) > > Hi > > VBscript doesn't support named argument syntax (e.g., > ArgName:="argvalue"), you > > have to rewrite this. > > More on differences between VBA and VBS here: > > Subject: Re: insert toc in word .doc via script > > http://groups.google.com/groups?selm=uZwIMevaCHA.1332%40tkmsftngp11 > > Some examples on using the Excel object from VBScript: > > Read from Microsoft Excel > > http://www.rlmueller.net/Read%20from%20Excel.htm > > Write to Microsoft Excel > > http://www.rlmueller.net/Write%20to%20Excel.htm > > Automate Excel In Vbscript... http://cwashington.netreach.net/depo/view.asp?Index=142&ScriptType=vb... Quote: > > Import task from Outlook in order to create a Status report in an Excel > > Spreadsheet http://cwashington.netreach.net/depo/view.asp?Index=510&ScriptType=vb... Quote: > > Use Excel To Generate Charts... http://cwashington.netreach.net/depo/view.asp?Index=300&ScriptType=vb... Quote: > > Google newsgroup search in the microsoft.public.scripting.vbscript group http://groups.google.com/groups?as_epq=excel%20application&safe=image... Quote: > O-8859-1&as_ugroup=microsoft.public.scripting.vbscript&lr=&hl=en > > Google newsgroup search in the microsoft.public.scripting.wsh group http://groups.google.com/groups?as_epq=excel%20application&safe=image... - Hide quoted text - - Show quoted text - Quote: > O-8859-1&as_ugroup=microsoft.public.scripting.wsh&lr=&hl=en > > -- > > torgeir > > Microsoft MVP Scripting and WMI, Porsgrunn Norway > > Administration scripting examples and an ONLINE version of the 1328 page > > Scripting Guide: http://www.microsoft.com/technet/scriptcenter
Sun, 11 Sep 2005 01:02:08 GMT

Robert Stobe #5 / 5	Need help passing arguments to a COM method... Thank you both for your answers. I tried to specify all the arguments listed in my book, but alas I kept getting the "workbook method failed message". The situation is complicated since some of the arguments to OpenText are only appropriate if certain other arguments are specified, plus I think my book may be wrong about the exact arguments and/or the order the must be specified. I'll try to access VBA help from my version of Excel (2000 / 9.0) to verify the arguments. In the mean time, I've gone around the problem by using the OpenTextFile method of the scripting.filesystem object and then putting the info into an array mad then putting that into my spreadsheet. It's slower, but works well. Thanks again, Robert Quote: > No. You need to get rid of the "filename :=" part entirely. This is what's > known as a named argument, which is not supported in vbscript. This is all > you need: > xlWb.OpenText "H:\license\licensefile" > The drawback to this is that ALL arguments must be passed, IN ORDER. Look up > the OpenText method in VBA Help and make sure you provide all the listed > arguments. > Bob Barrows > > Torgeir, > > Thank you, this is good stuff! But which of these examples shows how I > > should re-write the named arguments? Because I don't know how to rewrite > > them... And I don't see the answer in the links you provided. > > I think you mean I should change: > > xlWb.OpenText _ > > fileName:="H:\license\licensefile" > > ... > > To: > > xlWb.OpenText _ > > xlWb.OpenText.fileName="H:\license\licensefile" > > Am I getting warmer? > > Thank you very much, > > Robert Stober > > > > I've been trying (and failing) for the last several hours to call an > > Excel > > > > method from my vbs script. The trouble is that the arguments are > rather > > > > complicated. Here's a snippet of the Excel VBA code that I'm trying to > > move > > > > the into my vbs script (this code works) > > > Hi > > > VBscript doesn't support named argument syntax (e.g., > > ArgName:="argvalue"), you > > > have to rewrite this. > > > More on differences between VBA and VBS here: > > > Subject: Re: insert toc in word .doc via script > > > http://groups.google.com/groups?selm=uZwIMevaCHA.1332%40tkmsftngp11 > > > Some examples on using the Excel object from VBScript: > > > Read from Microsoft Excel > > > http://www.rlmueller.net/Read%20from%20Excel.htm > > > Write to Microsoft Excel > > > http://www.rlmueller.net/Write%20to%20Excel.htm > > > Automate Excel In Vbscript... http://cwashington.netreach.net/depo/view.asp?Index=142&ScriptType=vb... Quote: > > > Import task from Outlook in order to create a Status report in an Excel > > > Spreadsheet http://cwashington.netreach.net/depo/view.asp?Index=510&ScriptType=vb... Quote: > > > Use Excel To Generate Charts... http://cwashington.netreach.net/depo/view.asp?Index=300&ScriptType=vb... Quote: > > > Google newsgroup search in the microsoft.public.scripting.vbscript group http://groups.google.com/groups?as_epq=excel%20application&safe=image... Quote: > > O-8859-1&as_ugroup=microsoft.public.scripting.vbscript&lr=&hl=en > > > Google newsgroup search in the microsoft.public.scripting.wsh group http://groups.google.com/groups?as_epq=excel%20application&safe=image... - Hide quoted text - - Show quoted text - Quote: > > O-8859-1&as_ugroup=microsoft.public.scripting.wsh&lr=&hl=en > > > -- > > > torgeir > > > Microsoft MVP Scripting and WMI, Porsgrunn Norway > > > Administration scripting examples and an ONLINE version of the 1328 page > > > Scripting Guide: http://www.microsoft.com/technet/scriptcenter
Mon, 12 Sep 2005 00:54:59 GMT

Page 1 of 1

[ 5 post ]