时间戳

首页 > 解决方案 > 在特定工作表上运行 VBA

excel - 在特定工作表上运行 VBA

问题描述

我有一些有效的 Excel 代码，但只能在活动工作表上运行。我想准确指定要在其上输入数据的工作表。这是代码：

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & GetFile, Destination:=Range( _
    "$A$2"))
    .Name = "logexportdata"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(5, 2, 2, 2, 2, 2, 9, 9, 9, 9, 9)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
End Sub

我尝试将第一行更改为： With Sheet("MigrationReport")..QueryTables.Add(Connection:= _

所以它不仅仅是活动工作表，我可以在特定工作表上运行它。但这给了我一个编译错误。如何用工作表名称替换活动工作表？

标签： excelvba

解决方案

你试过了吗：

With ThisWorkbook.Worksheets("MigrationReport")
    With .QueryTables.Add(Connection:= _
        "TEXT;" & GetFile, Destination:=Range( _
        "$A$2"))
        .Name = "logexportdata"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(5, 2, 2, 2, 2, 2, 9, 9, 9, 9, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End With

推荐阅读