How to run VBScript inside VBA

[Gregreg]

<tbody>
[TD="class: votecell"][/TD]
[TD="class: postcell"] I want VBScript to read variable value from VBA as the file directory (<code>Wshell.SendKeys "C:\testfile.txt")</code>will change when I run VBA in loop. I would like to have data as list in Excel and tell VBA to read and then VBScript to paste in Import file window (window displayed by SAP).I know i can run the below script from a separate file and it works. I also read on the net that VBscript can be run inside VBA, however, I can't figure out how to change the code to make it work. I'm not developer, but I learned enough to adjust most of the VBA to my needs. Please assist and advise which command would run the below inside VBA or how to adjust it to work in VBA?
Many thanks in advance.

VBScript:

<code>Set Wshell = CreateObject("WScript.Shell")

MsgBox "in sub"

Do
bWindowFound = Wshell.AppActivate("Import file")
WScript.Sleep 1000
Loop Until bWindowFound

MsgBox "Window = 'Import file' found"

bWindowFound = Wshell.AppActivate("Import file")

If (bWindowFound) Then
Wshell.AppActivate "Import file"

WScript.Sleep 100

Wshell.SendKeys "{tab}"
Wshell.SendKeys "{tab}"
Wshell.SendKeys "{tab}"
Wshell.SendKeys "{tab}"
Wshell.SendKeys "{tab}"
Wshell.SendKeys "C:\testfile.txt"
WScript.Sleep 100

Wshell.SendKeys "{enter}"
WScript.Sleep 100
End If</code>

[/TD]

</tbody>

 

[John_w]

Use the Shell function to run the .vbs script with the file name argument:

Public Sub Run_VBScript()

    Dim VBScriptFile As String
    Dim scriptArg As String
    
    scriptArg = "C:\testfile.txt"
    
    VBScriptFile = "C:\Path\To\Your_VBScript.vbs"   'CHANGE AS NEEDED
    
    Shell "wscript " & Q(VBScriptFile) & " " & Q(scriptArg), vbNormalFocus
    
End Sub

Private Function Q(text As String) As String
    Q = Chr(34) & text & Chr(34)
End Function

Within the .vbs file, get the file name argument like this:

Option Explicit

If WScript.Arguments.count > 0 Then
    MsgBox "The file name argument supplied by VBA is " & WScript.Arguments(0)
Else
    MsgBox "Argument not supplied"
End If

 

[Gregreg]

Hi John,

Thank you for this. As far as I understand this will allow me to send variables to VBS file saved on the drive, correct?
What if I wanted to integrate the VBS code inside excel - i.e. in module?
Is that possible?

Rgds,
Greg

 

[John_w]

Yes, my code shows how to send variables (arguments) to the VBScript file.

VBScript code is similar to VBA and can be converted to VBA with a few changes. For example, you don't need WShell and therefore "WShell." should be deleted from the AppActivate and SendKeys lines. The code can then be run in Excel in a VBA module. However, the VBA versions of AppActivate and SendKeys are unreliable - they might work or they might not or you might get varying results. If your VBScript code works successfully as it is then I would stick to VBScript rather than converting it to VBA.

 

[Gregreg]

Thank you John. I have had no time to test it yet, but will let you know as soon as I do it.
Many thanks in advance.
Greg