 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / November 2007How to read from txt file from bottom to top?
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 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 > How to read from txt file from bottom to top? 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 > Hi, > [quoted text clipped - 26 lines] > Regards > Piotr 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.