Help required to activate saved workbook from SAP while VBA script is running

[Former Member]

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 

[script_man · Accepted solution]

Hi Sagar,

try it as follows:

. . .

wshell.run """c:\tmp\save_as.vbs """ & FilePath, 1, false

. . .

Regards,

ScriptMan

[Former Member · Accepted solution]

Hi ScriptMan,

I also used this line and it saving the file successfully but I wanted after saveAs , the macro should open activate that file so I can do work on it.

Regards,
Sagar

[script_man · Accepted solution]

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

[Former Member · Accepted solution]

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

[holger_khn · Accepted solution]

When file got saved you can open a copy via script and work on this.

But when you have a ALV list Output as result I would suggest to read this directly into your calling Excel workbook and perform required calculations afterwards.

[Former Member · Accepted solution]

Hi Holger,

I'm new to VBA / VBScript and its my first project on SAP, hence I'm actually confused how to preform this task you have suggested.

any help is much appreciated..

Regards,
Sagar

[script_man · Accepted solution]

Hi Sagar,

You're right. But this is sometimes like that. A VB script responds simply different than a manual input. If the Excel workbook was created and the "Save As" window is closed, you can open file with the following command itself:

Workbooks.Open Filename:= ExcelPath

Regards,

ScriptMan

[Former Member · Accepted solution]

Hi Scriptman,

Good day to you. I am to fresh SAP script and facing samiliar problem as export Excel from SAP.

I use T-code "FBL3N" to review GL account report. But somehow impossbile to record the script after "Save As" windows pop up

Enclosed the script for your refereence.

FBL3n.txt.zip

[script_man · Accepted solution]

Hi Henry,

I can recommend this link:

Regards,

ScriptMan

[Former Member · Accepted solution]

Hi Script Man, thanks for your kind help.

[former_member805846]

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.

[former_member805846]

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.

[holger_khn]

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.

[Former Member]

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 ?

[holger_khn]

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.