Using VBA to read a certain line from a text file

Jaymond Flurrie

Well-known Member
Joined
Sep 22, 2008
Messages
919
Office Version
  1. 365
Platform
  1. Windows
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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
Back
Top