VBA - Call Macro Function Doesn't Work After Data Extracted From SAP

[Koala123]

Hi Guys,

I have a SAP-related macro issue couldn't fix by myself. I am dumping data out from SAP in the form of excel spreadsheet, so at the end of this macro it will pop-up a excel file, but after the extracting data macro finishes, it meant to call and run another macro, as you can see I put "Call move_data" at the end of this macro, but it doesn't work, everything just ends here.

Can anyone please advise me why everything I write after "session.findById("wnd[1]/tbar[0]/btn[11]").press" doesn't work at all? Is that because the pop-up excel file stops everything from running?

Any thoughts will be badly appreciated!

'export

session.findById("wnd[0]").maximize
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "H:\General"
session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 93
session.findById("wnd[1]/tbar[0]/btn[11]").press                                  --------------> pop-up a excel file

'it stops here --- why?

Call move_data

End Sub

 

[Rpaikh]

Hello Koala123,



First of all it's nothing related to "session.findById("wnd[1]/tbar[0]/btn[11]").press" since this is code to perform "SAVE" function in SAP. What cause your codes to stop working is the pop-up excel file that you download from SAP.

Currently it's not possible to set it up directly in SAP to prevent pop-up file opening automatically after downloading; but I get it fix with below approach.


1. Create a new module and input below code
2. Call this module after "session.findById("wnd[1]/tbar[0]/btn[11]").press"

For example,

objSess.findById("wnd[1]/tbar[0]/btn[11]").press
Application.Run "'" & ThisWorkbook.Name & "'!closeOtherExcelInstances"

Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Sub closeOtherExcelInstances()
    Dim oServ As Object
    Dim cProc As Variant
    Dim oProc As Object
    Dim currentProcId
    
    currentProcId = GetCurrentProcessId
    
    Set oServ = GetObject("winmgmts:")
    Set cProc = oServ.ExecQuery("Select * from Win32_Process where NAME = 'EXCEL.EXE'")
    
    For Each oProc In cProc
        If oProc.ProcessId <> currentProcId Then
            Debug.Print oProc.Name, oProc.ProcessId
            errReturnCode = oProc.Terminate()
        End If
    Next
End Sub

 

[Koala123]

Hello Koala123,



First of all it's nothing related to "session.findById("wnd[1]/tbar[0]/btn[11]").press" since this is code to perform "SAVE" function in SAP. What cause your codes to stop working is the pop-up excel file that you download from SAP.

Currently it's not possible to set it up directly in SAP to prevent pop-up file opening automatically after downloading; but I get it fix with below approach.


1. Create a new module and input below code
2. Call this module after "session.findById("wnd[1]/tbar[0]/btn[11]").press"

For example,

objSess.findById("wnd[1]/tbar[0]/btn[11]").press
Application.Run "'" & ThisWorkbook.Name & "'!closeOtherExcelInstances"

Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Sub closeOtherExcelInstances()
    Dim oServ As Object
    Dim cProc As Variant
    Dim oProc As Object
    Dim currentProcId
   
    currentProcId = GetCurrentProcessId
   
    Set oServ = GetObject("winmgmts:")
    Set cProc = oServ.ExecQuery("Select * from Win32_Process where NAME = 'EXCEL.EXE'")
   
    For Each oProc In cProc
        If oProc.ProcessId <> currentProcId Then
            Debug.Print oProc.Name, oProc.ProcessId
            errReturnCode = oProc.Terminate()
        End If
    Next
End Sub

Hi Rpaikh, thank you very much! Your codes is very helpful, I have tried this and it works well!

I was just wondering is it possible to leave the excel file open and just close the pop-up file? The reason is my master file is meant to run another macro which is that "Call move_data" part. I can see this codes seems forcefully shuts down the whole excel application, which means my whole process is also turned off in the middle.

It's kind of strange as if I run half of my codes actually the pop-up file doesn't give me any problem, but if I run the whole macro ( which changes some fields in general ledger account in SAP then export) then it gives me the above problem. It;s really gets under my skin....haha

Will appreciate any help!

Regards
Alex

 

[Koala123]

Sub test_export()

   Set SapGuiAuto = GetObject("SAPGUI")
   Set SAPApp = SapGuiAuto.GetScriptingEngine
   Set SAPCon = SAPApp.Children(0)
   Set session = SAPCon.Children(0)

If IsObject(WScript) Then
   WScript.ConnectObject SAP_session, "on"
   WScript.ConnectObject SAPGuiApp, "on"
End If

session.findById("wnd[0]").maximize
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "H:\02_Unidentified Receipts"
session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 93
session.findById("wnd[1]/tbar[0]/btn[11]").press

Application.Wait Now() + TimeValue("0:0:05")

Workbooks("SAP test.xlsb").Activate

Dim ws2 As Worksheet
Set ws2 = Worksheets("Rawdata 22136")

ws2.Select
Range("A:G").Clear

Workbooks("export.xlsx").Activate

Worksheets("sheet1").Select
Range("A:G").Copy

Workbooks("SAP test.xlsb").Activate
ws2.Range("A1").Select
ActiveSheet.Paste

Application.DisplayAlerts = False
Workbooks("export.xlsx").Close
Application.DisplayAlerts = False


Call Copydata

End sub

Believe or not if I just run part of the whole macro (the above one) , actually it works just fine, dont really understand why....