Wait for the file to be downloaded and then continue

Question

I am facing the following problem. The current active workbook is the VBRK.XLSB. In this file I have written the VBA code. I have connected the SAP with EXCEL and I am trying to download a SAP table to spreadsheet to file EXPORT.XLSB. Then I would like to activate the extracted file. The problem is that macro activates the current file VBRK.XLSB because it takes some time to download and open the extracted file EXPORT.XLSB.

Please find below the code I have written.

Thank you very much for your help.

Is there a way for the macro to wait until the extracted file EXPORT.XLSB is downloaded, opened, and then continue executing?

Sub SAP_TABLE()
Application.DisplayAlerts = False

Session.findById("wnd[0]").maximize
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/NZSE16"
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/ctxtI_TABLE").Text = MainWS ' "VBRK"
Session.findById("wnd[0]/usr/ctxtI_TABLE").caretPosition = 4
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/tbar[1]/btn[8]").press
Session.findById("wnd[0]/mbar/menu[3]/menu[2]").Select
Session.findById("wnd[1]/tbar[0]/btn[14]").press
Session.findById("wnd[1]/usr/chk[2,5]").Selected = False
Session.findById("wnd[1]/usr/chk[2,15]").Selected = True
Session.findById("wnd[1]/usr/chk[2,10]").Selected = True
Session.findById("wnd[1]/usr/chk[2,10]").SetFocus
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Session.findById("wnd[0]/usr/ctxtI1-LOW").Text = "GR40"
Sheets(CfgWs).Select  ' gia na parei tis imerominies
On Error Resume Next
Session.findById("wnd[0]/usr/ctxtI2-LOW").Text = Range("c5") '"01.02.2023"
Session.findById("wnd[0]/usr/ctxtI2-HIGH").Text = Range("c6") '"28.02.2023"
Session.findById("wnd[0]/usr/ctxtI2-HIGH").SetFocus
Session.findById("wnd[0]/usr/ctxtI2-HIGH").caretPosition = 10
Session.findById("wnd[0]").sendVKey 0
'load table
Session.findById("wnd[0]/tbar[1]/btn[8]").press
'create spreadsheet
Session.findById("wnd[0]").maximize
Session.findById("wnd[0]").sendVKey 43
Session.findById("wnd[1]/usr/cmbG_LISTBOX").SetFocus
Session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "31"
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\USER1\_Tables MainFolder\Export\"
Session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
Session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 0
Session.findById("wnd[1]").sendVKey 4
Session.findById("wnd[2]/tbar[0]/btn[11]").press
Session.findById("wnd[1]/tbar[0]/btn[11]").press

Dim aa
For aa = 1 To 30 Step 1
Application.Wait (Now + TimeValue("00:00:01"))
Next aa

MsgBox ("activeworkbook = " & ActiveWorkbook.Name)

End Sub

Answer 1

(I assume your process itself works)

Check the ActiveWorkbook within the loop - increase the counter if 30s sometimes is not enough. If ActiveWorkbook is the expected file, you can leave the loop. Also, you should leave the loop after a certain time to prevent an endless loop when download failed.

Const TimeOut = 120   ' 2 minutes.
Const Exportfile = "EXPORT.XLSB"

(...)

Dim timeCounter as Long
For timeCounter = 1 To TimeOut Step 1
    DoEvents
    If ActiveWorkbook.Name = Exportfile Then
        MsgBox Exportfile & " downloaded in " & timeCounter "s."
        Exit Sub
    End If
    Application.Wait Now + TimeValue("00:00:01")
Next timeCounter 

If ActiveWorkbook.Name <> Exportfile Then
    MsgBox "A timeout occurred downloading " & Exportfile
End If