Capturing query table errors

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?

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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"T. Erkson" <notnecessary@nospam.org> wrote in message
news:%23ScNTdrGFHA.3108@tk2msftngp13.phx.gbl...
> 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?
>
>
>

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

"Rob van Gelder" <newsgroups@nojunkmail-vangelder.co.nz> wrote in message
news:euysWt3GFHA.1996@TK2MSFTNGP12.phx.gbl...
> 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
>
> --
> Rob van Gelder - http://www.vangelder.co.nz/excel
>
>
> "T. Erkson" <notnecessary@nospam.org> wrote in message
> news:%23ScNTdrGFHA.3108@tk2msftngp13.phx.gbl...
> > 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?
> >
> >
> >
>
>

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