asked on

How do I get Excel VBA to wait for SAP to create/download a temporary Excel file before continuing on with VBA macro?

Hi Experts,

I have automated a report in SAP using SAP script which I am downloading to Excel from SAP. SAP creates a tempoary Excel file (ie if you move away from the file it closes) called ALVXXL01.xls in memory every time I run the report.

The problem is that if the SAP report is very long (when I request a lot of data)
my Excel macro tries to continue on before SAP has a chance to create ALVXXL01 in memory and I get a run time error.

So I need to put something into my code below before the activate line to get excel to wait
until ALVXXL01 has been created in memory by SAP.

Just to be clear when I run the report with very little data I have no problem....it's only when there is lots of data being returned from SAP to Excel.

Many Thanks Experts.

HERE IS MY CODE
' This Section should drop to Excel 
    SapSession.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select 
    SapSession.findById("wnd[1]/tbar[0]/btn[0]").press 
    SapSession.findById("wnd[1]/usr/sub:SAPLSPO5:0101/radSPOPLI-SELFLAG[0,0]").Select 
    SapSession.findById("wnd[1]/usr/sub:SAPLSPO5:0101/radSPOPLI-SELFLAG[0,0]").SetFocus 
    SapSession.findById("wnd[1]/tbar[0]/btn[0]").press 
    SapSession.findById("wnd[1]/tbar[0]/btn[0]").press 
    
    
' Activate the Temp Excel Sheet 
    Windows("Worksheet in ALVXXL01 (1)").Activate

Open in new window

xellz

Are there any markers for you to know if the file is finished or not? If when finished, it is renamed or appears in a temp directory... you could do something like:

Do Until counter = 1
If Dir("c:\temp\ALVXXL01.xls") <> "" Then
Windows("Worksheet in ALVXXL01 (1)").Activate
counter = 1
Else
Application.Wait Now + TimeValue("00:00:30")
End If
Loop

MJOAP

ASKER

There is no actual Excel file created...

it exists only in RAM until it looses focus.... then it is gone....

ASKER CERTIFIED SOLUTION

experts1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial