Capturing query table errors

[T. Erkson]

How do I intercept query table errors, bypassing the Excel ones?

Here's an example of what I'm trying to do using examples from the Help
files:
Sub Macro2()
Workbooks(1).Activate
Application.DisplayAlerts = False ' This doesn't make a difference for my
purposes
On Error GoTo ErrorHandler
Selection.QueryTable.Refresh BackgroundQuery:=False
GoTo Exit_Sub

ErrorHandler:
Msg = "Error # " & Str(Err.Number) & " was generated by " & Err.Source &
Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

Exit_Sub:
On Error Resume Next
Application.DisplayAlerts = True
End Sub

For testing I've moved the database to a different folder so the first error
window is "Please Enter MS JET OLE DB Initialization Information", then my
example message box. I want to COMPLETELY bypass the Windows error window
and just use mine.

Suggestions?

 

[Rob van Gelder]

I think you're already achieving this.
To get the ODBC QueryTable error:
For i = 1 To Application.ODBCErrors.Count
msg = msg & Application.ODBCErrors(1).ErrorString
Next

There is the equivalent for OLEDB QueryTables: Application.OLEDBErrors

 

[T. Erkson]

Thanks Rob. Taking your example I added to my tester and got this which
helps some but still does not bypass the Windows error window :-( For the
query table it is the OLEDB error message that gets tagged, not the ODBC.

Sub Macro2()
'
' Macro2 Macro
'
Workbooks(1).Activate
Application.DisplayAlerts = False
On Error GoTo ErrorHandler

Selection.QueryTable.Refresh BackgroundQuery:=False
GoTo Exit_Sub

ErrorHandler:

msg = "Module Error #=" & Str(Err.Number) & " was generated by " &
Err.Source & Chr(13) & _
"Module Error Description=" & Err.Description & Chr(13)

'ODBC
For i = 1 To Application.ODBCErrors.Count
msg = msg & "ODBC ERROR=" & Application.ODBCErrors(1).ErrorString &
Chr(13)
Next

'OLEDB
For i = 1 To Application.OLEDBErrors.Count
msg = msg & "OLEDB ERROR=" & Application.OLEDBErrors.Item(1).ErrorString
& Chr(13) & _
"OLEDB SQL STATE=" & Application.OLEDBErrors.Item(1).SqlState &
Chr(13)
Next

MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext

Exit_Sub:
On Error Resume Next
Application.DisplayAlerts = True
End Sub

 

[T. Erkson]

Whoa. Instead of running the macro by using the Run Macro button I assigned
the code to a CommandButton and it DID bypass the Windows error window.
Damn, learn something new with Excel every day

Toby