使用VBA将多个文本文件导入到1 Excel书中的单独Excel工作表中

Question

我编写了一个VBA宏，用于将许多文本文件（从1个文件夹）导入到1 Excel工作簿中的单独工作表中。 所有文件都读入每个单独的工作表中。 但是，我看到了现场安置问题。 每个文本文件上的标头相同。 但是，字段值本身有时会被几个字段推低。 因此，并非所有字段值都在其正确的标题下排列。 谁能告诉我为什么会这样？ 我试过看看这是一个制表符分隔的问题还是一个管道分隔的问题，但这似乎不是问题所在。

Sub MultipleTextFilesIntoExcelSheets()
    Dim i As Integer 'a counter to loop through the files in the folder
    Dim fname As String, FullName As String 'fname is the name of the file, and FullName is the name of its path
    Dim ws As Worksheet 'a workbook object for the workbook where the current macro is running
i = 0 'seed the counter
'get the name of the first text file
fname = Dir("C:dummy_path*txt")
'loop through the text files to put them onto separate sheets in the Excel book
While (Len(fname) > 0)
    'get the full path of the text file
    FullName = "C:dummy_path" & fname
    i = i + 1 'get ready for the next iteration
    Set ws = ThisWorkbook.Sheets("Sheet" & i) 'the current sheet
    With ws.QueryTables.Add(Connection:="TEXT;" & FullName, Destination:=ws.Range("A1"))
        .Name = "a" & i
        .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 = True    'we are using a tab-delimited file
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        fname = Dir
    End With
Wend

结束子

Answer 1

"将 F 视为分隔符"问题是因为以下行：

.TextFileOtherDelimiter = False

删除它会使 VBA 按预期工作。我认为文本文件其他分隔符的默认值应该是"null"而不是 False。

Answer 2

将.ConsecutiveDelimiter = False更改为.ConsecutiveDelimiter = True

一些细节：这可能是由于可能有多个选项卡分隔"列"而发生的。更改此参数将允许将多个选项卡接受为一个选项卡。