cancel
Showing results for 
Search instead for 
Did you mean: 

Exporting or saving report to excel from SAP 7.30

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

stefan_schnell
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

......
.....
.....

stefan_schnell
Active Contributor
0 Kudos

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

holger_khn
Contributor
0 Kudos

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).

Former Member
0 Kudos

Thanks Stefan and Holger for your time.. really appriciated.
Now I understood what I was doing wrong.

Thanks again.

Answers (0)