使用 VBA 将多个文本文件导入到 1 个 Excel 工作簿中的单独 Excel 工作表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21048847/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
importing multiple text files into separate Excel sheets in 1 Excel book using VBA
提问by skyline01
I have written a VBA macro for importing many text files (from 1 folder) onto separate sheets in 1 Excel workbook. All files read into each separate sheet just fine. However, I am seeing a field placement issue. The headers on each text file are the same. But, the field values themselves sometimes get pushed down by a few fields. So, not all fields values line up under their proper headers. Can anybody suggest to me why this is happening? I have tried seeing if it's a tab-delimited or a pipe-delimited issue, but that doesn't seem to be the problem.
我编写了一个 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
End Sub
结束子
回答by Ruth
The "treating F as delimiter" problem is because of this line:
“将 F 视为分隔符”问题是因为这一行：
.TextFileOtherDelimiter = False
Removing it makes the VBA work as expected. The default value for TextFileOtherDelimiter should be "null" rather than False I think.
删除它会使 VBA 按预期工作。我认为 TextFileOtherDelimiter 的默认值应该是“null”而不是 False。
回答by Bernard Saucier
Change .ConsecutiveDelimiter = Falseto .ConsecutiveDelimiter = True
更改.ConsecutiveDelimiter = False为.ConsecutiveDelimiter = True
SOME DETAILS :This probably happens due to the fact that there might be more than one tab delimiting the "columns". Changing this parameter will allow for multiple tabs to be accepted as one.
一些细节：这可能是由于可能有多个选项卡分隔“列”这一事实而发生的。更改此参数将允许将多个选项卡视为一个选项卡。
