vba 将txt文件导入excel，并用文本到列进行格式化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26685103/
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
Import a txt file into excel and format with text to column
提问by jchemp
I am attempting to import a .txt file into Excel via VBA code and then format the content with a text to column command.
我试图通过 VBA 代码将 .txt 文件导入 Excel，然后使用文本到列命令格式化内容。
The txt file holds content in the following:
txt 文件包含以下内容：
DATE | 1 | 2 | 3 | 4 | Something ||||| Not Sure |||||
DATE | 5 | 6 | 7 | 8 | New ||||| Whatever |||||
Currently, using code I've found and slammed together, I've managed to get this far
目前，使用我发现并猛烈抨击的代码，我已经做到了这一点
Sub Sample()
Dim MyData As String, strData() As String, myFile As String
myFile = Application.GetOpenFilename()
Open myFile For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
strData() = Split(MyData, "|")
End Sub
This merely gets all of the data from the txt file and separates each item into an array.
这只是从 txt 文件中获取所有数据并将每个项目分成一个数组。
I'd like to put the items from the array into columns of excel starting from Range("A5") AND account for each new row.
我想将数组中的项目放入从 Range("A5") 开始的 excel 列中，并为每个新行添加帐户。
Help?
帮助？
(Edit: I thought of moving down a row anytime I get to a empty array selection, but there are many blanks within each row and this wouldn't work. Also, the lengths of the rows are inconsistent depending on content.)
（编辑：我想在遇到空数组选择时向下移动一行，但每行中有很多空格，这不起作用。此外，行的长度根据内容不一致。）
回答by chris neilsen
You need to Split the data two ways: into lines using the NewLine character, then into cells using |
您需要以两种方式拆分数据：使用 NewLine 字符拆分为行，然后使用拆分为单元格 |
Note that the line break chacter in yourtext file may not be vbNewLine. If this code doesn't split into lines, thats the first place to look.
请注意，在换行符chacter您的文本文件可能不是vbNewLine。如果这段代码没有分成几行，那就是第一个要查看的地方。
To complete your code as poseted, try
要按照提出的方式完成您的代码，请尝试
Sub Sample()
Dim MyData As String
Dim lineData() As String, strData() As String, myFile As String
Dim i As Long, rng As Range
' lets make it a little bit easier for the user
myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
Open myFile For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
' Split into wholes line
lineData() = Split(MyData, vbNewLine)
Set rng = Range("A5")
' For each line
For i = 0 To UBound(lineData)
' Split the line
strData = Split(lineData(i), "|")
' Write to the sheet
rng.Offset(i, 0).Resize(1, UBound(strData) + 1) = strData
Next
End Sub
As an alternative, treat the .txt file as, well, Text
作为替代方案，将 .txt 文件视为文本
Sub Sample()
Dim fn As Integer
Dim MyData As String
Dim lineData As String, strData() As String, myFile As String
Dim i As Long, rng As Range
myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
Set rng = Range("A5")
' Lets not rely on Magic Numbers
fn = FreeFile
Open myFile For Input As #fn
i = 1
Do While Not EOF(fn)
Line Input #fn, lineData
strData = Split(lineData, "|")
rng.Cells(i, 1).Resize(1, UBound(strData) + 1) = strData
i = i + 1
Loop
Close #fn
End Sub
