on 08-24-2017 5:41 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.