Passing parms to a VBS file or a macro when executed?

C

CRayF

I have a REXX program that runs and now outputs different TXT files depending
on a Race Track. At the end of the REXX is run the following VBS that opens
Excel. I want to pass the VBS a variable filename.
In REXX I would call ‘RaceBetting.vbs’ filename

How can I set up the VBS to accept the parm when run? And then how can I
then pass this to the macro within Excel (below)
[RaceBetting.vbs]
--------------
Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = true
XLApp.Workbooks.Open "RaceBetting.xls"
XLApp.ActiveWorkbook.RunAutoMacros 1
----------------

In my macro I have the following code.
Note “filename†was hardcoded below, I believe I have the syntax correct
except on how to accept the “passed-filename†parm from the VBS file above.
----------------
Sub ImportRaceProgramData()
'
Dim file_name As Variant
Dim passed-filename As Variant ‘(added)
Worksheets("ProgramDataInput").Range("A3:H242").ClearContents
‘file_name = ThisWorkbook.Path & "\Cut-And-Paste-Into-XLS.txt" with
‘(replaced)
file_name = ThisWorkbook.Path & "\" & passed-filename with
Worksheets("ProgramDataInput").QueryTables.Add(Connection:= _
"TEXT;" & file_name _
…more…
 
R

Rowan

You pass a variable by using arguments after the macro name eg:

Sub calling()
Dim passed_filename As String
passed_filename = "myfile"
Call ImportRaceProgramData(passed_filename)
End Sub

Sub ImportRaceProgramData(passed_filename As String)
MsgBox passed_filename
End Sub


Hope this helps
Rowan
 
C

CRayF

I can’t get it to work. It says error on Line 1 Char 38. Expecting ‘)’
I will be executing the RaceBetting.vbs from a COMMAND PROMPT.

C:\xxxxxx\xxxxxx> RaceBetting.vbs myfile.txt

[RaceBetting.vbs]
-----------------------
Sub StartRaceBetting(passed_filename As String)
Dim XLApp
Dim XLWkb
MsgBox passed_filename
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = true
XLApp.Workbooks.Open "RaceBetting.xls"
XLApp.ActiveWorkbook.RunAutoMacros 1
End Sub
-------------------

I am hoping to have the VBS above accept the parm and then to pass it onto
the macro.


Rowan said:
You pass a variable by using arguments after the macro name eg:

Sub calling()
Dim passed_filename As String
passed_filename = "myfile"
Call ImportRaceProgramData(passed_filename)
End Sub

Sub ImportRaceProgramData(passed_filename As String)
MsgBox passed_filename
End Sub


Hope this helps
Rowan

I have a REXX program that runs and now outputs different TXT files depending
on a Race Track. At the end of the REXX is run the following VBS that opens
Excel. I want to pass the VBS a variable filename.
In REXX I would call ‘RaceBetting.vbs’ filename

How can I set up the VBS to accept the parm when run? And then how can I
then pass this to the macro within Excel (below)
[RaceBetting.vbs]
--------------
Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = true
XLApp.Workbooks.Open "RaceBetting.xls"
XLApp.ActiveWorkbook.RunAutoMacros 1
----------------

In my macro I have the following code.
Note “filename†was hardcoded below, I believe I have the syntax correct
except on how to accept the “passed-filename†parm from the VBS file above.
----------------
Sub ImportRaceProgramData()
'
Dim file_name As Variant
Dim passed-filename As Variant ‘(added)
Worksheets("ProgramDataInput").Range("A3:H242").ClearContents
‘file_name = ThisWorkbook.Path & "\Cut-And-Paste-Into-XLS.txt" with
‘(replaced)
file_name = ThisWorkbook.Path & "\" & passed-filename with
Worksheets("ProgramDataInput").QueryTables.Add(Connection:= _
"TEXT;" & file_name _
…more…
Click to expand...
Click to expand...
 
R

Rowan

I think you need to remove the "As String" ie
Sub StartRaceBetting(passed_filename)

Regards
Rowan
I can’t get it to work. It says error on Line 1 Char 38. Expecting ‘)’
I will be executing the RaceBetting.vbs from a COMMAND PROMPT.

C:\xxxxxx\xxxxxx> RaceBetting.vbs myfile.txt

[RaceBetting.vbs]
-----------------------
Sub StartRaceBetting(passed_filename As String)
Dim XLApp
Dim XLWkb
MsgBox passed_filename
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = true
XLApp.Workbooks.Open "RaceBetting.xls"
XLApp.ActiveWorkbook.RunAutoMacros 1
End Sub
-------------------

I am hoping to have the VBS above accept the parm and then to pass it onto
the macro.


:

You pass a variable by using arguments after the macro name eg:

Sub calling()
Dim passed_filename As String
passed_filename = "myfile"
Call ImportRaceProgramData(passed_filename)
End Sub

Sub ImportRaceProgramData(passed_filename As String)
MsgBox passed_filename
End Sub


Hope this helps
Rowan

I have a REXX program that runs and now outputs different TXT files depending
on a Race Track. At the end of the REXX is run the following VBS that opens
Excel. I want to pass the VBS a variable filename.
In REXX I would call ‘RaceBetting.vbs’ filename

How can I set up the VBS to accept the parm when run? And then how can I
then pass this to the macro within Excel (below)
[RaceBetting.vbs]
--------------
Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = true
XLApp.Workbooks.Open "RaceBetting.xls"
XLApp.ActiveWorkbook.RunAutoMacros 1
----------------

In my macro I have the following code.
Note “filename†was hardcoded below, I believe I have the syntax correct
except on how to accept the “passed-filename†parm from the VBS file above.
----------------
Sub ImportRaceProgramData()
'
Dim file_name As Variant
Dim passed-filename As Variant ‘(added)
Worksheets("ProgramDataInput").Range("A3:H242").ClearContents
‘file_name = ThisWorkbook.Path & "\Cut-And-Paste-Into-XLS.txt" with
‘(replaced)
file_name = ThisWorkbook.Path & "\" & passed-filename with
Worksheets("ProgramDataInput").QueryTables.Add(Connection:= _
"TEXT;" & file_name _
…more…
Click to expand...
Click to expand...
Click to expand...
 
C

CRayF

OK, that removed the error... is there a way to test if it received the
parm... it simply returns be to the prompt and MsgBox passed_filename does
not generate a popup.
I'm not at the receiving macro yet... I just want to first test that it
picked up the variable...

-----------------
Sub StartRaceBetting(passed_filename)

Dim XLApp
Dim XLWkb

MsgBox passed_filename

Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = true
XLApp.Workbooks.Open "RaceBetting.xls"
XLApp.ActiveWorkbook.RunAutoMacros 1
End Sub
-----------------

Rowan said:
I think you need to remove the "As String" ie
Sub StartRaceBetting(passed_filename)

Regards
Rowan
I can’t get it to work. It says error on Line 1 Char 38. Expecting ‘)’
I will be executing the RaceBetting.vbs from a COMMAND PROMPT.

C:\xxxxxx\xxxxxx> RaceBetting.vbs myfile.txt

[RaceBetting.vbs]
-----------------------
Sub StartRaceBetting(passed_filename As String)
Dim XLApp
Dim XLWkb
MsgBox passed_filename
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = true
XLApp.Workbooks.Open "RaceBetting.xls"
XLApp.ActiveWorkbook.RunAutoMacros 1
End Sub
-------------------

I am hoping to have the VBS above accept the parm and then to pass it onto
the macro.


:

You pass a variable by using arguments after the macro name eg:

Sub calling()
Dim passed_filename As String
passed_filename = "myfile"
Call ImportRaceProgramData(passed_filename)
End Sub

Sub ImportRaceProgramData(passed_filename As String)
MsgBox passed_filename
End Sub


Hope this helps
Rowan


CRayF wrote:

I have a REXX program that runs and now outputs different TXT files depending
on a Race Track. At the end of the REXX is run the following VBS that opens
Excel. I want to pass the VBS a variable filename.
In REXX I would call ‘RaceBetting.vbs’ filename

How can I set up the VBS to accept the parm when run? And then how can I
then pass this to the macro within Excel (below)
[RaceBetting.vbs]
--------------
Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = true
XLApp.Workbooks.Open "RaceBetting.xls"
XLApp.ActiveWorkbook.RunAutoMacros 1
----------------

In my macro I have the following code.
Note “filename†was hardcoded below, I believe I have the syntax correct
except on how to accept the “passed-filename†parm from the VBS file above.
----------------
Sub ImportRaceProgramData()
'
Dim file_name As Variant
Dim passed-filename As Variant ‘(added)
Worksheets("ProgramDataInput").Range("A3:H242").ClearContents
‘file_name = ThisWorkbook.Path & "\Cut-And-Paste-Into-XLS.txt" with
‘(replaced)
file_name = ThisWorkbook.Path & "\" & passed-filename with
Worksheets("ProgramDataInput").QueryTables.Add(Connection:= _
"TEXT;" & file_name _
…more…
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Call an XLS that is one directory “UP†from where the VBS file is? 1
VBS starts Excel. How to point to different macro when run? 3
Is there a way to test first if MySampleAll.xls is already open 4
How to disable MS Excel Clipboard message when I run a macro 1
$ for a FrontPage macro to insert a particular file into the current document 0

Top