Using VBA to read a certain line from a text file

Jaymond Flurrie

Well-known Member
Joined
Sep 22, 2008
Messages
891
Office Version
  1. 2016
So the situation is that I have a text file with a million lines. I need to read a certain line from there. How do I do it so that I don't read every single line from the beginning, but instead, say, line #431278 without reading the lines 1 to 432177 first?

The idea is that since the file is more than huge (over 4 GB), I need to avoid the massive delay that I expect to get if I read the lines in order like 1-1-2-1-2-3-1-2-3-4-1-2-3-4-5 (to get the five first ones) and so on would cause when I need to take a line, process it, pass it forward and take a new line.

Here's a code I use to read lines right now:

Code:
    sFileName = "C:\textfile.txt"

    If Len(Dir$(sFileName)) = 0 Then
        Exit Function
    End If

    iFileNum = FreeFile()
    Open sFileName For Input As iFileNum
    
    Do While Not EOF(iFileNum)
        Line Input #iFileNum, sBuf
        ' now you have the next line of the file in sBuf
        ' do something useful:
        vLine = Split(sBuf, Chr(9)) 'Split on tabs
    Loop
    ' close the file
    Close iFileNum

but that has the problem that when I get to any later lines, it most likely shoots the processing time thru the roof.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Possibly Random Access may assist you:
http://support.microsoft.com/kb/150700


Neither Open for Input, nor Line Input use this retreival pattern:
1-1-2-1-2-3-1-2-3-4-1-2-3-4-5
They go straight through: 1-2-3-4-5.
(Line Input is actually collecting the bytes between the CR/LFs.)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,390
Messages
5,836,978
Members
430,464
Latest member
nickburrett

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top