CODEWITHSUNDEEP
vbaexcelvbscript
Safinn
Safinn

Reputation: 642

Pass Variable from Excel Macro to .vbs file

I have an excel button with a macro. The code simply stores the path of the workbook in a vaiable names 'MyCompletePath' and then runs a .vbs file. The code is:

MyCompletePath = ActiveWorkbook.FullName

'Run VBS file
Shell "wscript C:\Users\name\Desktop\vb.vbs", vbNormalFocus

I want to pass the variable 'MyCompletePath' from the excel file to the file which is executed using the last line.

I made some searches but didn't fully understand what their solutions do. Maybe someone can tell me how to do it.

UPDATE/EDIT: I'm now having a problem with the filepath after Shell "wscript. It has spaces in the folder name. How can I get it to work with spaces in the name?

Thank you.

Upvotes: 1

Views: 3318

Answers (2)

rory.ap
rory.ap

Reputation: 35328

You can include command line arguments after the path to your VBS file. For example:

Shell "wscript C:\Users\name\Desktop\vb.vbs BlahBlahBlah", vbNormalFocus

Then inside your VBS script, you can access them using the WScript.Arguments collection. For example:

MsgBox WScript.Arguments(0)

would pop up a message box displaying "BlahBlahBlah".

For a file path, as you indicated, which might include spaces and thus would be treated as multiple arguments to the script, I would include the argument in quotes, like this:

Shell "wscript C:\Users\name\Desktop\vb.vbs ""this has multiple words""", vbNormalFocus

Upvotes: 2

Zzirconium
Zzirconium

Reputation: 471

based on passing argument from vba to vbs

When I write that within the vba:

Sub Macro1()

MyCompletePath = "toto"

Shell "wscript D:\\vb.vbs " & MyCompletePath

End Sub

and this in vb.vbs

MsgBox("Hello " & WScript.Arguments(0))

I do get "Hello toto"

Upvotes: 1

Related Questions