sql - Detailed QueryTables Error Handling -

good afternoon,

i looking way handle querytable errors. have looked @ other questions on google , stackoverflow, not appear answer specific question trying ask.

basically, there way determine specific error when handling querytables error?

code:

on error goto queryerror  activesheet.querytables...  .... end   queryerror:     msgbox("there error querytables. reason error was: " & myerror) 

is there way set myerror give more details specific problem was, if means selecting sort of status code? eg

querytables.statuscode... 

or something?

thanks in advance.

how handle errors:
excel vba doesn't supporttry catch finally. instead, uses on error goto

for full control on error-handling in excel must use labels (which end in colon).

in example, 2 labels are:

• tryagain:
• queryerror:

assume query table being created text file looks like:

when first run routine, user prompted 3 inputs:

• filepath
• new table name
• cell (i.e. range) paste into

if error occurs on of these inputs, code go label queryerror:

so, user didn't enter in valid filepath, this:

if user clicks yes (to try again), resume tryagain take code label , go through over.

pay attention select case @ end. how can control how want handle specific errors.

here code paste in module:

option explicit  sub createquerytable()      'assign values these variables prompting user input boxes     dim filepath string     dim qrytablename string     dim startcellfortable range      'these variables used in error handling prompts     dim msg string     dim ans integer      'if error occurs, code go label `queryerror:`     on error goto queryerror   tryagain:      'prompt user filename of .txt file use querytable source     filepath = inputbox("please enter filepath of text file use source")      'prompt user name new query table     qrytablename = inputbox("please enter name of query table")      'prompt user cell put table @     set startcellfortable = application.inputbox(prompt:="please select cell paste table to", type:=8)       'if user hits ok, check see @ least put value     if filepath <> "" , qrytablename <> "" , startcellfortable <> ""          'format filepath variable can pass argument querytables.add         'trim leading or trailing spaces qrytablename         filepath = "text;" & filepath         qrytablename = trim(qrytablename)      end if      'create querytable @ range("a1")     activesheet.querytables.add(connection:=filepath, destination:=range(startcellfortable.address))         .name = qrytablename         .refresh backgroundquery:=false     end      'if there no errors, exit procedure (so `queryerror:` code won't execute)     exit sub   queryerror:      msg = ""      'say error occured     msg = msg & "an error occurred query table. " & vbnewline & vbnewline      'use excel's built-in error object (named `err`) show error number , description of error     msg = msg & err.number & ": " & error(err.number) & vbnewline & vbnewline      select case err.number          'type mismatch         case 13           'object required         case 424              msg = msg & vbnewline & "please check valid range selected" & vbnewline          'application defined or object defined error         case 1004              msg = msg & vbnewline & "please check filepath correct: " & vbnewline & vbnewline & filepath & vbnewline          case else       end select       'prompt user try again     msg = msg & vbnewline & vbnewline & "try again?"      ans = msgbox(msg, vbyesno + vbcritical)      'if user says yes, clear error, , resume execution of code @ label `tryagain:`     if ans = vbyes resume tryagain   end sub