Pass variable from Batch to VBS

Question

Hi I am trying to pass a user input variable from a BAT to VBS script

Im sure this can be done in VBS but the user "Filename" input is used also later in the BAT file

As you can see the "FileName" variable in the .bat section needs to be passed into the VBS script for the file path ("C:\Users\bob\Documents\ %FileName%")

.Bat

set /p FileName= Enter Filename Including Extention e.g. test.xlsx

VBS:

Set xlObj = CreateObject("Excel.Application")
Set xlFile = xlObj.WorkBooks.Open("C:\Users\xxx\Documents\%FileName%")
'turn off screen alerts
xlObj.Application.DisplayAlerts = False
'loop through sheets
For Each Worksheet In xlFile.Worksheets 
'change sheet to desired worksheet name
If Worksheet.Name = "Old111" Then
Worksheet.Name = "NewName111"
End if
Next
'save, close, then quit
xlFile.Close True
xlObj.Quit

Answer 1

I wouldn't recommend passing information from one script to another via environment variables. I suppose you're running the VBScript from the batch script? In that case you could simply pass the filename as an argument to the VBScript:

set /p "FileName= Enter Filename Including Extention (e.g. test.xlsx): "
cscript.exe //NoLogo C:\path\to\your.vbs "%FileName%"

In the VBScript you process the argument like this:

filename = WScript.Arguments.Unnamed(0)

You could also use a name argument:

set /p "FileName= Enter Filename Including Extention (e.g. test.xlsx): "
cscript.exe //NoLogo C:\path\to\your.vbs /filename:"%FileName%"

with the argument evaluation in your VBScript looking like this:

filename = WScript.Arguments.Named("filename")