Re: Can you have a command line arg with a workbook?

use .Arguments.Count to check that you have enough

eg
dim expected
expected = 3
if wscript.arguments.count = expected then
msgbox "OK"
else
msgbox "Missing arg"
end if

"Trefor" <Trefor@xxxxxxxx> wrote in message news:CCBE89FF-1C54-4740-9602-62ADD5F05D7C@xxxxxxxxxxxxxxxx
Patrick / Steve,

This is my first venture into VBScript and I know this is the start of what
will work for me. As it stands the VBScript will error if all the args are
not specified. What is the best way to handle this?

I tied this and it appears to work, but look a little excessive:

If Arg1 <> "" and Arg2 <> "" then
objXL.run "TestMacro", CStr(Arg1), CStr(Arg2)
Elseif Arg2 <> "" then
objXL.run "TestMacro", CStr(Arg1), ""
Else
objXL.run "TestMacro", "" , ""
End If

--
Trefor


"Steve Yandl" wrote:

Trefor,

I've always used a vbScript file as a sort of helper file to do this sort of
thing.

For my test, I created a workbook named "C:\Test\TestBk1.xls". In module 1
I created a subroutine named "TestMacro" that takes two text string
arguments.

Now I create a text file that I name "C:\Test\LaunchSub.vbs". The contents
of this script is shown between the lines below,
________________________________________
arg1 = WScript.Arguments(0)
arg2 = WScript.Arguments(1)


Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objWkbk = objXL.Workbooks.Open("C:\Test\TestBk1.xls")
objXL.run "TestMacro", CStr(arg1), CStr(arg2)
__________________________________________

Now, the command line that I can use at the prompt for cmd.exe or at the
'Start > Run' line for Windows would be:
WScript.exe "C:\Test\LaunchSub.vbs" "dog" "cat"
Note, that I am using WScript.exe with the name of my vbs file as the first
argument. After that is a space followed by my first argument to go to the
vbs script and then a second space followed by my final argument. Those
arguments will be retrieved in the WScript.Arguments collection. In the
script, all the arguments will be treated as variant type so you will want
to convert them to the appropriate data type before sending them to your
Excel macro.


Steve Yandl




"Trefor" <Trefor@xxxxxxxx> wrote in message
news:A7FC1773-7EAA-4840-B0CB-B13279E0B86F@xxxxxxxxxxxxxxxx
> If it possible to read an arg from the command line in VBA?
>
> myworkbook.xls arg1 arg2
>
> I would like to run a certain macro if a certain argument or parameter > is
> specified.
> -- > Trefor



.

Relevant Pages

  • Re: Can you have a command line arg with a workbook?
    ... case else:msgbox "more than 3 arguments" ... Case if that works in VBScript?) ... this script starts a new instance of Excel. ... I created a subroutine named "TestMacro" that takes two text string ...
    (microsoft.public.excel.programming)
  • Script doing right?
    ... I have a script the returns only the internal address from IIS. ... ' 0 arg is command can be ... ' 2 arg is username ... elseif Then ...
    (microsoft.public.scripting.vbscript)
  • Re: Brand new: script to back up files?
    ... It copies ALL your *log file on your Hard Disk in C:\BackupsTest ... For Each Arg In inputData ... ' WScript.Echo Arg ... 'MsgBox strTestString ...
    (microsoft.public.scripting.vbscript)
  • Re: Copy script
    ... the FINAL destination folder is C:\BackupsTest: ... For Each Arg In inputData ... ' WScript.Echo Arg ... 'MsgBox strTestString ...
    (microsoft.public.scripting.vbscript)
  • Re: Find and Replace letters inside a file
    ... This is the barebones version of a script that I ... Dim fso, ts, s, arg, fil, fpath, s1 ... Set fso = CreateObject ... s1 = Replace(s, vbCrLf, vbCr) ...
    (microsoft.public.scripting.vbscript)
Quantcast