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

MJOAP
MJOAP used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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

Author

Commented:
There is no actual Excel file created...

it exists only in RAM until it looses focus.... then it is gone....
Commented:
MJOAP,

The example code below assumes that you have the active workbook open, in which
you will create a worksheet named "ALVXXL01_1".

The "wait_for_sheet() " function will continuously loop until the worksheet named "ALVXXL01_1"
is created.

After worksheet "ALVXXL01_1" is created, then the function will return control to your
subroutine and cause the worksheet "ALVXXL01_1" to be activated.

I hope this is what you wanted!

Regards



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 
 
'At this point we call the "wait_for_sheet()" function
x = wait_for_sheet() 
 
' Activate the Temp Excel Sheet 
    Windows("Worksheet in ALVXXL01 (1)").Activate
 
Function wait_for_sheet() As Boolean
'This function will continue to loop until worksheet "ALVXXL01_1"
'is created in active workbook
  Dim sheet_exist As Boolean
  sheet_exist = False
  While Not sheet_exist
   For Each xSheet In Sheets
    If UCase(xSheet.Name) = UCase("ALVXXL01_1") Then sheet_exist = True
   Next
  DoEvents 'Do not freeze the computer
  Loop
  wait_for_sheet = sheet_exist
End Function

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial