Excel file downloaded from SAP is not getting recognized in my VBA code

Question

I have a macro code which downloads an excel file from SAP, post which it should do some data manipulation on it to arrive at my final output file. The code downloads and has the excel file opened. But still it shows an error every time that object is out of range. Once I click somewhere in the excel sheet and continue to run my code, it works perfectly after that. How to avoid this manual intervention. Kindly advise.

If Not IsObject(App) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set App = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = App.Children(0)
End If
If Not IsObject(session) Then
   Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject App, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "stark"
session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "*****"
session.findById("wnd[0]/usr/pwdRSYST-BCODE").SetFocus
session.findById("wnd[0]/usr/pwdRSYST-BCODE").caretPosition = 12
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[0]/okcd").Text = "ABC01"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/radMADE").SetFocus
session.findById("wnd[0]/usr/radMADE").Select
session.findById("wnd[0]/mbar/menu[0]/menu[0]").Select
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\Stark\Desktop\SAP Scripting"
session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 44
session.findById("wnd[1]/tbar[0]/btn[0]").press

Windows("EXPORT.xlsx").Activate
Range("AA1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("AA1").Select
    Selection.Copy
    Range("B2:D2").Select
    Range(Selection, Selection.End(xlDown)).Select

The file always gets downloaded as EXPORT.xlsx. I get an error at Windows("EXPORT.xlsx").Activate Once I enter the sheet manually and continue running the code, it then works.

Kindly advise.

Are you sure that the exported `xlsx` file is open in the same Excel session? Please, use Task Manager and see how many Excel session you can see. And avoid using `Windows` and activating. Try setting a variable `Set wbE = Workbooks("EXPORT.xlsx")`. Or iterate between all open workbooks and search for the one having the name "EXPORT.xlsx". Can such a code find it? — FaneDuru, Dec 22 '20 at 14:44
Or maybe your code needs to wait for the workbook to be downloaded and open, if this operation takes some time... — FaneDuru, Dec 22 '20 at 14:53
If you find it in the existing session, you can try a loop between all existing workbooks searching for the one in discussion, with a delay of a second in each iteration (using `Application.Whait`). `For each WB in Workbooks`. `If Wb.Name = "EXPORT.xlsx" Then`. When the workbook is found, you set it as I recommended above and `Exit Do`. If it is in the same session I can help with a piece of code, if my above explanation is not clear enough. But now I will leave the office and I can do it in some hours, when I will be at home... — FaneDuru, Dec 22 '20 at 15:03
It is in the same session. It will be helpful if you could kindly share a code. — stark1208, Dec 22 '20 at 15:42

score 0 · Answer 1 · answered Dec 22 '20 at 14:08

I would try

applicatin.workbooks("Export.xlsx").activate

If there is still an error, run this code instead of the activate line

Dim i As Long
For i = 1 To Application.Workbooks.Count
    Debug.Print Application.Workbooks(i).Name
Next

This way, you get the names of the workbook you need to use in your activate code line.

FaneDuru · Answer 2 · 2020-12-23T07:45:20.887

Please, try the next way to wait for the exported file to be loaded in Excel. When VBA open a workbook, it waits for it to be open, but if is not directly open the code try finding the exported workbook before it has been loaded/open:

Sub testCheckOpenSAPExportedWb()
 'your existing code
 '...
 Session.findById("wnd[1]/tbar[0]/btn[0]").press
  
  Dim wb As Workbook, wbSAP As Workbook, lastRow As Long, count As Long
  
  'loop until the exported workbook is found. If more then 10 iteration sets passed, the loop is exited
  Do While wbSAP Is Nothing
    For Each wb In Workbooks
        count = count + 1
        If wb.Name = "EXPORT.xlsx" Then
            Set wbSAP = wb: Exit Do
        End If
        Application.Wait Now + TimeValue("0:00:01")
        If coumn >= 10 Then MsgBox "The workbook coould not be found in 10 seconds...": Exit Do
    Next
  Loop
  If wbSAP Is Nothing Then Exit Sub
  
    MsgBox "The exported workbook has been found..."
    wbSAP.Range("AA1").FormulaR1C1 = "1" 'I do not understand what you want doing here...
    lastRow = wbSAP.Range("B" & rows.count).End(xlUp).row
    wbSAP.Range("B2:D" & lastRow).Copy 'then you can paste it wherever you need...
    'do here whatever you need with the copied range...
    '...
End Sub

Please, test it and send some feedback. I would also like to know how much time takes opening of such a workbook if you manually try exporting it. Then, is it possible to only download the file somewhere in your computer, and let VBA to open it?

The workbook could not be found in 10 seconds. I tried to just download the file but it gets opened automatically as soon as it gets downloaded. Is there any other way to work this out — stark1208, Dec 23 '20 at 03:59
@stark1208: If you do not answer my question, it would be difficult to understand what is happening. I do not have SAP installed in order to check by myself... "**I would also like to know how much time takes opening of such a workbook if you manually try exporting it**". I meant, from the moment you press the export button untill you can see it open. It maybe takes 12 seconds and we will adapt the above code. Or only two and we must search for another way... — FaneDuru, Dec 23 '20 at 07:42
It takes less that 10 seconds for manually exporting it. I found a way out for this. Saved the file as txt format from SAP and then wrote a macro to import data from the text file. It works without any manual intervention now. Thanks for your help — stark1208, Dec 23 '20 at 10:02

score 0 · Answer 3 · answered Nov 12 '21 at 12:05

I have the same issue and tried a loop using the Sleep API function. It always times out because opening the workbook is held until code execution is completed. If you run it line by line in debug mode it works because you can allow time for the workbook to open before executing the next line of code. However, if you leave the macro to run, Excel waits until all the code in the macro has finished executing, then opens the file.

Excel file downloaded from SAP is not getting recognized in my VBA code

3 Answers3