vba - Excel 中录制的宏在运行时失败

Question

Sub Macro3()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://..." _
        , Destination:=Range("Sheet6!$G$23"))

        ''// The line above fails with the error:
        ''// "Run-time error '-2147024809 (80070057)':
        ''//     The destination range is not on the same worksheet
        ''//     that the Query table is being created on."

        .Name = _
        "?tmp=toolbar_FlvTube_homepage&prt=..."
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

如注释中所述，录制的宏失败。

Answer 1

您在工作表 6 处于事件状态时记录了宏，但现在正试图在不同的工作表上运行它。要为当前事件工作表运行宏，只需将代码更改如下:

Sub Macro3()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://..." _
        , Destination:=ActiveSheet.Range("$G$23"))
        ...
    End With
End Sub

编辑: (回应评论):

I need to be able to paste the results of the query to a different sheet then the active one since the macro can be run at any time and must be paste to the same location everytime. Perhaps there is a way to change your active sheet with code?

当两张表不同时会发生错误，因此如果您希望在特定表上发生魔法，您应该指定该表而不是使用 ActiveSheet .以下代码将始终将 QueryTable 放在 Sheet6 上:

Sub Macro3()
    With Sheet6.QueryTables.Add(Connection:= _
        "URL;http://..." _
        , Destination:=Sheet6.Range("$G$23"))
        ...
    End With
End Sub