用excel vba 将txt批量转excel

发布网友发布时间：2022-04-28 22:17

共1个回答

热心网友时间：2022-06-23 23:29

假设你的文件名为：textfile.txt，并在"我的文档"里面

Sub ImportRange()
    Dim ImpRng As Range
    Dim Filename As String
    Dim r As Long, c As Integer
    Dim txt As String, Char As String * 1
    Dim Data
    Dim i As Integer

    Set ImpRng = ActiveCell
    On Error Resume Next
    Filename = Application.DefaultFilePath & "\textfile.txt"
    Open Filename For Input As #1
    If Err <> 0 Then
        MsgBox "Not found: " & Filename, vbCritical, "ERROR"
        Exit Sub
    End If
    r = 0
    c = 0
    txt = ""
    Application.ScreenUpdating = False
    Do Until EOF(1)
        Line Input #1, Data
        For i = 1 To Len(Data)
            Char = Mid(Data, i, 1)
            If Char = Chr(9) Then
                ActiveCell.Offset(r, c) = txt
                c = c + 1
                txt = ""
            ElseIf i = Len(Data) Then
                ActiveCell.Offset(r, c) = txt
                txt = ""
            Else
                txt = txt & Char
            End If
        Next i
        c = 0
        r = r + 1
    Loop
    Close #1
    Application.ScreenUpdating = True
End Sub