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").Cl earContents
€˜file_name = ThisWorkbook.Path & "\Cut-And-Paste-Into-XLS.txt" with
€˜(replaced)
file_name = ThisWorkbook.Path & "\" & passed-filename with
Worksheets("ProgramDataInput").QueryTables.Add(Con nection:= _
"TEXT;" & file_name _
€¦more€¦

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").Cl earContents
€˜file_name = ThisWorkbook.Path & "\Cut-And-Paste-Into-XLS.txt" with
€˜(replaced)
file_name = ThisWorkbook.Path & "\" & passed-filename with
Worksheets("ProgramDataInput").QueryTables.Add(Con nection:= _
"TEXT;" & file_name _
€¦more€¦

I cant 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" wrote:

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").Cl earContents
€˜file_name = ThisWorkbook.Path & "\Cut-And-Paste-Into-XLS.txt" with
€˜(replaced)
file_name = ThisWorkbook.Path & "\" & passed-filename with
Worksheets("ProgramDataInput").QueryTables.Add(Con nection:= _
"TEXT;" & file_name _
€¦more€¦

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

Regards
Rowan

CRayF wrote:
I cant 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" wrote:


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€¦

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" wrote:

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

Regards
Rowan

CRayF wrote:
I cant 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" wrote:


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€¦