|
Passing parms to a VBS file or a macro when executed?
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 |
Passing parms to a VBS file or a macro when executed?
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 |
Passing parms to a VBS file or a macro when executed?
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 |
Passing parms to a VBS file or a macro when executed?
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 |
Passing parms to a VBS file or a macro when executed?
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 |
| All times are GMT +1. The time now is 10:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com