Skip to Content
0

Saving extracted SAP report in Excel format into specified drive

Aug 24, 2017 at 04:41 PM

160

avatar image
Former Member

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




10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Script Man Aug 25, 2017 at 08:11 AM
0

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

Show 8 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank you Script.Man, it saves in the directory. However, the report generated is different from the expected report. :(

0

Can you indicate what is the difference?

0
Former Member

Hello ScriptMan,below is the expected output.

However, this report below was generated.

needed-output.jpg (32.4 kB)
extracted.jpg (30.5 kB)
0

Please create a script with the script recorder for the needed-output and publish it here in the Forum.

0
Former Member

Hello Script.Man, i got the correct report. :) However, i was wondering after I got the correct report, the saving path didn't work. I always got an error in "EXCEL_PATH"

0
Former Member

SAP_Workbook = "Worksheet in Basis(1)"
EXCEL_Path = Range("J29").value & "/"
myWorkbook = CoCode & "_FREEADJ SIM_" & Contract & "_" & Year(Now) & monthtoday & daytoday & ".xls"


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[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


session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press


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








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




End Sub



0

A script does nothing different than one tells him it. Record the relevant commands manually and compare it to the script. But should not it be correct as follows?

EXCEL_Path = Range("J29").value & "\" instead of EXCEL_Path = Range("J29").value & "/"

0
Former Member

Thank you so much ScriptMan. :) the code is working perfectly until the excel freeze and prompted "not responding".

0