Vba to loop on a text file and extract data into excel ( reading text file from vba)

Harishiyer · Sep 18, 2015

Hi All,

I have a query. I have a text file in a path . I want to read the text file from excel . the text file looks as below

Code:

------START---------
DATE_OF_ACTIVITY : 09182015_1107_WED
CLASS : 4D        SEC : C
REPORT : MONTH_WISE_REPORT           USER : ARJUN
S.NO      NAME              MATHS_MARK     SCIENCE_MARK     TOTAL     AVERAGE
----      ----              ----------     ------------     -----     -------
  JOUID:443345  SERVICE : NAT
0001    HARISH   785.26   856.87   1642.13           M
0002    GANSDD    45.65    10.00     55.65           T
  JOUID:441122  SERVICE : VAT
0003    HASASA    12.00    10.00     22.00           M
0004    HAAFFG    11.00    10.00     21.00           Y
     END OF SERVICE = DATA

CLASS : 5E        SEC : U
REPORT : DAY_WISE_REPORT           USER : SYSTEM
S.NO      NAME              MATHS_MARK     SCIENCE_MARK     TOTAL     AVERAGE SIGN LOGINID    PUBLIC
----      ----              ----------     ------------     -----     -------   ----    -------    ------
  JOUID:AS1234  SERVICE : CAT
0444    MANJIK    18.91    12.11     50.11           I    OPO      LOPOL         Y
0555    LOPING    40.11    10.00     50.11           O    WPO      MOPOL         N
  JOUID:567654  SERVICE : QAT
0666    QWERTY    40.11    10.00     50.11           P    OBB      LUUOL         N
0888    ZXCVBN    40.11    10.00     50.11           R    OPO      LOUOL         N
     END OF SERVICE = GYANN
 
CLASS : 4E        SEC : F
REPORT : MONTH_WISE_REPORT           USER : MANOJ
S.NO      NAME              MATHS_MARK     SCIENCE_MARK     TOTAL     AVERAGE
----      ----              ----------     ------------     -----     -------
  JOUID:44AA11  SERVICE : LAT
0045    gurtyy   785.26   856.87   1642.13           U
0056    MAJOIU    88.65    10.00     98.65           I
0072    LKPOIU    90.00    10.00    100.00           O
0091    ASDEWQ    45.00    10.00     55.00           P
     END OF SERVICE = DATA
 
------END---------

This is just a sample . The text file will be more tha 40000 lines. The output i want to acheive is looks as below

Code:

[TABLE="width: 996"]
<TBODY>[TR]
[TD]DATE_OF_ACTIVITY</SPAN>
[/TD]
[TD]CLASS</SPAN>
[/TD]
[TD]SEC</SPAN>
[/TD]
[TD]USER</SPAN>
[/TD]
[TD]JOUID</SPAN>
[/TD]
[TD]SERVICE</SPAN>
[/TD]
[TD]S.NO</SPAN>
[/TD]
[TD]NAME</SPAN>
[/TD]
[TD]MATHS_MARK</SPAN>
[/TD]
[TD]SCIENCE_MARK</SPAN>
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]AVERAGE</SPAN>
[/TD]
[TD]END OF SERVICE</SPAN>
[/TD]
[TD]PARAMETER-1</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]443345</SPAN>
[/TD]
[TD]NAT</SPAN>
[/TD]
[TD]0001</SPAN>
[/TD]
[TD]HARISH</SPAN>
[/TD]
[TD]785.26</SPAN>
[/TD]
[TD]856.87</SPAN>
[/TD]
[TD]1642.13</SPAN>
[/TD]
[TD]M</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]HARISH0001</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]443345</SPAN>
[/TD]
[TD]NAT</SPAN>
[/TD]
[TD]0002</SPAN>
[/TD]
[TD]GANSDD</SPAN>
[/TD]
[TD]45.65</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]55.65</SPAN>
[/TD]
[TD]T</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]GANSDD0002</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]441122</SPAN>
[/TD]
[TD]VAT</SPAN>
[/TD]
[TD]0003</SPAN>
[/TD]
[TD]HASASA</SPAN>
[/TD]
[TD]12</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]22</SPAN>
[/TD]
[TD]M</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]HASASA0003</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]441122</SPAN>
[/TD]
[TD]VAT</SPAN>
[/TD]
[TD]0004</SPAN>
[/TD]
[TD]HAAFFG</SPAN>
[/TD]
[TD]11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]21</SPAN>
[/TD]
[TD]Y</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]HAAFFG0004</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0045</SPAN>
[/TD]
[TD]gurtyy</SPAN>
[/TD]
[TD]785.26</SPAN>
[/TD]
[TD]856.87</SPAN>
[/TD]
[TD]1642.13</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]gurtyy0045</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0056</SPAN>
[/TD]
[TD]MAJOIU</SPAN>
[/TD]
[TD]88.65</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]98.65</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]MAJOIU0056</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0072</SPAN>
[/TD]
[TD]LKPOIU</SPAN>
[/TD]
[TD]90</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[TD]O</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]LKPOIU0072</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0091</SPAN>
[/TD]
[TD]ASDEWQ</SPAN>
[/TD]
[TD]45</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]P</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]ASDEWQ0091</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

This data should be moved to a sheet called MONTH_WISE_REPORT

Code:

[TABLE="width: 1035"]
<TBODY>[TR]
[TD]DATE_OF_ACTIVITY</SPAN>
[/TD]
[TD]CLASS</SPAN>
[/TD]
[TD]SEC</SPAN>
[/TD]
[TD]USER</SPAN>
[/TD]
[TD]JOUID</SPAN>
[/TD]
[TD]SERVICE</SPAN>
[/TD]
[TD]S.NO</SPAN>
[/TD]
[TD]NAME</SPAN>
[/TD]
[TD]MATHS_MARK</SPAN>
[/TD]
[TD]SCIENCE_MARK</SPAN>
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]AVERAGE</SPAN>
[/TD]
[TD]SIGN</SPAN>
[/TD]
[TD]LOGINID</SPAN>
[/TD]
[TD]PUBLIC</SPAN>
[/TD]
[TD]END OF SERVICE</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]AS1234</SPAN>
[/TD]
[TD]CAT</SPAN>
[/TD]
[TD]0444</SPAN>
[/TD]
[TD]MANJIK</SPAN>
[/TD]
[TD]18.91</SPAN>
[/TD]
[TD]12.11</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD]OPO</SPAN>
[/TD]
[TD]LOPOL</SPAN>
[/TD]
[TD]Y</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]AS1234</SPAN>
[/TD]
[TD]CAT</SPAN>
[/TD]
[TD]0555</SPAN>
[/TD]
[TD]LOPING</SPAN>
[/TD]
[TD]40.11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]O</SPAN>
[/TD]
[TD]WPO</SPAN>
[/TD]
[TD]MOPOL</SPAN>
[/TD]
[TD]N</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]567654</SPAN>
[/TD]
[TD]QAT</SPAN>
[/TD]
[TD]0666</SPAN>
[/TD]
[TD]QWERTY</SPAN>
[/TD]
[TD]40.11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]P</SPAN>
[/TD]
[TD]OBB</SPAN>
[/TD]
[TD]LUUOL</SPAN>
[/TD]
[TD]N</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]567654</SPAN>
[/TD]
[TD]QAT</SPAN>
[/TD]
[TD]0888</SPAN>
[/TD]
[TD]ZXCVBN</SPAN>
[/TD]
[TD]40.11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]R</SPAN>
[/TD]
[TD]OPO</SPAN>
[/TD]
[TD]LOUOL</SPAN>
[/TD]
[TD]N</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

this data should move a sheet called to DAY_WISE_REPORT

i Dont want to import the text and work on excel.
I want to read from the excel sheet.

I would be vary happy if some one can help.
Thanks,
Harish

Harishiyer · Sep 18, 2015

Guyz I would be happy if I get a reply

Harishiyer · Sep 19, 2015

Guyz, If any one need any more information plz tell... Im very much delayed on the project and will be very happy if some one can assist

Thanks,
Harish

Harishiyer · Sep 19, 2015

Plz guyzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

John_w · Sep 20, 2015

In the text file, you have "REPORT : MONTH_WISE_REPORT USER : ARJUN" followed a few lines later by "END OF SERVICE = DATA".

However, in the MONTH_WISE_REPORT sheet, column "END OF SERVICE" is "GYANN", not "DATA". Which is correct?

The same apparent anomaly also applies to the "REPORT : MONTH_WISE_REPORT USER : MANOJ" section.

Harishiyer · Sep 21, 2015

Hi John,

Really sorry
MONTH_WISE_REPORT Sheet should contain the below data

[TABLE="width: 891"]
<TBODY>[TR]
[TD]DATE_OF_ACTIVITY[/TD]
[TD]CLASS[/TD]
[TD]SEC[/TD]
[TD]USER[/TD]
[TD]JOUID[/TD]
[TD]SERVICE[/TD]
[TD]S.NO[/TD]
[TD]NAME[/TD]
[TD]MATHS_MARK[/TD]
[TD]SCIENCE_MARK[/TD]
[TD]TOTAL[/TD]
[TD]AVERAGE[/TD]
[TD]END OF SERVICE[/TD]
[TD]PARAMETER-1[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED[/TD]
[TD]4D[/TD]
[TD]C[/TD]
[TD]ARJUN[/TD]
[TD="align: right"]443345[/TD]
[TD]NAT[/TD]
[TD="align: right"]1[/TD]
[TD]HARISH[/TD]
[TD="align: right"]785.26[/TD]
[TD="align: right"]856.87[/TD]
[TD="align: right"]1642.13[/TD]
[TD]M[/TD]
[TD]DATA[/TD]
[TD]HARISH0001[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED[/TD]
[TD]4D[/TD]
[TD]C[/TD]
[TD]ARJUN[/TD]
[TD="align: right"]443345[/TD]
[TD]NAT[/TD]
[TD="align: right"]2[/TD]
[TD]GANSDD[/TD]
[TD="align: right"]45.65[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]55.65[/TD]
[TD]T[/TD]
[TD]DATA[/TD]
[TD]GANSDD0002[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED[/TD]
[TD]4D[/TD]
[TD]C[/TD]
[TD]ARJUN[/TD]
[TD="align: right"]441122[/TD]
[TD]VAT[/TD]
[TD="align: right"]3[/TD]
[TD]HASASA[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]22[/TD]
[TD]M[/TD]
[TD]DATA[/TD]
[TD]HASASA0003[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED[/TD]
[TD]4D[/TD]
[TD]C[/TD]
[TD]ARJUN[/TD]
[TD="align: right"]441122[/TD]
[TD]VAT[/TD]
[TD="align: right"]4[/TD]
[TD]HAAFFG[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]21[/TD]
[TD]Y[/TD]
[TD]DATA[/TD]
[TD]HAAFFG0004[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED[/TD]
[TD]4E[/TD]
[TD]F[/TD]
[TD]MANOJ[/TD]
[TD]44AA11[/TD]
[TD]LAT[/TD]
[TD="align: right"]45[/TD]
[TD]gurtyy[/TD]
[TD="align: right"]785.26[/TD]
[TD="align: right"]856.87[/TD]
[TD="align: right"]1642.13[/TD]
[TD]U[/TD]
[TD]DATA[/TD]
[TD]gurtyy0045[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED[/TD]
[TD]4E[/TD]
[TD]F[/TD]
[TD]MANOJ[/TD]
[TD]44AA11[/TD]
[TD]LAT[/TD]
[TD="align: right"]56[/TD]
[TD]MAJOIU[/TD]
[TD="align: right"]88.65[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]98.65[/TD]
[TD]I[/TD]
[TD]DATA[/TD]
[TD]MAJOIU0056[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED[/TD]
[TD]4E[/TD]
[TD]F[/TD]
[TD]MANOJ[/TD]
[TD]44AA11[/TD]
[TD]LAT[/TD]
[TD="align: right"]72[/TD]
[TD]LKPOIU[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD]O[/TD]
[TD]DATA[/TD]
[TD]LKPOIU0072[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED[/TD]
[TD]4E[/TD]
[TD]F[/TD]
[TD]MANOJ[/TD]
[TD]44AA11[/TD]
[TD]LAT[/TD]
[TD="align: right"]91[/TD]
[TD]ASDEWQ[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]55[/TD]
[TD]P[/TD]
[TD]DATA[/TD]
[TD]ASDEWQ0091[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

John_w · Sep 21, 2015

This is quite a tricky format, so it helps if you are accurate in your requirements.

Try this. You must edit the folder path and file name of the text file.

Code:

Public Sub Import_Text_File()

    Dim dataFile As String
    Dim fileLine As String, item As String, parts As Variant
    Dim i As Long, n As Long
    Dim monthData() As Variant, dayData() As Variant
    Dim dateActivity As String, class As String, sec As String, report As String, user As String, _
        jouid As String, service As String
    Dim monthReportDest As Range, mRow As Long, dayReportDest As Range, dRow As Long
    
    dataFile = "C:\Folder\Path\To\data file.txt"            'CHANGE THIS FOLDER PATH AND FILE NAME
    
    With Worksheets("MONTH_WISE_REPORT")
        .Cells.Clear
        .Range("A1:N1").Value = Array("DATE_OF_ACTIVITY", "CLASS", "SEC", "USER", "JOUID", "SERVICE", "S.NO", _
            "NAME", "MATHS_MARK", "SCIENCE_MARK", "TOTAL", "AVERAGE", "END OF SERVICE", "PARAMETER-1")
        Set monthReportDest = .Range("A2")
        mRow = 0
    End With
    
    With Worksheets("DAY_WISE_REPORT")
        .Cells.Clear
        .Range("A1:P1").Value = Array("DATE_OF_ACTIVITY", "CLASS", "SEC", "USER", "JOUID", "SERVICE", "S.NO", _
            "NAME", "MATHS_MARK", "SCIENCE_MARK", "TOTAL", "AVERAGE", "SIGN", "LOGINID", "PUBLIC", "END OF SERVICE")
        Set dayReportDest = .Range("A2")
        dRow = 0
    End With

    Open dataFile For Input As #1
    
    n = 0
    
    While Not EOF(1)
    
        Line Input #1, fileLine
        Debug.Print fileLine
        
        item = GetItem(fileLine, "DATE_OF_ACTIVITY : ")
        If item <> "" Then dateActivity = item
        
        item = GetItem(fileLine, "CLASS : ")
        If item <> "" Then class = item
        
        item = GetItem(fileLine, "SEC : ")
        If item <> "" Then sec = item
        
        item = GetItem(fileLine, "REPORT : ")
        If item <> "" Then report = item
        
        item = GetItem(fileLine, "USER : ")
        If item <> "" Then user = item
        
        item = GetItem(fileLine, "JOUID:")
        If item <> "" Then jouid = item
        
        item = GetItem(fileLine, "SERVICE : ")
        If item <> "" Then service = item
        
        parts = Split(Application.WorksheetFunction.Trim(fileLine), " ")
        
        If UBound(parts) = 5 Then
            If IsNumeric(parts(0)) Then
                n = n + 1
                ReDim Preserve monthData(1 To 14, 1 To n)
                monthData(1, n) = dateActivity
                monthData(2, n) = class
                monthData(3, n) = sec
                monthData(4, n) = user
                monthData(5, n) = jouid
                monthData(6, n) = service
                monthData(7, n) = parts(0)
                monthData(8, n) = parts(1)
                monthData(9, n) = parts(2)
                monthData(10, n) = parts(3)
                monthData(11, n) = parts(4)
                monthData(12, n) = parts(5)
                'Note: monthData(13, 1 to n) is filled by the loop below
                monthData(14, n) = parts(1) & parts(0)
            End If
        End If
        
        If UBound(parts) = 8 Then
            If IsNumeric(parts(0)) Then
                n = n + 1
                ReDim Preserve dayData(1 To 16, 1 To n)
                dayData(1, n) = dateActivity
                dayData(2, n) = class
                dayData(3, n) = sec
                dayData(4, n) = user
                dayData(5, n) = jouid
                dayData(6, n) = service
                dayData(7, n) = parts(0)
                dayData(8, n) = parts(1)
                dayData(9, n) = parts(2)
                dayData(10, n) = parts(3)
                dayData(11, n) = parts(4)
                dayData(12, n) = parts(5)
                dayData(13, n) = parts(6)
                dayData(14, n) = parts(7)
                dayData(15, n) = parts(8)
            End If
        End If
        
        item = GetItem(fileLine, "END OF SERVICE = ")
        
        If item <> "" Then
        
            If report = "MONTH_WISE_REPORT" Then
            
                For i = 1 To n
                    monthData(13, i) = item
                Next
                monthReportDest.Offset(mRow, 0).Resize(n, UBound(monthData)).Value = Application.Transpose(monthData)
                mRow = mRow + n
                n = 0
            
            ElseIf report = "DAY_WISE_REPORT" Then
            
                For i = 1 To n
                    dayData(16, i) = item
                Next
                dayReportDest.Offset(dRow, 0).Resize(n, UBound(dayData)).Value = Application.Transpose(dayData)
                dRow = dRow + n
                n = 0
           
            End If
        End If
                
    Wend
    
    Close #1
    
    MsgBox "Finished"
    
End Sub

Harishiyer · Sep 21, 2015

Hi John,

A big thanks for replying.

I get an error

Compile error. Sub or function not defined.

Thanks,
Harish

John_w · Sep 21, 2015

Sorry, add this code at the bottom of the module:

Code:

Private Function GetItem(text As String, item As String) As String

    Dim p1 As Long, p2 As Long
    
    GetItem = ""
    p1 = InStr(text, item)
    If p1 > 0 Then
        p1 = p1 + Len(item)
        p2 = InStr(p1, text, " ")
        If p2 = 0 Then p2 = Len(text) + 1
        GetItem = Mid(text, p1, p2 - p1)
        'Debug.Print GetItem
    End If

End Function

Harishiyer · Sep 21, 2015

Hey , I seriously dont know how can i thank you....
You have helped so much in this case.
Thanks a lot.
Working beautifully.
Let me see how it runs with the big data

Vba to loop on a text file and extract data into excel ( reading text file from vba)

Harishiyer

Board Regular

Excel Facts

Harishiyer

Board Regular

Harishiyer

Board Regular

Harishiyer

Board Regular

John_w

MrExcel MVP

Harishiyer

Board Regular

John_w

MrExcel MVP

Harishiyer

Board Regular

John_w

MrExcel MVP

Harishiyer

Board Regular

Forum statistics

Share this page

Vba to loop on a text file and extract data into excel ( reading text file from vba)

Board Regular

Excel Facts

Board Regular

Board Regular

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock