excel vba call vbscript with named arguments

Question

I am trying to call a VBScript file from an Excel VBA Macro. The VBScript has named arguments.

My VBScript is as follows.

dim argument1
argument1 = WScript.Arguments.Named.Item("argument1")

if argument1 = "" then
    WScript.Echo "No filepath provided. Aborting."
    WScript.Quit
end if

WScript.Echo "Launching Excel..."
dim excelInstance
set excelInstance = CreateObject("Excel.Application")

dim Application
set Application = excelInstance.Application

excelInstance.Visible = false
Application.ScreenUpdating = false

WScript.Echo "Opening Excel file..."
dim File1 
set File1 = OpenWorkbook(excelInstance, argument1)

My Excel VBA Macro is as follows.

Sub CreateOutput()
    Dim filePath, fileDir, shellCommand, vbScriptPath
    fileDir = ThisWorkbook.Path
    filePath = fileDir & "\exampleFile.xlsx"
    vbScriptPath = fileDir & "\VBScript1.vbs"
    shellCommand = vbScriptPath & " " & Chr(34) & filePath & Chr(34)
    
    Set wshShell = CreateObject("Wscript.Shell")
    wshShell.Run """" & shellCommand & """"
    
End Sub

Upon executing the Macro I am getting a popup with the message "No filepath provided. Aborting." Any suggestions on how to pass the path to exampleFile.xlsx as a named argument?

I think the VBA should pass the argument as "Argument:parameter" i.e. the name of the prameter should be in the command line arguments, otherwise vbscript has no way of identifying the argument. It might be clearer is you change the name argument1 to "myFilePath" and pass myFilePath: — freeflow, Oct 10 '20 at 17:24
Stop asking questions that have been covered many times on [so], just search and you’ll [find the answer](https://stackoverflow.com/a/60447662/692942). — user692942, Oct 11 '20 at 09:00

FaneDuru · Answer 1 · 2020-10-10T20:56:15.237

1

Try the VBA Sub in this way, please:

Sub CreateOutput()
    Dim filePath, fileDir, shellCommand, vbScriptPath
    fileDir = ThisWorkbook.path
    filePath = fileDir & "\exampleFile.xlsx"
    vbScriptPath = fileDir & "\VBScript1.vbs"
    
    shellCommand = "cscript """ & vbScriptPath & """ """ & filePath & """"
    
    Shell shellCommand, vbHide
End Sub

Edited:

And VBScript code should look like this:

dim argument1

if WScript.Arguments.Count = 0 then
    WScript.Echo "No filepath provided. Aborting."
    WScript.Quit
end if
argument1= WScript.Arguments(0) 

'msgbox argument1
msgbox "Launching Excel..."
dim excelInstance
set excelInstance = CreateObject("Excel.Application")

excelInstance.Visible = true 'you may make it false after seeing it working.
'Application.ScreenUpdating = false
msgbox "Opening Excel file..."
dim File1 
set File1 = excelInstance.Workbooks.Open(argument1)

edited Oct 10 '20 at 20:56

answered Oct 10 '20 at 17:26

FaneDuru

38,298
4
19
27

I implemented the above mentioned suggestion and executed the Macro. Also I tested the macro with the following code 'File1.Range("I2")="Temp1"' to my VBScript. The exampleFile.xlsx file is not opened or edited. – busy bee Oct 10 '20 at 19:47
@busy bee: The best way of testing is to see the workbook opening. – FaneDuru Oct 10 '20 at 21:00
1

Actually you have completely changed the OPs code to not use named arguments. All the OP needs to do is pass `/argument:value` when calling the VBScript and the code will work. – user692942 Oct 11 '20 at 09:09
@Lankymart: Yes, so I did. Since he needs passing only a single argument and he does not look being experienced, I tried choosing the simplest way. Being in VBA, also using `Shell` VBA function. You can send many parameters/arguments in this way, too. Making a string and using a character separator then splitting it to extract the necessary parameter. – FaneDuru Oct 11 '20 at 09:36
My code executes correctly after implementing your suggestions. Thank you. – busy bee Oct 11 '20 at 14:21
@busy bee: Glad I could help! If, in the future you will need to use a second or a third argument, please build the command string in this way: `shellCommand = "cscript """ & vbScriptPath & """ """ & filePath & """ ""Test""" `. In this specific case, "Test" will be the second argument. It can be received in the VBScript code as `argument2= WScript.Arguments(1)`. OK. When a supplied solution solves your problem, we here tick the left code side check box, in order to make it **accepted answer**. When somebody else will search for something similar, he will know that the supplied solution works. – FaneDuru Oct 11 '20 at 14:45
It's `WScript.Arguments` 101 it’s been answered over 5 years+ ago, it doesn’t need people going over already answered questions when a language is over 20+ years old. This is the problem with so on [so] they don’t help the community they hinder it. – user692942 Oct 11 '20 at 18:21
@busybee be aware that approach is no longer using named arguments and if this really is the solution you can wanted it was [answered 10 years ago](https://stackoverflow.com/a/2806731/692942)! – user692942 Oct 11 '20 at 18:24

score 1 · Answer 2 · answered Oct 11 '20 at 05:54

Calling with named arguments: https://ss64.com/vb/syntax-args.html

Sub CreateOutput()
    
    Dim filePath, fileDir, shellCommand, vbScriptPath, wshShell
    
    fileDir = ThisWorkbook.Path
    filePath = fileDir & "\exampleFile.xlsx"
    
    vbScriptPath = fileDir & "\VBScript1.vbs"
    shellCommand = """" & vbScriptPath & """ /argument1:""" & filePath & """"
    
    Set wshShell = CreateObject("Wscript.Shell")
    wshShell.Run shellCommand
    
End Sub

It's [been answered before](https://stackoverflow.com/a/60104838/692942). — user692942, Oct 13 '20 at 07:59

excel vba call vbscript with named arguments

2 Answers2