将单独的行和列从 .txt 文件导入 Excel 工作表 (vba)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20301663/
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 separate rows and columns from a .txt file into an excel worksheet(vba)
提问by user3052345
I have been following an example for importing .txt files into excel, the only problem is i can't seem to figure out how to make this line split the data into 2 separate columns
我一直在关注将 .txt 文件导入 excel 的示例，唯一的问题是我似乎无法弄清楚如何让这条线将数据拆分为 2 个单独的列
Set oFS = oFSO.OpenTextFile(filePath)
Do While Not oFS.AtEndOfStream
Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1) = oFS.ReadLine
Loop
oFS.Close
The .txt file I need to import has 2 columns and N rows , my question is , how can I read it without both columns from the .txt file being written into 1 column on the excel worksheet?
我需要导入的 .txt 文件有 2 列和 N 行，我的问题是，如何在不将 .txt 文件中的两列写入 Excel 工作表上的 1 列的情况下读取它？
N
X Y
X2 Y2
X3 Y3
etc..
Thats how the .txt file must look. Thanks in advance.
这就是 .txt 文件的外观。提前致谢。
采纳答案by jacouh
This will do the job, using VBA Split() function:
这将完成这项工作，使用 VBA Split() 函数：
Sub sof20301663ImportTextFile()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim i, iup, varArray
Dim fso As Object, tso As Object
Dim strFilePath, strLine
' adapt here your text file name: '
strFilePath = "MyTestFile.txt"
Set fso = CreateObject("Scripting.FileSystemObject") '
' get TextStream:
Set tso = fso.OpenTextFile(strFilePath, ForReading)
i = 2
Do While Not tso.AtEndOfStream '
' read a line from the file:
strLine = tso.ReadLine
' split with separator tab:
varArray = Split(strLine, vbTab)
iup = UBound(varArray)
' split with separator space:
If (iup <= 0) Then
varArray = Split(strLine, " ")
iup = UBound(varArray)
End If
' fill a cell: using strLine as range address:
strLine = "A" & i
Range(strLine).Value = varArray(0)
Range(strLine).NumberFormat = "General"
' if there is more then two words, fill cell 2:
If (iup > 0) Then
strLine = "B" & i
Range(strLine).Value = varArray(1)
Range(strLine).NumberFormat = "General"
End If
i = i + 1
Loop
' clean objects:
tso.Close
Set tso = Nothing
Set fso = Nothing
End Sub
Content of MyTestFile.txt:
MyTestFile.txt 的内容：
50
info computer
image logo
tendancy soulant
Excel Result:
excel结果：
