on 03-17-2016 9:39 AM
Dear Community,
I'm new to this forum and also new as VBA developer. Currently I'm working on the automation project in which i need to enter specific details on SAP (730) and generate report which look like excel-sheet but its not. When I select export to excel workbook the SAP save as window pops-up and I'm unable to access that window through macro and I was stuck there. Then I have identified many similar cases in this forum and provided solutions for it.
I tried to use below solution by calling separate vb script to control Save As window through my VBA code, but I'm stuck when there is any space in the file path where I need to save these files. I'm not much aware of SAP and vb script. Do I need to change any specific code below ?
Any help is much appreciated..thanks in advance.
My Code is as below:
session.findById("wnd[0]").sendVKey 8 'after this line the SaveAs window pop-up
FilePath = "c:\tmp\Sap download file\test1.xlsx") 'here is some space in file path
set Wshell = CreateObject("WScript.Shell")
wshell.run """c:\tmp\save_as.vbs """ & FilePath
session.findById("wnd[0]/tbar[1]/btn[16]").press
Save_as.vbs includes:
if Wscript.Arguments.count > 0 then
set fs = CreateObject("Scripting.FileSystemObject")
if fs.fileExists(WScript.arguments(0)) then
Set myfile = fs.GetFile(WScript.arguments(0))
myfile.Delete
end if
set Wshell = CreateObject("WScript.Shell")
Do
bWindowFound = Wshell.AppActivate("Save As")
WScript.Sleep 1000
Loop Until bWindowFound
Do
Wshell.appActivate "Save As"
Wshell.sendkeys WScript.arguments(0)
WScript.Sleep 400
Wshell.appActivate "Save As"
Wshell.sendkeys "%s"
WScript.Sleep 400
bWindowFound = Wshell.AppActivate("Save As")
WScript.Sleep 400
Loop Until not bWindowFound
end if
Hello Sagar,
welcome in the Scripting Language forum.
As far as I understand you correct, you want to control a Windows "Save As" dialog via VBA.
You can find an excellent solution for this requirement from Holger Köhn here.
Let us know your results.
Cheers
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Stefan,
I tried with Holger Kohn's solution, but don't know what I'm doing wrong.
I have created another module and inserted Holger Kohn's code and from my main module I'm calling it. I have added the call function after the line where SaveAs window pop-up. Is it correct ? as the code stop at the SaveAs window. After I manually click on cancel / save button, only then it start executing the rest of the code/line.
I'm I doing it correct..please help me out in this...
Code:
If Not IsObject(App) Then
Set SapGuiAuto = GetObject("SAPGUI") 'Get the SAP GUI Scripting objects
Set App = SapGuiAuto.GetScriptingEngine 'Get the currently running SAP GUI
End If
If Not IsObject(connection) Then
Set connection = App.Children(0) 'Get the first system that is currently connected
End If
If Not IsObject(session) Then
Set session = connection.Children(0) 'Get the first session (window) on that connection
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject App, "on"
End If
AppActivate session.findById("wnd[0]").Text 'to bring the SAP-window to the foreground.
For i = 8 To cnt
application.StatusBar = "Working on " & wks.Range("B" & i).Value & " ...."
'SAP T code
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "zfi_jva_sst"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/txtENAME-LOW").Text = ""
session.findById("wnd[1]/usr/txtENAME-LOW").SetFocus
session.findById("wnd[1]/usr/txtENAME-LOW").caretPosition = 0
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").setCurrentCell 24, "TEXT"
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").firstVisibleRow = 15
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").selectedRows = "24"
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").doubleClickCurrentCell
'Input ASL number
session.findById("wnd[0]/usr/ctxtS_KUNNR-LOW").Text = wks.Range("B" & i).Value
'Input report generated date
session.findById("wnd[0]/usr/ctxtS_BUDAT-HIGH").Text = GenDate
'Input report (period) date
session.findById("wnd[0]/usr/ctxtP_ASDAT").Text = ReportDate
session.findById("wnd[0]/usr/ctxtS_KUNNR-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtS_KUNNR-LOW").caretPosition = 8
session.findById("wnd[0]/tbar[1]/btn[8]").press
'Application.Wait (Now + TimeValue("0:00:01"))
If Err.Number = 619 Then
wks.Range("D" & i).Value = "No details available"
Else
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").firstVisibleRow = 11
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").firstVisibleColumn = "DEAL_PARENT"
'///////////////////////////////////////////////////////////////////////////////////////
'code to export / download excel file
'///////////////////////////////////////////////////////////////////////////////////////
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").pressToolbarContextButton "&MB_EXPORT" 'old
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").selectContextMenuItem "&XXL" 'old
FilePath1 = FilePath & wks.Range("B" & i).Value & ".xlsx" Declaring file path
session.findById("wnd[0]/tbar[1]/btn[8]").press 'Here on this line SaveAs window pop-up & code stop executing unless I manually click on cancel/save button
Call Auto_SaveAs_SAP(FilePath1) 'here I'm calling Holger Kohn code
......
.....
.....
Hello Sagar,
I modify Holgers code a little bit
'==> Get the handle of the Save Button in the Save As Dialog Box
ChildRet = FindWindowEx(Ret, ByVal 0&, ByVal "Button", ByVal "Cancel")
'ChildRet = FindWindowEx(Ret, ByVal 0&, ByVal "Button", ByVal "Abbrechen")
ChildRet = GetWindow(ChildRet, GW_HWNDPREV)
because I have some trouble with German umlauts (Sch&reibgeschützt öffnen instead Sch&reibgeschützt Öffnen) and it works perfect.
I copy a spool number from SP02 in the clipboard, call the report RSTXPDFT4 via SE38, paste the spool number into the spool request and execute the report. So it opens a "Save As" dialog for a PDF. Now I start Holgers code from VBA and with the modification it works perfect. Please try the same way to check if it basically works.
You use the transaction code zfi_jva_sst, it is an individual TAC, so it is not possible to support you for your detail requirement. It is in this case necessary to abstract your case.
Let us know your results.
Cheers
Stefan
If will not work when you call SaveAS code from same Excel instance where your SAP code is called from.
The VBA code is waiting until it got an 'executed' Event back.
So you Need to let SaveAs code run in a seperate Excel Instance (e.g. in a Loop until it got handle of SaveAS MS dialogue window).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.