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

Macros cannot be run from the Command line.

Place your arguments in a Workbook_Open event in Thisworkbook module.

Or Auto_Open in a standard module.


Gord Dibben MS Excel MVP

On Sun, 14 Jun 2009 08:44:02 -0700, Trefor wrote:

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.

its very limited.

what you can do is create a VBScript file that can open excel & you can pass
arguments to this...

so create a workbook he C:\temp\DemoBook4.xls
add this procedure to a standard code module:=

Option Explicit
Sub DemoRoutine(a As String, b As String, c As String)
MsgBox a & vbCrLf & b & vbCrLf & c
End Sub

Onto your desktop, add a new textfile, call it anything you like, but change
the .TXT extension to .VBS
wdir with Notepad and paste this:-

option explicit
dim wb
dim xl
set xl = CreateObject("Excel.Application")
set wb = xl.workbooks.Open( "C:\temp\DemoBook4.xls")
xl.visible = true
xl.run
"DemoRoutine",wscript.arguments.item(0),wscript.ar guments.item(1),wscript.arguments.item(2)
wb.Close False
xl.quit
set wb = nothing
set xl = nothing


open a command window

type
cd desktop [return]
"new text document.vbs" "a" "b" "c"

basically you're running a VBScript file and passing three arguments to it.
The script opens an excel file and calls the procedure, passing to it the
three variables...which you see in the excel mssage






"Trefor" wrote in message
...
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

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" wrote in message
...
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

Hi,

You can try that. It's OK with XL2007 and Vista.
Sorry, it's in french.

http://xcell05.free.fr/pages/prog/parametres.htm

ctac

"Trefor" wrote:

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

ctac,

Thankyou for the reply.

Sorry I can't read French and I am using XL2003. This look similar to the
code I saw for getting the cmd line from Excel itself as opposed to the
workbook cmd line?

--
Trefor


"ctac" wrote:

Hi,

You can try that. It's OK with XL2007 and Vista.
Sorry, it's in french.

http://xcell05.free.fr/pages/prog/parametres.htm

ctac

"Trefor" wrote:

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

Patrick,

Many thanks for the reply, please see my reply to Steve.

--
Trefor


"Patrick Molloy" wrote:

its very limited.

what you can do is create a VBScript file that can open excel & you can pass
arguments to this...

so create a workbook he C:\temp\DemoBook4.xls
add this procedure to a standard code module:=

Option Explicit
Sub DemoRoutine(a As String, b As String, c As String)
MsgBox a & vbCrLf & b & vbCrLf & c
End Sub

Onto your desktop, add a new textfile, call it anything you like, but change
the .TXT extension to .VBS
wdir with Notepad and paste this:-

option explicit
dim wb
dim xl
set xl = CreateObject("Excel.Application")
set wb = xl.workbooks.Open( "C:\temp\DemoBook4.xls")
xl.visible = true
xl.run
"DemoRoutine",wscript.arguments.item(0),wscript.ar guments.item(1),wscript.arguments.item(2)
wb.Close False
xl.quit
set wb = nothing
set xl = nothing


open a command window

type
cd desktop [return]
"new text document.vbs" "a" "b" "c"

basically you're running a VBScript file and passing three arguments to it.
The script opens an excel file and calls the procedure, passing to it the
three variables...which you see in the excel mssage






"Trefor" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

Patrick,

Thanks, but I guess I would still need the multiple IF statements (or Select
Case if that works in VBScript?) to cover the different counts?

Also on another note, this script starts a new instance of Excel. Is it
possible to use the existing instance if one already exists?

--
Trefor


"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote in message
...
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

Patrick,

Many thanks for you reply. I would still have to use multiple IF statements
for each count though wouldnt I?

Also on another note, this script starts a new instance of Excel. Is it
possible for the script to use the existing instance if there is one?

--
Trefor


"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote in message
...
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

Patrick,

Many thanks for you reply. I would still have to use multiple IF statements
for each count though wouldnt I?

Also on another note, this script starts a new instance of Excel. Is it
possible for the script to use the existing instance if there is one?

--
Trefor


"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote in message
...
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

yes - IF or Select both work - thats down to you to decide what is most
effective

select case wscript.arguments.count
case 0:msgbox "no arguments"
case 1:msgbox "1 arguments"
case 2:msgbox "2 arguments"
case 3:msgbox "3 arguments"
case else :msgbox "more than 3 arguments"



end select

"Trefor" wrote in message
...
Patrick,

Thanks, but I guess I would still need the multiple IF statements (or
Select
Case if that works in VBScript?) to cover the different counts?

Also on another note, this script starts a new instance of Excel. Is it
possible to use the existing instance if one already exists?

--
Trefor


"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote in message
...
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

Patrick,

Great thankyou. Are you able to help with my last question re multiple
instances?

--
Trefor


"Patrick Molloy" wrote:

yes - IF or Select both work - thats down to you to decide what is most
effective

select case wscript.arguments.count
case 0:msgbox "no arguments"
case 1:msgbox "1 arguments"
case 2:msgbox "2 arguments"
case 3:msgbox "3 arguments"
case else :msgbox "more than 3 arguments"



end select

"Trefor" wrote in message
...
Patrick,

Thanks, but I guess I would still need the multiple IF statements (or
Select
Case if that works in VBScript?) to cover the different counts?

Also on another note, this script starts a new instance of Excel. Is it
possible to use the existing instance if one already exists?

--
Trefor


"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote in message
...
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

yes - using Windows API calls

Chip Pearson has quite a good write up....try this
http://www.cpearson.com/excel/ActivateExcelMain.aspx


"Trefor" wrote in message
...
Patrick,

Great thankyou. Are you able to help with my last question re multiple
instances?

--
Trefor


"Patrick Molloy" wrote:

yes - IF or Select both work - thats down to you to decide what is most
effective

select case wscript.arguments.count
case 0:msgbox "no arguments"
case 1:msgbox "1 arguments"
case 2:msgbox "2 arguments"
case 3:msgbox "3 arguments"
case else :msgbox "more than 3 arguments"



end select

"Trefor" wrote in message
...
Patrick,

Thanks, but I guess I would still need the multiple IF statements (or
Select
Case if that works in VBScript?) to cover the different counts?

Also on another note, this script starts a new instance of Excel. Is it
possible to use the existing instance if one already exists?

--
Trefor


"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote in message
...
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

Patrick,

I didn't understand what you were refering me to, but I found what I was
after:

This should ensure I reuse an existing instance before starting another.

Err.Clear
on error resume next
Set objXL = GetObject(, "Excel.Application")
If Err < 0 then
Err.Clear
Set objXL = CreateObject("Excel.Application")
end if



--
Trefor


"Patrick Molloy" wrote:

yes - using Windows API calls

Chip Pearson has quite a good write up....try this
http://www.cpearson.com/excel/ActivateExcelMain.aspx


"Trefor" wrote in message
...
Patrick,

Great thankyou. Are you able to help with my last question re multiple
instances?

--
Trefor


"Patrick Molloy" wrote:

yes - IF or Select both work - thats down to you to decide what is most
effective

select case wscript.arguments.count
case 0:msgbox "no arguments"
case 1:msgbox "1 arguments"
case 2:msgbox "2 arguments"
case 3:msgbox "3 arguments"
case else :msgbox "more than 3 arguments"



end select

"Trefor" wrote in message
...
Patrick,

Thanks, but I guess I would still need the multiple IF statements (or
Select
Case if that works in VBScript?) to cover the different counts?

Also on another note, this script starts a new instance of Excel. Is it
possible to use the existing instance if one already exists?

--
Trefor


"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote in message
...
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

nice one :)
I'd completely forgotten about the GetObject function.

"Trefor" wrote in message
...
Patrick,

I didn't understand what you were refering me to, but I found what I was
after:

This should ensure I reuse an existing instance before starting another.

Err.Clear
on error resume next
Set objXL = GetObject(, "Excel.Application")
If Err < 0 then
Err.Clear
Set objXL = CreateObject("Excel.Application")
end if



--
Trefor


"Patrick Molloy" wrote:

yes - using Windows API calls

Chip Pearson has quite a good write up....try this
http://www.cpearson.com/excel/ActivateExcelMain.aspx


"Trefor" wrote in message
...
Patrick,

Great thankyou. Are you able to help with my last question re multiple
instances?

--
Trefor


"Patrick Molloy" wrote:

yes - IF or Select both work - thats down to you to decide what is
most
effective

select case wscript.arguments.count
case 0:msgbox "no arguments"
case 1:msgbox "1 arguments"
case 2:msgbox "2 arguments"
case 3:msgbox "3 arguments"
case else :msgbox "more than 3 arguments"



end select

"Trefor" wrote in message
...
Patrick,

Thanks, but I guess I would still need the multiple IF statements
(or
Select
Case if that works in VBScript?) to cover the different counts?

Also on another note, this script starts a new instance of Excel. Is
it
possible to use the existing instance if one already exists?

--
Trefor


"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote in message
...
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