1

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.

Community
  • 1
  • 1
Safinn
  • 622
  • 3
  • 15
  • 26
  • possible duplicate of [passing argument from vba to vbs](http://stackoverflow.com/questions/18376588/passing-argument-from-vba-to-vbs) – JimmyPena Oct 25 '13 at 18:06

2 Answers2

2

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
rory.ap
  • 34,009
  • 10
  • 83
  • 174
  • If I entered the variable name in the quotes it would send the variable name and not the value it has right? EDIT: Yes, the quotes cannot hold variables. Only strings. Any way around that? – Safinn Oct 25 '13 at 13:31
  • You can do this: Shell "wscript C:\Users\name\Desktop\vb.vbs """ & strMyPath & """", vbNormalFocus – rory.ap Oct 25 '13 at 13:38
  • 2
    Or this might be easier to read: Shell "wscript C:\Users\name\Desktop\vb.vbs " & Chr(34) & strMyPath & Chr(34), vbNormalFocus. Chr(34) returns the double-quote character. – rory.ap Oct 25 '13 at 13:39
1

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"

Community
  • 1
  • 1
Zzirconium
  • 431
  • 1
  • 10
  • 32
  • Worked great, only thing is that the text behind WScript.Arguments(0) would stop at an spaces for if you had: MyCompletePath = "to to" MsgBox("Hello " & WScript.Arguments(0)) It would return here: "to" – Safinn Oct 25 '13 at 13:29
  • roryap answer holds the solution for that : add escaped double quotes around each spaced argument so that it is read as a consistent whole :) – Zzirconium Oct 25 '13 at 13:32