BravesPiano5
03-17-2018, 11:47 AM
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!! :crying:
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!! :crying: