将txt文件导入excel并将文本格式化为列
问题描述:
我试图通过VBA代码将.txt文件导入到Excel中,然后使用文本格式将内容格式化为列命令。将txt文件导入excel并将文本格式化为列
TXT文件保存在下面的内容:
DATE | 1 | 2 | 3 | 4 | Something ||||| Not Sure |||||
DATE | 5 | 6 | 7 | 8 | New ||||| Whatever |||||
目前,使用代码我发现和抨击在一起,我已经成功地走到这一步
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
这仅仅得到来自txt文件的所有数据并将每个项目分隔成一个数组。
我想将数组中的项目放入从Range(“A5”)开始的excel列和每个新行的帐户。
帮助? (编辑:我想在任何时候,我得到一个空的数组选择,但每行内有很多空白,这是行不通的。而且,行的长度是不一致的,取决于内容。)
答
您需要两种方式分割数据:使用|
注到使用换行符线,然后进入细胞,在您文本文件中的换行符chacter可能不vbNewLine
。如果这段代码没有分成几行,这就是第一个看到的地方。
为了方便您的代码poseted,尝试
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
作为替代,治疗中的.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
你正在处理_Text_文件任何特别的原因作为_Binary_? – 2014-11-01 00:41:17
没有理由,除了天真地尝试的东西 – jchemp 2014-11-03 17:32:42