Please ignore my last post. I thought about this and decided to do a
test for curiosity's sake and was very surprised. You should definitely
go through line by line.

I created a file with 10,000 records of 100 char each. I then accessed a
specific line in the file, closed it and repeated accessing the line 100
times.

Test 1 uses Line Input to go through line by line.
Test 2 loads the whole file into memory and parses the string using MID.
Test 3 loads the whole file into memory and splits the string using SPLIT.

Even if you go through the file line by line and need to access the
penultimate record it is still almost twice as fast as loading the file
into memory and using MID and 2.5 times as fast as using SPLIT. I would
expect it to be faster in general but not if I'm accessing the last
record. Wow! It's probably the SPLIT and MID that take the time I guess.

**TEST BEGINS**
Accessing line 999
Reading 100 files
Test 1: 00:00 (nn:ss)
Test 2: 00:07
Test 3: 00:10
**TEST ENDS**

**TEST BEGINS**
Accessing line 9999
Reading 100 files
Test 1: 00:04
Test 2: 00:07
Test 3: 00:10
**TEST ENDS**

**TEST BEGINS**
Accessing line 99
Reading 1000 files
Test 1: 00:00
Test 2: 01:12
Test 3: 01:40
**TEST ENDS**


My test code in case you're interested:

Sub ReadWholeFileTest()

Dim myFile As String
Dim myLines() As String
Dim hFile As Integer
Dim LineToAccess As Long
Dim myPath As String
Dim NumberOfTimesToReadFile As Integer
Dim myStartTime As Date
Dim myLineCounter As Integer
Dim myLine As String
Dim myRecordLength As Long
Dim PositionOfRecord As Long

LineToAccess = 99
myPath = "c:\test.txt"
NumberOfTimesToReadFile = 100

Debug.Print "**TEST BEGINS**"
Debug.Print "Accessing line " & LineToAccess
Debug.Print "Reading " & NumberOfTimesToReadFile & " files"

'Test 1 - normal line by line
myStartTime = Now
For i = 1 To NumberOfTimesToReadFile
hFile = FreeFile
Open myPath For Input As #hFile
Do While Not EOF(hFile)
Line Input #hFile, myLine
myLineCounter = myLineCounter + 1
If myLineCounter = LineToAccess Then Exit Do
Loop
Close #hFile
myLineCounter = 0
Next i
Debug.Print "Test 1: " & Format(myStartTime - Now, "nn:ss")
Debug.Print myLine

'Test 2 - by parsing string using mid
myLine = ""
myRecordLength = 100 + Len(vbCrLf)
myStartTime = Now
For i = 1 To NumberOfTimesToReadFile
hFile = FreeFile
Open myPath For Input As #hFile
myFile = Input(LOF(hFile), hFile)
Close #hFile
PositionOfRecord = (myRecordLength * (LineToAccess - 1)) + 1
myLine = Mid$(myFile, PositionOfRecord, 100)
Next i
Debug.Print "Test 2: " & Format(myStartTime - Now, "nn:ss")
Debug.Print myLine


'Test 3 - by splitting string
myLine = ""
myRecordLength = 100 + Len(vbCrLf)
myStartTime = Now
For i = 1 To NumberOfTimesToReadFile
hFile = FreeFile
Open myPath For Input As #hFile
myFile = Input(LOF(hFile), hFile)
Close #hFile
myLines = Split(myFile, vbCrLf)
myLine = myLines(LineToAccess - 1)
Next i
Debug.Print "Test 3: " & Format(myStartTime - Now, "nn:ss")
Debug.Print myLine

Debug.Print "**TEST ENDS**"

End Sub




Gareth wrote:
As far as I know Tim's spot on, in that

(a) there's no way as far as I know - unless you start to get into
random access files;
and
(b) you will be amazed at how blindingly fast VBA can search through
thousands of files, parse each line, compare strings. It's amazing.

However, if you need to access specific points in a file... I could
understand why this might sound a good idea -- say you want to go to
line 9999, it *might* be quicker to read the file in one fell swoop
rather than bit by bit. (But surely - definitely - not -as Tim wrote -
if you need to just access data at the beginning of the file).

To do this maybe something like:

Sub ReadWholeFile()

Dim myFile As String
Dim myLines() As String
Dim hFile As Integer

hFile = FreeFile
Open "c:\test.txt" For Input As #hFile
myFile = Input(LOF(hFile), hFile)
Close #hFile

'If file has records of identical length (say 100)
'you could access line 999 thus:
myrecordlength = 100 + Len(vbCrLf)
Line999 = Mid$(myFile, myrecordlength * 999, myrecordlength)

'Alternatively, you could split everything up by line into an
'array
myLines = Split(myFile, vbCrLf)
'You can now address line 999 thus:
Line999 = myLines(999)

End Sub

I have no idea if this will be quicker or (massively) slower than
reading a large file piecemeal. I would be interested to know if you do
a performance test!

HTH,
G



Tim Williams wrote:

I don't think you can "go directly" without having to count lines: in
any
case it doesn't take long to count to 3...

How large are the files? Even if you checked them all it wouldn't
take too
long (unless of course this is something you need to do every
minute). For
a one-time job even a couple of minutes should be acceptable.


Tim.