使用宏(.CommandType = 0)将定界文件导入Excel

问题描述

发生了许多类似的问题，但Google和堆栈溢出并未触及我认为我需要的部分.我正在尝试导入带有宏的管道定界文本文件.当我录制宏时，这就是我得到的:

Lots of similar questions going on but google and stack overflow are not touching the part I think I need. I am trying to import a pipe delimited text file with a macro. When I record macro, this is what I get:

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"的方向

When I try to run the macro, it fails, and when I debug, it points me in the direction of ".CommandType = 0"

任何人都知道是否存在可以使该宏运行的值?还是我的问题比那更糟?

Anyone know if there is a value that will get this macro going? Or are my problems worse than that?

推荐答案

好的.

我对删除Mukul215的建议中可能存在的垃圾更加有信心，我一直在删除内容并重试.

More confident in just deleting possible trash off Mukul215's suggestion, I kept deleting stuff and retrying.

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

而这个做到了.

这篇关于使用宏(.CommandType = 0)将定界文件导入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！