How to read from txt file from bottom to top?

[Coder1215]

Hi,

I'm using excel 2007 and 2003. I have a routine to loop trough *.txt
files in a folder and search for string in a single line. All was fine
until I had to, based on found value, take all lines from this value
to top, e.g. When I find a line marked with specyfic marker ("in this
case "NC") I have to take all values from over this value until I
reach empty line (so looping backwards), then I continue searching.
Code samples (below function to copy data to specyfic cells)

Function move_all(LineofText, k)
Cells(k, "a") = Mid(LineofText, 2, 2)'Here if
Mid(LineofText, 2, 2) = "NC"
'macro should go back and pull all data until it reaches
empty line.
Cells(k, "b") = Mid(LineofText, 6, 8)
Cells(k, "c") = CLng(Mid(LineofText, 17, 10))
Cells(k, "d") = Str(Mid(LineofText, 29, 15))
Cells(k, "e") = CStr(Mid(LineofText, 46, 38))
Cells(k, "f") = CStr(Mid(LineofText, 86, 4))
Cells(k, "g") = Mid(LineofText, 91, 18)
Cells(k, "h") = Trim(Mid(LineofText, 100, 20))
Cells(k, "i") = Mid(LineofText, 125, 8)
Cells(k, "a").Activate
End Function

Thanks in advance for help

Regards
Piotr

 

[Dave D-C]

A possible start:
Sub ReveseFile()
Dim i1%, LineOfText$, Coll1 As New Collection
Open "c:\autoexec.bat" For Input As #1
Do While Not EOF(1)
i1 = i1 + 1
Line Input #1, LineOfText
Coll1.Add LineOfText, Format(i1)
Loop
Close (1)
Do While i1 > 0
Debug.Print Coll1(Format(i1))
i1 = i1 - 1
Loop
End Sub ' D-C Dave

 

[Chip Pearson]

Depending on the file size, you might want to work with it in memory.
Something like



Sub AAA()

Dim TextToMark As String

Dim FileLine As String
Dim FileText As String
Dim Pos As Long
Dim FName As String
Dim FNum As Integer
Dim FileSize As Long
Dim LookFor As String
Dim N As Long
Dim Done As Boolean

Const LINEBREAK = vbCrLf '<<< CHANGE

FName = "C:\Test1\Test.txt" '<<< CHANGE
LookFor = "NC" '<<< CHANGE
FNum = FreeFile()
FileSize = FileLen(FName)

FileText = String$(FileSize, vbNullChar)
Pos = 1

Open FName For Binary Access Read As #FNum

Input #FNum, FileLine
If EOF(FNum) Then
Done = True
End If
Do Until Done
If Len(FileLine) > 0 Then
N = InStr(1, FileLine, LookFor, vbTextCompare)
If N > 0 Then
If N > Len(LookFor) Then
FileLine = Left(FileLine, N - 1)
End If
End If
Mid(FileText, Pos, Len(FileLine) + Len(LINEBREAK)) = FileLine &
LINEBREAK
Pos = Pos + Len(FileLine) + Len(LINEBREAK)
End If
If N > 0 Then
Exit Do
End If
If EOF(FNum) = True Then
Done = True
Else
Input #FNum, FileLine
End If
Loop

Close #FNum
TextToMark = Left(FileText, Pos)
Debug.Print TextToMark
End Sub

 

[Coder1215]

Hi,

I will try the code tomorrow ASAP. The largest file size is over 13
MB, and there are about 30 files in one subfolder, there are 4
subfolders for now and the number will grow each month. The tool is
designed for users to quick fing data they want and put in to excel
making necessarry calculations afted downloading. So the main problem
is speed. I read somewhere that VB6 is a good solution here. Can you
please advise on this?

Thanks very much for help

Regards

Piotr