File Access - Reading the Last Line?

[Garratt]

This is for Excel97 VBA if it matters.

I've been trying to read up on the file access methods but I'm a little slow this morning:
(example) http://www.erlandsendata.no/english/...eaccessgeneral

OK, I'm reading a lot of Oracle .sql files (text files, sometimes very big that are created from Oracle queries). Some of these files can get up to 33MB or so. I read them into a spreadsheet and then make reports with them.

Is there a way to quickly read the last line (or last two lines) of the text file? With a 33MB text file it takes a LONG time to INPUT# each line in a loop just to get to the last line.

I'm just trying to make a preview window on a form to show the first 10 or so lines and then grab the last line (or last two lines) which contain some file info.


To sum up the question:
I want to open a txt file and INPUT# the first ten lines, then skip quickly to the last two lines and put those two lines in string variables.


Thanks

[Ecniv]

I think its possible to use one of the file pointer variables/functions to return the characters at a certain point.

Then you could loop bakwards until you have the two/three/four return characters (chr$(10) or chr$(13) or both).

Not actually messed around with text file manipulation inna while .. I'll experiment.

You'll need the length of the file, which is a function too. Whether or not it'll work with 33mb files.. dunno . worth a try.

As a test, cut the top 20 lines, display 10 of them and the last two.


Vince

[Ecniv]

Public Sub scanfile(ByVal strFile As String, ByVal lngBotRows As Long)
 
    Dim strLine As String, strChar As String
    Dim dblLen As Double, dblLoop As Double
    Dim lngFound As Long
    Dim blnLeave As Boolean
    
    
    Close #1
    Open strFile For Binary As #1
    
'    Debug.Print "End of File", EOF(1)
'    Debug.Print "File Length", FileLen(strFile), LOF(1)
'    Debug.Print "Position in file", Loc(1)
    
    Line Input #1, strLine
'    Debug.Print strLine
    
    blnLeave = False
    lngFound = 0
    dblLoop = LOF(1) - 1
    Do Until blnLeave
        
        Seek #1, dblLoop
        strChar = String(2, " ")
        Get #1, , strChar
        
        If strChar = vbCrLf Then lngFound = lngFound + 1
        If lngFound > lngBotRows Then blnLeave = True
        If dblLoop < 1 Then blnLeave = True
        dblLoop = dblLoop - 1
    Loop
    
    For dblLoop = 0 To lngBotRows
        Line Input #1, strLine
        Debug.Print "Line : ", strLine
    Next
    
    Close #1
 
End Sub

Attached is the test text file.


Vince