Post by colmkavHi,
I get the following message
"Microsoft Office Excel is waiting for another application to complete
an ole action"
when I run a series of Access 2007 queries via VBA code. How can I
stop these messages occuring and what kind of reason does it occur? If
I keep clicking ok I get to the end and everything
runs fine but obviously I dont want this to happen.
Colm
Ok here is the code:
Private Sub CommandButton2_Click()
Dim wsSettings As Worksheet
Dim sDBDailyMonitoring As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wsSettings = ThisWorkbook.Worksheets("Settings")
sDBDailyMonitoring = wsSettings.Range("DailyMonitoringDB")
CreateReports sDBDailyMonitoring
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Process Complete: " & Time
End Sub
Sub CreateReports(sDBDailyMonitoring As String)
'run the report queries and store in the report tables
Dim rgCursor As Range
Dim l_objDailyMonitoring As New clsDailyMonitoringDB
Dim ws As Worksheet
Dim sQryName As String
Dim l_QryDef As QueryDef
Dim dtContextDate As Date
l_objDailyMonitoring.OpenDatabase sDBDailyMonitoring
Set ws = ThisWorkbook.Worksheets("Settings")
dtContextDate = ws.Range("ContextDate").Value
Set rgCursor = ws.Range("QueryList").Offset(1, 0)
Do While Not rgCursor = ""
sQryName = rgCursor.Value
'run SQL maketable query which put results in new table
If rgCursor.Offset(0, -1) = True Then
l_objDailyMonitoring.RunRuleQuery sQryName, dtContextDate
End If
' l_objDailyMonitoring.RunReportQuery sQryName
Set rgCursor = rgCursor.Offset(1, 0)
Loop
l_objDailyMonitoring.CloseDatabase
Exit Sub
ERR_HANDLER:
MsgBox "Error: " & Err.Description, vbExclamation
l_objDailyMonitoring.CloseDatabase
End Sub