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

 

[keith0528]

I forgot to mention the date type variable: TodaysFolder = Format(VBA.Date(), "YYYYMMDD") this is used as argument 0
Shell "wscript C:\Users\klxxx\Documents\VBScript_Stuff\Copy_File_to_Splitter.vbs" & " {TodaysFolder}" & " {sFile1}"


thanks,
Keith

 

[John_w]

Try this:

Sub vba()
    Const Q = """"
    Dim TodaysFolder As String, sFile1 As String
    TodaysFolder = Format(Date, "YYYYMMDD")
    sFile1 = "BAxxx_xx_03202013.xlsx"   'hard-coded file name for testing
    
    Shell "wscript ""C:\Users\kev\Documents\VBScript_Stuff\Copy_File_to_Splitter.vbs"" " & Q & TodaysFolder & Q & " " & Q & sFile1 & Q
End Sub

'Copy_File_to_Splitter.vbs
MsgBox ">" & WScript.Arguments(0) & "<" & vbNewLine & ">" & WScript.Arguments(1) & "<"
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile "\\abcdef\LoanMods\ImportFileLoadingDock\KeithLxxxxxxx\" & WScript.Arguments(0) & "\" & WScript.Arguments(1), _
			    "\\abcdef\loanmods\ImportFileLoadingDock\LMWDENSplitter\" & WScript.Arguments(1)

 

[tweedle]

@Keith;
If you ever decide to move away from vbs to just copy files, here are some examples of vba-only and vba using fso: Copy, Move and Delete files and folders