vba Read Text file and update in Different Column...

[Mallesh23]

Hi Team
I have textfile with data in it as shown below , my task is to seperate below data in ColumnsSeperation Criteria is *---------So in Below situation Data should get populated in Column A,B,C.Thanks in advance.
xxxxxxxx*--------------yyyyyyyy*--------------zzzzzzzz

My attempted Code. with this I get data in First Column only.

Option Explicit
Sub ReadtxtFileIntoSeveralColumns()
Dim fso As FileSystemObject: Set fso = New FileSystemObjectDim txtStream As TextStreamDim TextLine As StringDim i As LongDim Sheet_i As Worksheet
Set fso = New FileSystemObjectSet txtStream = fso.OpenTextFile("C:\Users\User\Desktop\TextData.txt", ForReading, False)
' Read the file one line at a time   Do While Not txtStream.AtEndOfStream    TextLine = txtStream.ReadLine 'read line        If Left(TextLine, 1) = "*" Then Exit Do     ActiveSheet.Cells(i + 1, 1).Value = TextLine    i = i + 1Else    End If
Loop    ' Close filetxtStream.Close

Set txtStream = NothingSet fso = Nothing
End Sub

Regards,
mg

 

[Leith Ross]

Hello Mallesh,

This worked for me...

Sub ReadtxtFileIntoSeveralColumns()


    Dim File    As String
    Dim Line    As Variant
    Dim RegExp  As Object
    Dim Rng     As Range
    Dim Text    As String
    Dim Wks     As Worksheet
    
        File = "C:\Users\User\Desktop\TextData.txt"
        
        Set Wks = ThisWorkbook.Worksheets("Sheet1")
        Set Rng = Wks.Range("A2:C2")
        
        Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.Pattern = "(.+)\*\-+(.+)\*\-+(.+)\b"


        Open File For Binary Access Read As #1 
            Text = String(LOF(1), Chr(32))
            Get #1 , , Text
        Close #1 
        
            For Each Line In Split(Text, vbCrLf)
                If RegExp.Test(Line) = True Then
                    Rng.Value = Split(RegExp.Replace(Text, "$1 $2 $3"), " ")
                    Set Rng = Rng.Offset(1, 0)
                End If
            Next Line
        
End Sub