Hello,

My VBA macro runs a VBscript to pull a report from SAP. I need to pause the vba script to account for the time it takes the VBscript to fully run. I was using a function that waits for the file to be saved to my desktop, but this SAP report can't be saved and exported. It can only be exported to excel. So I was thinking of another way of checking to see if the VBscript is done running. That is looping an activate wb function. If the wb activates, then continue the vba script. If the wb doesn't activate, then pause for a couple of seconds and try to activate the wb again.

The code below waits for the file to exist on my desktop. I'm hoping someone can assist me in editing the code to loop activating the wb instead of checking for the file on my desktop, or point me in the right direction as to what needs edited.

Sub WaitForFileToExist()


  Dim iError As Long
  Dim iTimeOutValueInMilliseconds As Long
  Dim xTimerElapsedSeconds As Double
  Dim xTimerStartValue As Double
  Dim sPathandFileName As String
    
  'Start a Timer for benchmarking purposes (nothing to do with the solution)
  xTimerStartValue = Timer
  

  'Create the Path and File Name
  sPathandFileName = ("C:\Users\xqs2269\Worksheet in Basis (1).xls")
  
  'Establish the Timeout Value in Milliseconds (1000 Milliseconds = 1 Second)
  '1 minutes
  iTimeOutValueInMilliseconds = CLng(60# * 1000#)
  
  'Wait for the file to exist or wait for Timeout
  iError = WaitUntilFileExistsOrTimeOutOccurs(sPathandFileName, iTimeOutValueInMilliseconds)

  'Save the Elapsed Time
  xTimerElapsedSeconds = Timer - xTimerStartValue
  
  'Display the Results
  If iError = 0 Then
    MsgBox "File Hours.xls exists." & vbCrLf & _
           "Elapsed time = " & Format(xTimerElapsedSeconds, "0.00") & " seconds."
  Else
    MsgBox "File Hours.xls DOES NOT EXIST." & vbCrLf & _
           "Timeout after " & Format(xTimerElapsedSeconds, "0.00") & " seconds."
  End If

End Sub

Function WaitUntilFileExistsOrTimeOutOccurs(sPathandFileName As String, iTimeOutInMilliseconds)
  'This returns the Following values:
  '0 (ZERO) if file Exists
  '1 if Timeout Occurs

  'Frequency to Check For File Existence in Milliseconds (1000 Milliseconds = 1 Second)
  Const iINDIVIDUAL_MILLISECOND_WAIT = 5000

  Dim iError As Long
  Dim iMillisecondsElapsed As Long
    
  Dim NeedMore As Boolean
  
  
  'Wait until the file exists or TIMEOUT
  'Use DoEvents to let other processes execute
  NeedMore = True
  While NeedMore
    If LJMFileExists(sPathandFileName) = True Then
      NeedMore = False
    Else
      DoEvents
      Sleep iINDIVIDUAL_MILLISECOND_WAIT
      DoEvents
      iMillisecondsElapsed = iMillisecondsElapsed + iINDIVIDUAL_MILLISECOND_WAIT
      'Check for timeout - will run forever if the timeout value is LESS THAN ZERO
      If iTimeOutInMilliseconds > 0 And iMillisecondsElapsed >= iTimeOutInMilliseconds Then
        NeedMore = False
        iError = 1
      End If
    End If
  Wend
  
  'Set the return value
  WaitUntilFileExistsOrTimeOutOccurs = iError
End Function
Public Function LJMFileExists(sPathAndFullFileName As String) As Boolean
  'This returns TRUE if a file exists and FALSE if a file does NOT exist
  
  Dim iError As Integer
  Dim iFileAttributes As Integer

  On Error Resume Next
  iFileAttributes = GetAttr(sPathAndFullFileName)
     
  'Check the internal error  return
  iError = Err.Number
  Select Case iError
    Case Is = 0
        iFileAttributes = iFileAttributes And vbDirectory
        If iFileAttributes = 0 Then
          LJMFileExists = True
        Else
          LJMFileExists = False
        End If
    Case Else
        LJMFileExists = False
  End Select

  On Error GoTo 0

End Function