SAP GUI export automatically opens excel

Question

I am trying to write a macro that retrieves data from an ALV grid in SAP GUI. Everything is working fine up until the data exports. When exporting data from ALV grid to an .xlsx file, the file will automatically open after it exports.

I need my script to wait for the export to open, and then copy the data from the newly opened export file to the .xlsm file that the script is coming from.

If I try to activate the export.XLSX file immediately following the command to export the file in SAP GUI, I get a "subscript out of range" error. I thought maybe I could loop the activate command until it stops erroring (while the export.xlsx file is opening) but that causes excel to crash. What should I do?

Function funcLSAT(strEnv)

Dim wkbExport As Workbook
Dim strError As String

If Not IsObject(SapGuiApp) Then
    Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.1")
End If
If Not IsObject(Connection) Then
    Set Connection = SapGuiApp.OpenConnection(strEnv, True)
End If
    
Set session = Connection.Children(0)
session.findById("wnd[0]/tbar[0]/okcd").Text = "[TCODE]"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlG_CC_MCOUNTY/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlG_CC_MCOUNTY/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "[filepath]"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "export.xlsx"
session.findById("wnd[1]/tbar[0]/btn[11]").press
Set session = Nothing
Set Connection = Nothing
Set SapGuiApp = Nothing

Do
On Error Resume Next
Windows("export.XLSX").Activate
Loop Until (Err.Number = 0)
On Error GoTo 0

Range("A2:AS2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

End Function

I believe there is a Do Until progress bar complete you're supposed to add, where you wait 1 second, then it rechecks the progress bar. Here is the closest thing I can currently find: https://www.ozgrid.com/forum/forum/help-forums/excel-general/123713-wait-until-file-loads — Cyril, Aug 08 '18 at 20:39

score 0 · Answer 1 · answered Aug 08 '18 at 21:43

0

I had the same issue time ago when my macro had to wait for a .txt to be created and then continue so I found this:

Dim Directory As String, File As String
Directory = ActiveWorkbook.Path & "\" 'path for the file
File = Directory & "datos.txt" 'name of the file along with the path


  FindIt = Dir(File)
  While Len(FindIt) = 0
  FindIt = Dir(File)
  Wend

Hope it helps.

answered Aug 08 '18 at 21:43

Damian

5,152
1
10
21

For me this is continuously eating CPU usage. – Fennekin Oct 15 '21 at 08:45
Is there any set interval in VBA just like java? – Fennekin Oct 15 '21 at 08:46
@Fennekin you can use `Application.Wait` to make it wait, thought the goal of this code is to process the file as soon as it loads. – Damian Oct 16 '21 at 09:15

score 0 · Answer 2 · edited Nov 05 '21 at 05:49

Here is what I do to export as XSLX, copy over the sheet, and close.

Application.wait does not do what you would want it to do here. Application.wait does not release Excel and so the SAP file never opens. Setting a timer will release Excel so the file will load.

It may not be the most well written, but it works. First, do a function to be able to see if the files are open or not. Then, set a timer of 0.5 seconds or so to put on a loop. You could do a longer timer and probably wouldn't have to loop, but done with a short time keeps Excel from being released any longer than it has to. Once the file locked on to, it will copy over the contents and close the file, and will exit the loop. Then I also kill the file because, on next run, it needs to be gone if the first run the isfile open is going to pick up the old file. This is written to where it's supposed to grab the workbook if it's opened in another instance—though, I haven't tested that. The way my VBA workbook loads, SAP files end up loading in the same instance. Good Luck!

Function IsFileOpen(FileName As String)
    Dim iFilenum As Long
    Dim iErr As Long
     
    On Error Resume Next
    iFilenum = FreeFile()
    Open FileName For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
    On Error GoTo 0
     
    Select Case iErr
    Case 0:    IsFileOpen = False
    Case 70:   IsFileOpen = True
    Case Else: Error iErr
    End Select
     
End Function

Dim xlfile as string, xldir as string
Dim PauseTime, Start
Dim control As Long
Dim xlapp As Object
Dim wb as workbook
xldir= "Your file folder path here"   
control = 0
xlfile = "Your filename here"
Do Until control = 5
PauseTime = 0.5 ' Set duration.
Start = Timer    ' Set start time.
Do While Timer < Start + PauseTime
    DoEvents    ' Yield to other processes.
 Loop
  If control = 1 And IsFileOpen(xldir & xlfile) = False Then
  Exit Do
  End If
 If IsFileOpen(xldir & xlfile) = True Then
  Set xlapp = GetObject(xldir & xlfile).Application
  If control = 0 Then
  Workbooks(xlfile).Sheets(1).Name = Left(xlfile, Len(xlfile) - 5)
  Workbooks(xlfile).Sheets(Left(xlfile, Len(xlfile) - 5)).Copy    
  before:=ThisWorkbook.Sheets(1)
  ThisWorkbook.Activate
  control = 1
   End If
  For Each wb In Application.Workbooks
 If wb.Name = xlfile Then
  wb.Close
    End If
 Next wb
 End If
  Loop
 control = 0
 kill(xldir&xlfile)

SAP GUI export automatically opens excel

2 Answers2