excel - 使用宏将分隔文件导入 Excel (.CommandType = 0)

Question

很多类似的问题正在发生，但谷歌和堆栈溢出并没有触及我认为我需要的部分。我正在尝试使用宏导入管道分隔的文本文件。当我录制宏时，这是我得到的:

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\Users\johnsmith\Desktop\Macro Tinkering\ABC_Financials_ALL(Delimited).txt" _
    , Destination:=Range("$A$1"))
    .CommandType = 0
    .Name = "ABC_Financials_ALL(Delimited)_1"
    .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 = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = "|"
    .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

当我尝试运行宏时，它失败了，当我调试时，它指向“.CommandType = 0”的方向

任何人都知道是否有一个值可以让这个宏运行？还是我的问题比这更糟？

Answer 1

好的。

我更有信心从 Mukul215 的建议中删除可能的垃圾，我不断删除内容并重试。

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\Users\johnsmith\Desktop\Macro Tinkering\ABC_Financials_ALL(Delimited).txt" _
    , Destination:=Range("$A$1"))
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = "|"
    .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

这一个做到了。