CODEWITHSUNDEEP
vbavbscriptargument-passing
Kay
Kay

Reputation: 2854

Pass Argument from VBS to VBA

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
'------------------------

Upvotes: 7

Views: 20816

Answers (2)

junglejim63
junglejim63

Reputation: 121

Addendum to @user69820 answer, if arguments are VBScript variables, they need to be cast as appropriate type before calling the subroutine:

This does not work:

dim argumentVariable
argumentVariable = "an argument"
wd.run "test", argumentVariable

This does:

dim argumentVariable
argumentVariable = "an argument"
wd.run "test", CStr(argumentVariable)

Tested on Excel 2010, Win7SP1 x64

Upvotes: 12

user69820
user69820

Reputation:

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.

Upvotes: 12

Related Questions