将txt文件导入到excel中，并将文本格式设置为列 [英] Import a txt file into excel and format with text to column

问题描述

我试图通过VBA代码将.txt文件导入Excel，然后使用文本到列命令格式化内容.

I am attempting to import a .txt file into Excel via VBA code and then format the content with a text to column command.

txt文件包含以下内容:

The txt file holds content in the following:

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

这仅仅是从txt文件中获取所有数据，并将每个项目分成一个数组.

This merely gets all of the data from the txt file and separates each item into an array.

我想将数组中的项从Range("A5")开始放入excel的列中，并为每行添加一个值.

I'd like to put the items from the array into columns of excel starting from Range("A5") AND account for each new row.

帮助?

(我想在每次选择一个空数组时都向下移动一行，但是每行中有很多空白，这行不通.而且，行的长度根据内容的不同而不一致. )

( 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.)

推荐答案

您需要将数据拆分为两种方式:使用NewLine字符拆分为行，然后使用|

You need to Split the data two ways: into lines using the NewLine character, then into cells using |

请注意，您的文本文件中的换行符可能不是vbNewLine.如果这段代码没有分成几行，那就是第一个要查找的地方.

Note that the line break chacter in your text file may not be vbNewLine. If this code doesn't split into lines, thats the first place to look.

要按姿势完成代码，请尝试

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

或者，将.txt文件也视为 Text

As an alternative, treat the .txt file as, well, Text

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

这篇关于将txt文件导入到excel中，并将文本格式设置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！