-2

I write a lot of Excel macros that send script messages to SAP's GUI to execute queries and download the results to an Excel file. My problem is that SAP tries to open the file when the export is complete. I want to suppress this action because at that point, my macro has moved on to another part of the process and the opening of the file will interrupt it. I fixed the problem by renaming the excel export as soon as it's created. That just kicks the problem down the road in the process. When my macro processes complete, Excel gets around to an attempt to open the files, which Excel cannot find because of the renaming. I typically get this message:

Text

Is there a way through SAP scripting or a environmental setting in SAP to tell SAP not to try to open the export?
Thanks
Joe

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Joe Messingschlager
  • 69
  • 3
  • 11
  • 1
    The solution is either at SAP side to not attempt the opening, or in your macro to close the Excel file after it was opened by SAP. Could you either explain what you do at SAP side to export, or what code you have in your macro, depending on the question you want to ask? – Sandra Rossi Apr 02 '20 at 15:36
  • That's my dilemma. On the excel side, you don't know when the file will open. Sometimes it waits until the macro process completes, sometimes not. In this current macro under development, it waits until the end and then Excel send three of these errors to the screen for the three files that SAP exported to xlsx. The elegant solution would be to set the SAP GUI app not to try to open the file. If you know that and can share it, you're golden! – Joe Messingschlager Apr 02 '20 at 16:16
  • 2
    If you prefer a solution at SAP side, the problem is that you don't describe enough what you do to export ("execute queries and download the results to an Excel file"). So, please, again, explain what the macro does at SAP side (run transaction code ? click button ? press key ? etc.) – Sandra Rossi Apr 02 '20 at 19:55
  • I found a solution. In Excel 365, I open an instance for the spreadsheet with the macro and then I open a second instance of Excel with a blank spreadsheet. When SAP tells Excel to open the exported spreadsheet, it tells the new instance of Excel and not the instance running the macro. Not an elegant solution, but it works. – Joe Messingschlager Aug 06 '21 at 10:30

4 Answers4

1

For me the best solution was: from SAP save as Local (txt). TXT has not opened automatically. Instance opened but not presents in VBE list. When i tried to set the opened workbook with

Set src = Workbooks("EXPORT.XLSX")

Sets fine in debug mode but automatically not, even with application wait excel failed to set. So excel see the new instance after everything stopped.

/via SAP script, in VBA/

session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlCUSTOM/shellcont/shell/shellcont/shell").selectContextMenuItem "&PC"
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]/usr/ctxtDY_PATH").text = "C:\Data\"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = "data.txt"
session.findById("wnd[1]").sendVKey 11

In Excel just simply

Sub text_to_sheet()
Dim wb As Workbook, txt As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("tmp_sheet") 'a temporary sheet
Set txt = Workbooks.Open("C:\Data\data.txt")
txt.Sheets(1).Cells.Copy ws.Cells
txt.Close SaveChanges:=False
End Sub

Then i just simply save the desired worksheet, in my case it is *.csv

Sub exp_csv()
trgt = Environ("USERPROFILE") & "\Desktop\"
fn = "desired_filename"
Application.DisplayAlerts = False
ThisWorkbook.Sheets(2).Copy
ActiveWorkbook.SaveAs Filename:=trgt & fn,FileFormat:=xlCSV 'FileFormat:=xlWorkbookDefault , for regular xlsx
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub
Attila Ragó
  • 33
  • 7
  • In my case, I want to save the file in XLSX format, so that limits me to exports, where I run into this problem. My method of renaming the output file immediately after the file is exported works well. If I have a second and newer instance of Excel open, then this other instance tries to open the file, so the Not Found error is captured in this separate instance, allowing my macro to run uninterrupted. – Joe Messingschlager Oct 25 '22 at 17:08
0

I found an answer on an SAP forum. It looks you could just call this sub procedure to close all other instances of Excel.

' Choose one
Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long '32 bit Windows
Private Declare PtrSafe Function GetCurrentProcessId Lib "kernel32" () As LongPtr '64 bit Windows

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

Here's where I found this https://answers.sap.com/questions/737136/sap-gui-74-patch-12---disallow-excel-auto-open.html.

Good luck and happy automating.

Scott Ridings
  • 744
  • 1
  • 8
  • 14
0

Copy this after the code for exporting the file from SAP. Worked for me, I hope it helps :)

Dim oShell : Set oShell = CreateObject("WScript.Shell")             'check all process runing

oShell.Run "taskkill /im excel.exe", , True                         'kill firts SAP excel opening attempt
oShell.Run "taskkill /f /im excel.exe", , True                      'kill second system excel opening attempt
Alestac
  • 1
0

Thanks for your suggestion and it works for me. BTW, if you have multiple excel to export in one Script then you need to rename variable "oShell" like "oShell_1 ~oShell_n" without using loop .

Wyatt
  • 1