on 04-13-2016 12:18 PM
Dear Community
I'm doing a project in which I need to download excel report from SAP 7.3 and from the downloaded workbook I need to prepare summary report using different calculations. There are more than 200+ items which I need to download and prepare summary reports, guess need to loop through this task.
When I manually saved one excel report it get automatically open and became my active screen, hence I thought its better to do work on my summary task as soon as I saved the report but its not working. I use normal activate workbook option, but it did not locate the workbook. When I manually stop the code then the workbook get open. any Idea why is this happening..... Has anyone worked on same situation before?
Below is my code:
session.findById("wnd[0]").sendVKey 8 'on this line the SaveAs window pop-up
FilePath = "c:\tmp\Sap download file\ExcelReport1.xlsx") 'I run another vb script to locate the saveAs window and enter path and save it
set Wshell = CreateObject("WScript.Shell")
wshell.run """c:\tmp\save_as.vbs """ & FilePath
Application.wait Now + TimeValue("0:00:05")
workbooks("ExcelReport1.xlsx").activate
Hi Sagar,
try it as follows:
. . .
wshell.run """c:\tmp\save_as.vbs """ & FilePath, 1, false
. . .
Regards,
ScriptMan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sorry, it was my fault. But so it should work:
. . .
FilePath = "c:\tmp\Sap download file\ExcelReport1.xlsx") 'I run another vb script to locate the saveAs window and enter path and save it
set Wshell = CreateObject("WScript.Shell")
wshell.run """c:\tmp\save_as.vbs """ & FilePath, 1, false
session.findById("wnd[0]").sendVKey 8 'on this line the SaveAs window pop-up
Application.wait Now + TimeValue("0:00:05")
workbooks("ExcelReport1.xlsx").activate
. . .
But if it does not work, you would have to look at save_as.vbs.
I tested the following by me and it works without problems:
Sub myMacro()
FilePath = "c:\tmp\Sap download file\ExcelReport1.xlsx")
Set SapGuiAuto = GetObject("SAPGUI")
Set SAP_Application = SapGuiAuto.GetScriptingEngine
Set Connection = SAP_Application.Children(0)
Set session = Connection.Children(0)
session.findById("wnd[0]").maximize
. . .
session.findById("wnd[0]/usr/subSUB_AREA_ROOT:SAPLREIS_GUI_CONTROLLER:0200/subSUB_AREA:SAPLREIS_GUI_CONTROLLER:1000/cntlCC_LIST/shellcont/shell").currentCellColumn = "XRECNTYPE"
session.findById("wnd[0]/usr/subSUB_AREA_ROOT:SAPLREIS_GUI_CONTROLLER:0200/subSUB_AREA:SAPLREIS_GUI_CONTROLLER:1000/cntlCC_LIST/shellcont/shell").contextMenu
session.findById("wnd[0]/usr/subSUB_AREA_ROOT:SAPLREIS_GUI_CONTROLLER:0200/subSUB_AREA:SAPLREIS_GUI_CONTROLLER:1000/cntlCC_LIST/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "10"
Set wshell = CreateObject("Wscript.Shell")
wshell.Run "c:\tmp\save_as.vbs" & " " & FilePath, 1, False
session.findById("wnd[1]/tbar[0]/btn[0]").press
Workbooks.Open Filename:= FilePath
'
End Sub
Save_as.vbs:
if wscript.arguments.count > 0 then
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.fileExists(wscript.arguments(0)) Then
Set myfile = fso.GetFile(wscript.arguments(0))
myfile.Delete
End If
Set wshell = CreateObject("WScript.Shell")
Number = 0
Do
bWindowFound = wshell.AppActivate("Save As")
wscript.sleep 1000
Number = Number + 1
If bWindowFound Or Number > 10 Then Exit Do
Loop
If bWindowFound Then
wshell.AppActivate "Save As"
wscript.sleep 500
wshell.SendKeys "%n"
wscript.sleep 500
wshell.SendKeys wscript.arguments(0)
wscript.sleep 500
wshell.SendKeys "%s"
wscript.sleep 500
End If
End if
Regards,
ScriptMan
Hi ScriptMan,
Its not helping... When my code end the downloaded file get automatically open, but while code is running that file never get open, like its invisible, it got stuck in loop, when I press ctrl+pauseBreak it get suddenly open. It get open in macro workbook only, is that a problem?
I have provided my entire code and macro file image.
User is going to update all ASL numbers from range ("B8"), using those numbers macro generate and save its excel and PDF reports. After saving excel I'm trying to open that excel so I can update details in range ("E8:Y8"). but I'm stuck because that file is not visible.
Below I run a small test (Mark in bold), but no success. It just print my Macro file name and after pause break the value of 'Workbooks(workbooks.count).name change to what I'm looking for
Do
application.Wait Now + TimeValue("0:00:05")
Debug.Print Workbooks(Workbooks.Count).Name
Loop Until Workbooks(Workbooks.Count).Name = wks.Range("B" & i).Value & ".xlsx"
What should I do?
===============================================================
Function SAP_Action(cnt, ReportDate, GenDate, EFilePath, PFilePath)
'START SAP Scripting++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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
'///////////////////////////////////////
'Close SAP generated workbook in loop //
'///////////////////////////////////////
On Error Resume Next
Workbooks(wks.Range("B" & i - 1).Value & ".xlsx").Close False
On Error GoTo 0
application.StatusBar = "Working on " & wks.Range("B" & i).Value & " ...."
session.findById("wnd[0]").maximize
'SAP T code
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
'Clear system default text and enter user required details
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
'Generate report
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 report in excel workbook //
'/////////////////////////////////////////////////////
'below line select EXPORT option in SAP
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
'Storing file path to save excel workbook in the variable
ExcelPath = EFilePath & wks.Range("B" & i).Value & ".xlsx"
Set Wshell = CreateObject("Wscript.Shell")
'In below line ".vbs" file path need to change as per requirement
'.vbs script to capture the SaveAs window and enter the file path and save it
Wshell.Run """C:\Users\PandeyA\Desktop\SagarTest\Test3.vbs """ & ExcelPath, 1, False
'Select EXPORT to excel spreadsheet option, which pop-up 'SaveAs window'
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").selectContextMenuItem "&XXL"
Set Wshell = Nothing
Do
application.Wait Now + TimeValue("0:00:05")
Debug.Print Workbooks(Workbooks.Count).Name
Loop Until Workbooks(Workbooks.Count).Name = wks.Range("B" & i).Value & ".xlsx"
'////////////////////////////////
'code to generate PDF document //
'////////////////////////////////
session.findById("wnd[0]/tbar[1]/btn[8]").press ' <= This generate PrintPreview of PDF
session.findById("wnd[0]/mbar/menu[0]/menu[0]").Select ' <= This give command to print
PDFPath = PFilePath & wks.Range("B" & i).Value & ".pdf"
Set WSHShell = CreateObject("WScript.Shell")
WSHShell.AppActivate "Print" 'Title of the window
application.Wait Now + TimeValue("0:00:01")
Do
On Error Resume Next
AppActivate "Print"
Loop Until Err.Number = 0
AppActivate "Print"
WSHShell.SendKeys "Cu" 'Selecte CutePDF Writer
WSHShell.SendKeys "{ENTER}"
Set WSHShell = Nothing
Set WSHShell = CreateObject("WScript.Shell")
Do
On Error Resume Next
AppActivate "Save As"
Loop Until Err.Number = 0
AppActivate "Save As"
WSHShell.SendKeys "%n"
WSHShell.SendKeys PDFPath
application.Wait Now + TimeValue("0:00:01")
WSHShell.SendKeys "%s"
Set WSHShell = Nothing
wks.Range("D" & i).Value = "Done"
End If
'/////////////////////////////////////////////////////////////////////////
'below lines to go back to 1st screen for loop to enter next ASL number //
'/////////////////////////////////////////////////////////////////////////
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[1]/usr/btnBUTTON_1").press
'session.findById("wnd[0]/tbar[0]/btn[3]").press
Next i
Set SapGuiAuto = Nothing
Set App = Nothing
Set connection = Nothing
Set session = Nothing
End Function
Scriptman
May I have the complete method "save_as_vbs" again. Because I am not strong in VBA and just have some basic knowledge.
I have the same question (Problem) as Apr 15, 2016 at 06:46 AM mentioned.
I use T-code "FBL3N" to review GL account report. But somehow impossbile to record the script after "Save As" windows pop up
mega thanks in advance.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Scriptman
May I have the complete method "save_as_vbs" again. Because I am not strong in VBA and just have some basic knowledge.
I have the same question (Problem) as Apr 15, 2016 at 06:46 AM mentioned.
I use T-code "FBL3N" to review GL account report. But somehow impossbile to record the script after "Save As" windows pop up
mega thanks in advance.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You can´t handle SAP SaveAs dialogue from calling Excel Instance.
VBA code line Need to be executed until next line will be started. As Long as SaveAs dialogue is open VBA code is waiting for Event send back from SAP to Excel.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Holger,
I'm able to control saveAs window and successfully saved the file, but at the same time I wanted to activate that saved file so I can prepare summary on it.
After saving this file on desktop, macro start executing rest of the code, in that I wanted to open that file and do some task on it, however it's not opening until END Sub.
I'm confused why its happening ?
You have started an Event to control in one VBA Excel instance.
This started Action got handled by another code. Caused by this binding the previous code got no Event back that code line is executed.
If you have a ALV list which you want to download I would suggest to read this complete list into your Excel instance with Initial code.
Then there is no Need to use SaveAs. And you can do your calculations in same Excel workbook which contain the SAP ALV list extraction code.
Add an button on sheet, define some fields on sheet as selection criteria for SAP selection Screen. Then you have an comfortable Excel Workbook for extraction of SAP data and perform some calculations after extraction.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.