VBA wait on VBScript to finish?

[BravesPiano5]

Hello!

I am completely stumped; after hours of testing and hours of looking online for an answer, I cannot figure out how to resolve this issue.

I have the following code in VBA:

Sub RunExport()

Call VBScriptTEST(filepath, filename)
'The filepath and filename is declared elsewhere
End Sub

Sub VBScriptTEST(ByVal FilePath As String, ByVal FileName As String)
Dim wsh As Object
Dim Counter As Long

Set wsh = CreateObject("WScript.Shell")
wsh.Run """C:\Username\SAPSaveAs.vbs"" """ & FilePath & "\" & "" & FileName & ".xlsx" & """", 1, True

End Sub

I am using an ERP application (SAP) to run a transaction and export data using its own scripting language; I have to run a VBS file to export the data because the MS dialog "Save As" prompt is not recognized by the ERP application and this is the best workaround I can find/use.

Here is my code from VBScript:

Dim xlApp

Set Wshell = CreateObject("WScript.Shell")
Filepath = WScript.Arguments(O)
Filename = Mid(Filepath, InStrRev(Filepath, "\", -1) + 1, Len(Filepath) - InStrRev(Filepath, "\", -1))

Do
    bWindowFound = Wshell.AppActivate("Save As")  
    WScript.sleep 200
Loop Until bWindowFound

WScript.sleep 200
Wshell.sendkeys "%n"
WScript.sleep 2000
Wshell.sendkeys Filepath
WScript.sleep 3000
Wshell.sendkeys "%S"
WScript.sleep 5000
Wshell.sendkeys "%Y" 'In case the file already exists

Set xlApp = GetObject(, "Excel.Application")
xlApp.visible = true

Do
    WScript.sleep 400
Loop Until xlApp.ActiveWorkbook.Name = Filename

WScript.sleep 5000

Set Filepath = nothing
Set Filename = nothing
Set xlApp = nothing

The VBScript exports the data and saves it to the specified filepath without issue. With how the ERP application works, the downloaded file automatically opens within Excel; I already have an existing instance of Excel running (as the VBA macro called this VBScript to execute)...

The ISSUE is that I want VBA to wait until the exported file is available in Excel to manipulate but the VBA code is not waiting for the VBScript to finish and fully download the file. I've tried running a Do/Loop and have VBA wait for the Activeworkbook.name = Filename but it just holds Excel and won't download the file unless I stop VBA. Similarly, I've found that running a Do/Loop (like what I have above) in VBScript isn't working either.

I'm just...stumped. Any help would be appreciated!!

[SamT]

Filepath = WHICH WScript.Arguments?

VBA code is not waiting

Do
    WScript.sleep 400
DoEvents
Loop Until xlApp.ActiveWorkbook.Name = Filename

[BravesPiano5]

I found some code online that could pass an argument from VBA to VBS...I'm passing the fullpath of where I want to save the file, then parsing that within the VBS into what the filename is.

That particular loop was an idea to hold the script until the file had downloaded but VBS is just holding and not opening the file.

[SamT]

parsing that within the VBS into what the filename is.

In VBA...

FileName = Right(FullName, InStrRev(FullName, "\" + 1)

I see you working with 3 or 4 scripting languages + VBA across at least 2 applications. And your descriptions of them is sloppy... You call some particular Script "the script." You use "VBS" when it looks like you mean "VBA." So We really can not tell what you are trying to say.

We here, are all Geeks, we don't speak colloquial, or even generical. Because we are all so used to Code, we all speak, and read, very specifically.

Personally, I would use VBA as the interface for each of all the other languages and as often as possible between Applications

Ex: Application1 >> Script1 >> VBA >> Script2 >> Application >> Script >> VBA >> Script >> Application2 >> Script >> VBA >> Etc

That would allow easier error control and troubleshooting.

Since VBA can "create" "objects," have you tried anything like

ABC.vs = Vscript file

Dim ABC as  Object ', or as Variant
Set ABC = C:\ABC.vs

Will it work? ... :

[SamT]

I am using an ERP application (SAP) to run a transaction and export data using its own scripting language;

Why can't SAP save the data it exports to a file?




What applications must manipulate the data before it gets to its final destination.



I'm trying to get a clear picture of what must happen. I won't consider any more code until I understand that.