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
Bookmarks