Skip to Content
avatar image
Former Member

Saving extracted SAP report in Excel format into specified drive

Hello All,

I would like to ask on how I can save the report generated from SAP and save it to specified drive. I was trying the code given by @script.man but unfortunately, something is incorrect in my part. Kindly help.

Here's the code :

Public Sub GSAPpost()


AdjID = Sheet4.Cells(4, 11)
Contract = Sheet4.Cells(10, 4)
'filepathsave = Range("J29").value & "\"


If Month(Now) < 9 Then
    monthtoday = "0" & Month(Now)
Else
    monthtoday = Month(Now)
End If


If Day(Now) < 9 Then
    daytoday = "0" & Day(Now)
Else
    daytoday = Day(Now)
End If
  


Set SapGuiAuto = GetObject("SAPGUI")  'Get the SAP GUI Scripting object
Set SAPApp = SapGuiAuto.GetScriptingEngine 'Get the currently running SAP GUI
Set SAPCon = SAPApp.Children(0) 'Get the first system that is currently connected
Set session = SAPCon.Children(0) 'Get the first session (window) on that connection


session.findById("wnd[0]/tbar[0]/okcd").Text = "/NREAJSH"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtP_PEXTID").Text = AdjID
session.findById("wnd[0]/usr/ctxtP_PEXTID").caretPosition = 34
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlCC_ADJM_ADJMREC_GRID/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlCC_ADJM_ADJMREC_GRID/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/usr/cmbG_LISTBOX").SetFocus
session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "08"
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
'session.findById("wnd[1]/tbar[0]/btn[0]").press
'session.findById("wnd[0]/tbar[0]/btn[12]").press
session.findById("wnd[0]/tbar[0]/btn[12]").press


session.findById("wnd[0]/tbar[1]/btn[9]").press
session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[2]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = filepathsave
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "TR_PP SIM PLOG VNDR LI " & contractno & "_" & Year(Now) & monthtoday & daytoday & ".xls"
session.findById("wnd[1]/tbar[0]/btn[11]").press


SAP_Workbook = "Worksheet in Basis(1)"
EXCEL_Path = "\...........\ADJ SIMULATION"
myWorkbook = "TR_FREEADJ SIM VNDR LI " & Contract & "_" & Year(Now) & monthtoday & daytoday & ".xlsx"


On Error Resume Next
Do
Err.Clear
Set xclapp = GetObject(, "Excel.Application")
If Err.Number = 0 Then Exit Do
'msgbox "Wait for Excel session"
wscript.sleep 2000
Loop


Do
Err.Clear
Set xclwbk = xclapp.Workbooks.Item(SAP_Workbook)
If Err.Number = 0 Then Exit Do
'msgbox "Wait for SAP workbook"
wscript.sleep 2000
Loop


On Error GoTo 0


Set xclsheet = xclwbk.Worksheets(1)


xclapp.Visible = True
xclapp.DisplayAlerts = False


xclapp.ActiveWorkbook.SaveAs EXCEL_Path & myWorkbook
xclapp.ActiveWorkbook.Close


Set xclwbk = Nothing
Set xclsheet = Nothing
'xclapp.Quit
Set xclapp = Nothing




'session.findById("wnd[0]/tbar[0]/okcd").Text = "/n"
'session.findById("wnd[0]").sendVKey 0








End Sub




Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Aug 25, 2017 at 08:11 AM

    Hi Bomb Sheels,

    SAP has been developed also further. One does not need the former workaround anymore. Therefore, the solution could look as follows.

    for example:

    . . .
    
    EXCEL_Path = "\\asia-pac.shell.com\asia-pac\Corporate\SSSA RCBC Tower 2\Dept_05\EDR Retailer Billing\Data\REAL ESTATE\TURKEY\FREE ADJUSTMENT\REAL ESTATE_TR04_FREE ADJUSTMENT_201708\ADJ SIMULATION"
    myWorkbook = "TR_FREEADJ SIM VNDR LI " & Contract & "_" & Year(Now) & monthtoday & daytoday & ".xlsx"
    
    session.findById("wnd[0]/tbar[0]/okcd").Text = "/NREAJSH"
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/usr/ctxtP_PEXTID").Text = AdjID
    'session.findById("wnd[0]/usr/ctxtP_PEXTID").caretPosition = 34
    session.findById("wnd[0]/tbar[1]/btn[8]").press
    session.findById("wnd[0]/usr/cntlCC_ADJM_ADJMREC_GRID/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
    session.findById("wnd[0]/usr/cntlCC_ADJM_ADJMREC_GRID/shellcont/shell").selectContextMenuItem "&XXL"
    session.findById("wnd[1]/usr/cmbG_LISTBOX").setFocus
    session.findById("wnd[1]/usr/cmbG_LISTBOX").key = "10"
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[1]/usr/ctxtDY_PATH").Text = EXCEL_Path
    session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = myWorkbook
    session.findById("wnd[1]/tbar[0]/btn[11]").press
    
    On Error Resume Next
    Do
    Err.Clear
    Set xclapp = GetObject(, "Excel.Application")
    If Err.Number = 0 Then Exit Do
    session.findById("wnd[0]").iconify
    'msgbox "Wait for Excel session"
    wscript.sleep 2000
    session.findById("wnd[0]").maximize
    Loop
    
    Do
    Err.Clear
    Set xclwbk = xclapp.Workbooks.Item(myWorkbook)
    If Err.Number = 0 Then Exit Do
    'msgbox "Wait for SAP workbook"
    wscript.sleep 2000
    Loop
    
    On Error GoTo 0
    xclapp.Visible = True
    xclapp.DisplayAlerts = False
    xclapp.ActiveWorkbook.Close
    
    Set xclwbk = Nothing
    Set xclsheet = Nothing
    xclapp.Quit
    Set xclapp = Nothing
    
    

    Regards,

    ScriptMan

    Add comment
    10|10000 characters needed characters exceeded