PDA

View Full Version : Solved: Allowing time for the SQL query to fully run



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

SamT
04-16-2013, 10:23 AM
While Timer < WAIT + 5 'Wait 5 seconds
While Timer < WAIT + 60 'Wait 1 minute

snb
04-16-2013, 11:53 AM
set the querytable's property backgroundquery=false

debauch
04-16-2013, 12:43 PM
set the querytable's property backgroundquery=false
I was missing this property ... thank you!!