Pass Argument from VBS to VBA
argument-passing, vba, vbscript
Solution
You need to specify parameters in your VBA `Sub` and use them as you would do if using it from VBA normally.
For example, I tried the following VBScript
dim wd: set wd = GetObject(,"Word.Application")
wd.Visible = true
wd.run "test", "an argument"
and the VBA
Sub Test(t As String)
MsgBox t
End Sub
which worked successfully, generating a message box.
Problem
I try to call a VBA subroutine from VBS with passing a string variable from VBS to VBA, but can't find the appropiate syntax: ``` 'VBS: '------------------------ Option Explicit Set ArgObj = WScript.Arguments Dim strPath mystr = ArgObj(0) '? 'Creating shell object Set WshShell = CreateObject("WScript.Shell") 'Creating File System Object Set objFSO = CreateObject("Scripting.FileSystemObject") 'Getting the Folder Object Set ObjFolder = objFSO.GetFolder(WshShell.CurrentDirectory) 'Getting the list of Files Set ObjFiles = ObjFolder.Files 'Creat a Word application object Set wdApp = CreateObject("Word.Application") wdApp.DisplayAlerts = True wdApp.Visible = True 'Running macro on each wdS-File Counter = 0 For Each objFile in objFiles If UCase(objFSO.GetExtensionName(objFile.name)) = "DOC" Then set wdDoc = wdApp.Documents.Open(ObjFolder & "\" & ObjFile.Name, 0, False) wdApp.Run "'C:\Dokumente und Einstellungen\kcichini\Anwendungsdaten\Microsoft\Word\STARTUP\MyVBA.dot'!Test_VBA_with_VBS_Args" (mystr) 'how to pass Argument??? Counter = Counter + 1 End if Next MsgBox "Macro was applied to " & Counter & " wd-Files from current directory!" wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing '------------------------ 'VBA: '------------------------ Sub Test_VBA_with_VBS_Args() Dim wdDoc As Word.Document Set wdDoc = ActiveDocument Dim filename As String Dim mystr As String 'mystr = how to recognize VBS-Argument ??? filename = ActiveDocument.name MsgBox "..The file: " & filename & " was opened and the VBS-Argument: " & mystr & "recognized!" wdDoc.Close End Sub '------------------------ ```