debauch
04-16-2013, 09:41 AM
Hi there,
I have some code that has been an issue in the past that i ended up wasting hours trying to figure out ... the need to use this type of code has come up again, so i thought i would reach out to the smartest audience in VBA. :hi:
What i am trying to do, is allow the below code to execute without skipping past the query completion/refresh. It waits, but then finishes the code and the data when i go back in hasnt actually refreshed ....
thoughts? Is there a way to purposefully add a delay? The query takes about 1 minute to run.
Private Sub Workbook_Open()
'Application.Wait Now + TimeSerial(0, 0, 10)
Dim WAIT As Double
WAIT = Timer
While Timer < WAIT + 5
DoEvents 'do nothing
Wend
'Application.ScreenUpdating = False
'code start here
Refresh.Refresh 'refresh the data
Mail_Selection_Range_Outlook_Body 'send via outlook
'code end here
'Application.ScreenUpdating = True
Application.DisplayAlerts = False
'quit excel and dump memory
ActiveWorkbook.Save
Dim appExcel As Excel.Application
Set appExcel = GetObject(, "Excel.Application")
appExcel.Quit
Set appExcel = Nothing
End Sub
I have some code that has been an issue in the past that i ended up wasting hours trying to figure out ... the need to use this type of code has come up again, so i thought i would reach out to the smartest audience in VBA. :hi:
What i am trying to do, is allow the below code to execute without skipping past the query completion/refresh. It waits, but then finishes the code and the data when i go back in hasnt actually refreshed ....
thoughts? Is there a way to purposefully add a delay? The query takes about 1 minute to run.
Private Sub Workbook_Open()
'Application.Wait Now + TimeSerial(0, 0, 10)
Dim WAIT As Double
WAIT = Timer
While Timer < WAIT + 5
DoEvents 'do nothing
Wend
'Application.ScreenUpdating = False
'code start here
Refresh.Refresh 'refresh the data
Mail_Selection_Range_Outlook_Body 'send via outlook
'code end here
'Application.ScreenUpdating = True
Application.DisplayAlerts = False
'quit excel and dump memory
ActiveWorkbook.Save
Dim appExcel As Excel.Application
Set appExcel = GetObject(, "Excel.Application")
appExcel.Quit
Set appExcel = Nothing
End Sub