Passing variables from VBA to VBScript

[keith0528]

Greetings,

I have a line of code in my excel vba code that calls to a VBScript:

Shell "wscript C:\Users\kev\Documents\VBScript_Stuff\Copy_File_to_Splitter.vbs"

I want the VBScript to receive a variable "sFile" a string, which contains a file name and add that to the file path in VBScript. The VBScript exists outside of the VBA code.

vbscript:
Set objFSO = CreateObject("Scripting.FileSystemObject")
FileSystemObject.CopyFile "\\abc\LoMo\ImportFileLoadingDock\KeLance\" & TodaysFolder & "\" & sFile, "destination path"

I've searched the message boards but haven't found anything that fits the bill. Does anyone have knowledge of how to do this?

thanks,
Keith

 

[tweedle]

With the vba, you need to build out the parameter syntax.
With the vbs, instead of variable, use the WScript.Arguments() to get the parameter(s) passed from vba.

With the VBA
Shell "wscript C:\Users\kev\Documents\VBScript_Stuff\Copy_File_to_Splitter.vbs " & "" & "{yourfilename}" & ""

With the VBS
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile "\\abc\LoMo\ImportFileLoadingDock\KeLance\" & TodaysFolder & "\" & WScript.Arguments(0), "destination path"




FYI: You can create fso in VBA as well.

 

[keith0528]

Hi Tweedle,

Thanks so much for your reply. When I tried it i got the following error: "There is no script engine for file extension ".vbs{TodaysFolder}{sFile1}".

I know i'm doing something wrong but don't know enough to know what. I actually need to pass 2 arguments into the source path (a current date, and file name). In the Here is what I used:

VBA:

Shell "wscript C:\Users\kev\Documents\VBScript_Stuff\Copy_File_to_Splitter.vbs" & "{TodaysFolder}" & "{sFile1}"""

VBS:

Set objFSO = CreateObject("Scripting.FileSystemObject")
FileSystemObject.CopyFile "\\abc\LoMo\ImportFileLoadingDock\KeLance\" & WScript.Arguments(0) & "\" & WScript.Arguments(1)", _
"\\abc\lomo\ImportFileLoadingDock\LMWDENSplitter\ & WScript.Arguments(1)"


your comments/corrections are greatly appreciated.

 

[John_w]

You need a space to separate the .vbs file name and each argument. Like this:

    Shell "wscript C:\Users\kev\Documents\VBScript_Stuff\Copy_File_to_Splitter.vbs" & " {TodaysFolder}" & " {sFile1}"

Also, in case any argument (including the .vbs argument) has an embedded space you should surround it with double quotes (") so that it is passed to wscript correctly - like this (I'm guessing that TodaysFolder and sFile1 are string variables):

    Const Q = """"
    Shell "wscript ""C:\Users\kev\Documents\VBScript_Stuff\Copy_File_to_Splitter.vbs"" " & Q & TodaysFolder & Q & " " & Q & sFile1 & Q

 

[keith0528]

thank you John W!

That took the focus off the VBA part. I'm getting close now. I'm now getting an error on the VBS side. The error is code: 800A0401 Expected end of statement. line 3 char: 135. If i counted right it's the double quote i have in red in the code. However, if i take out the dbl quote the error changes to "Object required: 'FileSystemObject' char 1 line 3


Set objFSO = CreateObject("Scripting.FileSystemObject")

FileSystemObject.CopyFile "\\abcdef\LoanMods\ImportFileLoadingDock\KeithLxxxxxxx\" & WScript.Arguments(0) & "\" & WScript.Arguments(1)", _
"\\abcdef\loanmods\ImportFileLoadingDock\LMWDENSplitter\ & WScript.Arguments(1)"


regards,
Keith

 

[John_w]

You're defining objFSO, but use the undefined FileSystemObject variable to invoke the CopyFile method. Try:

Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.CopyFile "\\abcdef\LoanMods\ImportFileLoadingDock\KeithLxxxxxxx\" & WScript.Arguments(0) & "\" & WScript.Arguments(1)", _
 "\\abcdef\loanmods\ImportFileLoadingDock\LMWDENSplitter\ & WScript.Arguments(1)"

PS - as previously said, VBA can call FileSystemObject methods so you shouldn't need to use a separate VBScript to copy a file.

 

[John_w]

I've just spotted the double quotes you were talking about. Try instead:

Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.CopyFile "\\abcdef\LoanMods\ImportFileLoadingDock\KeithLxxxxxxx\" & WScript.Arguments(0) & "\" & WScript.Arguments(1), _
 "\\abcdef\loanmods\ImportFileLoadingDock\LMWDENSplitter\" & WScript.Arguments(1)

 

[keith0528]

Hi John,

Yeah, i tried that and when i do it that way I get the error, 800A004C Path not found. line 3 char 1.

could it be another spacing issue?

By the way, I don't know if it makes a difference but the argument 0 represents a date data type and argument 1 is a string.



Keith

 

[tweedle]

@Keith;
Can you provide an example path? (today's maybe)
Probably need to format the date to whatever it looks like in the folder-path.

 

[keith0528]

Hi Tweedle,

I ran the code with the values hard coded in the paths and it worked perfectly. Which leads me to believe that there is something wrong with the syntax of the arguments or a missing dbl quote or something. I'll show you both the hard coded example and the real one:

here is the hard coded example:
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile "\\xxxxxx\LoanMods\ImportFileLoadingDock\KeithLxxx\20130320\BAxxx_xx_03202013.xlsx", _
"\\xxxxxx\loanmods\ImportFileLoadingDock\LMWDENSplitter\BAxxx_xx_03202013.xlsx"


w/ arguments:

Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile "\\xxxxxx\LoanMods\ImportFileLoadingDock\KeithLxxx\" & WScript.Arguments(0) & "\" & WScript.Arguments(1), _
"\\xxxxxx\loanmods\ImportFileLoadingDock\LMWDENSplitter\" & WScript.Arguments(1)