sql - 详细的 QueryTables 错误处理

Question

下午好，

我正在寻找一种处理 QueryTable 错误的方法。我查看了有关 google 和 stackoverflow 的其他问题，但它们似乎没有回答我要问的具体问题。

基本上，有没有办法确定处理 QueryTables 错误时的具体错误是什么？

代码:

On Error Goto queryError

With Activesheet.QueryTables...

....
End With


queryError:
    Msgbox("There was an error with the QueryTables. The reason for the error was: " & myError)

有没有办法设置 myError 以提供更多特定于问题的详细信息，即使这意味着选择某种状态代码？例如

QueryTables.StatusCode...

或者其他的东西？

提前致谢。

Answer 1

如何处理错误:

Excel VBA 不支持 Try Catch Finally 。相反，它使用 On Error GoTo
要完全控制 Excel 中的错误处理，您必须使用标签(始终以冒号结尾)。

在此示例中，两个标签是:

再次尝试:

查询错误:

假设正在创建的查询表来自一个类似于以下内容的文本文件:



首次运行例程时，系统会提示用户输入三个输入:

文件路径

新表名

单元格(即范围)粘贴到

如果这些输入中的任何一个发生错误，代码将立即转到标签 queryError:

所以，假设用户没有输入有效的文件路径，它看起来像这样:



如果用户单击"is"(重试)，则 Resume tryAgain 会将代码带回该标签并重新遍历。

注意最后的 Select Case。这就是您可以控制如何处理特定错误的方式。

这是要粘贴到模块中的代码:

Option Explicit

Sub CreateQueryTable()

    'Assign values to these variables by prompting user with Input Boxes
    Dim filepath As String
    Dim qryTableName As String
    Dim startCellForTable As Range

    'These variables are used in the error handling prompts
    Dim Msg As String
    Dim Ans As Integer

    'If an error occurs, code will go to the label `queryError:`
    On Error GoTo queryError


tryAgain:

    'Prompt user for the filename of the .txt file to use as QueryTable Source
    filepath = InputBox("Please enter filepath of text file to use as the source")

    'Prompt user to name the new Query Table
    qryTableName = InputBox("Please enter name of Query Table")

    'Prompt user for the cell to put table at
    Set startCellForTable = Application.InputBox(Prompt:="Please select a cell where you would like to paste the table to", Type:=8)


    'If user hits OK, check to see that they at least put something as a value
    If filepath <> "" And qryTableName <> "" And startCellForTable <> "" Then

        'format filepath variable so can pass it as argument to QueryTables.Add
        'Trim any leading or trailing spaces from qryTableName
        filepath = "TEXT;" & filepath
        qryTableName = Trim(qryTableName)

    End If

    'Create QueryTable at Range("A1")
    With ActiveSheet.QueryTables.Add(Connection:=filepath, Destination:=Range(startCellForTable.Address))
        .Name = qryTableName
        .Refresh BackgroundQuery:=False
    End With

    'If there are no errors, exit the procedure (so the `queryError:` code won't execute)
    Exit Sub


queryError:

    Msg = ""

    'Say that an error occured
    Msg = Msg & "An error occurred with the Query Table. " & vbNewLine & vbNewLine

    'Use Excel's built-in Error object (named `Err`) to show error number and 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 that a valid range was selected" & vbNewLine

        'Application defined or Object defined error
        Case 1004

            Msg = Msg & vbNewLine & "Please check that this filepath is correct: " & vbNewLine & vbNewLine & filepath & vbNewLine

        Case Else


    End Select


    'Prompt user to Try Again
    Msg = Msg & vbNewLine & vbNewLine & "Try again?"

    Ans = MsgBox(Msg, vbYesNo + vbCritical)

    'If user says Yes, clear the error, and resume execution of code at label `TryAgain:`
    If Ans = vbYes Then Resume tryAgain


End Sub