Excel vba to open text file and look from end to beginning for sequence of lines.

D

Demosthenes&Locke

Board Regular
Joined
May 11, 2010
Messages
93
Is there a way to make excel open a text file and look for a sequence of lines, not just in a single string, but reading one line at a time, find several lines together? I want to make it automatically check a text log to see when the last "Backup" event was.

Any help would be appreciated.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
At different points in the log file there is a sequence,
=======
=======
Updated
=======
=======

The only problem when looking for series of ===== is that every update is surrounded by them, and looking at updated, every time a file is updated it has that word after it. The log includes several such "updated" blocks, but I only need to know what's happened since the last one.
And there's the problem of looking through it backwards to find them on separate lines.
 
Upvote 0
Code: 
===================
===================
===Updated
===================
===================
Date/Time: 8/23/2010 10:16:44 AM                       ============================================================
MDA-2\Final Planning\Exchanger-Condensers\WO#  989750003826 Flasher Reboiler OCIC\Planning Package Tool.xls, updated file
MDA-2\Final Planning\Piping\WO# 989750003817 081-156 LS-15602 Move Up and  Install PI\DSCN2434.JPG, updated file
MDA-2\Final Planning\Piping\WO# 989750003817 081-156 LS-15602 Move Up and  Install PI\DSCN2435.JPG, updated file
MDA-2\Final Planning\Piping\WO# 989750003817 081-156 LS-15602 Move Up and  Install PI\DSCN2449.JPG, updated file
MDA-2\Final Planning\Piping\WO# 989750003817 081-156 LS-15602 Move Up and  Install PI\Picture.doc, updated file
MDA-2\Final Planning\Piping\WO# 989750003817 081-156 LS-15602 Move Up and  Install PI\Planning Package Tool.xls, updated file
MDA-2\Final Planning\Piping\WO# 989750003817 081-156 LS-15602 Move Up and  Install PI\WO#989750003817 ISO Template.xls, updated file
MDA-2\Final Planning\WO# 989750002218 PM Neutralization Tank Gearbox Agitator\Final Planning Template.xls, updated file
MDI\Package Final Plan\Vessel Entry- External Inspection\WO# 989750002208 Inspect Atmospheric Condesate Receiver90-112\Planning Package Tool.xls, updated file
MR-1\Final Planning\Fabrication\Wo# 989750002362 Replace MCB Outlet Line 166 Chiller\Planning Package Tool.xls, updated file
MR-1\Final Planning\PIPING\WO# 989750003121 Remove- Inspect Double Wall Pipe 90-33\DSCN2358.JPG, updated file
MR-1\Final Planning\PIPING\WO# 989750003121 Remove- Inspect Double Wall Pipe 90-33\DSCN2368.JPG, updated file
MR-1\Final Planning\PIPING\WO# 989750003121 Remove- Inspect Double Wall Pipe 90-33\DSCN2369.JPG, updated file
MR-1\Final Planning\PIPING\WO# 989750003121 Remove- Inspect Double Wall Pipe 90-33\DSCN2371.JPG, updated file
MR-1\Final Planning\PIPING\WO# 989750003121 Remove- Inspect Double Wall Pipe 90-33\DSCN2453.JPG, updated file
MR-1\Final Planning\PIPING\WO# 989750003121 Remove- Inspect Double Wall Pipe 90-33\DSCN2454.JPG, updated file
MR-1\Final Planning\PIPING\WO# 989750003121 Remove- Inspect Double Wall Pipe 90-33\DSCN2455.JPG, updated file
MR-1\Final Planning\PIPING\WO# 989750003121 Remove- Inspect Double Wall Pipe 90-33\DSCN2457.JPG, updated file
MR-1\Final Planning\PIPING\WO# 989750003121 Remove- Inspect Double Wall Pipe 90-33\DSCN2458.JPG, updated file
MR-1\Final Planning\PIPING\WO# 989750003121 Remove- Inspect Double Wall Pipe 90-33\Picture.doc, updated file
MR-1\Final Planning\PIPING\WO# 989750003121 Remove- Inspect Double Wall Pipe 90-33\Planning Package Tool.xls, updated file
MR-1\Final Planning\PIPING\WO# 989750003321 Inspect - Repair Double Wall Pipe 90-537\Planning Package Tool.xls, updated file
Date/Time: 8/23/2010 10:16:44 AM Files updated: 22      ============================================================
===================
===================
===Updated
===================
===================
Date/Time: 8/23/2010 3:04:12 PM                       ============================================================
MR-1\Final Planning\Fabrication\Wo# 989750002362 Replace MCB Outlet Line 166 Chiller\Planning Package Tool.xls, updated file
MR-1\Final Planning\UT External -Internal Inspection\WO# 9989750002234 Tower Dump Tank OCC\Planning Package.xls, updated file
Date/Time: 8/23/2010 3:04:12 PM Files updated: 2      ============================================================
Date/Time: 8/23/2010 4:50:24 PM                       ============================================================
MDA-2\Final Planning\Piping\WO# 989750003817 081-156 LS-15602 Move Up and  Install PI\Planning Package Tool.xls, updated file
Date/Time: 8/23/2010 4:50:24 PM Files updated: 1      ============================================================
Date/Time: 8/24/2010 7:20:24 AM                       ============================================================
MDI\Package Final Plan\Condenser-Exchanger\WO# 989750002184 Open-Clean 90-504 Final Column Vent Scrubber\Planning Package Tool.xls, updated file
Date/Time: 8/24/2010 7:20:24 AM Files updated: 1      ============================================================
Date/Time: 8/24/2010 9:00:32 AM                       ============================================================
MDA-2\Final Planning\Exchanger-Condensers\WO# 989750003825 Aniline Stripper Condenser 81-32-051 R\Planning Package Tool.xls, updated file
Date/Time: 8/24/2010 9:00:32 AM Files updated: 1      ============================================================
Date/Time: 8/24/2010 12:50:25 PM                       ============================================================
MDA-2\Final Planning\81-053 Amine Surge Tank (Open)\Planning Package Tool.xls, updated file
Date/Time: 8/24/2010 12:50:25 PM Files updated: 1      ============================================================
Date/Time: 8/24/2010 1:30:33 PM                       ============================================================
MR-1\Final Planning\Fin Fan OCIC-PM\WO# 9896530045986 PM West Pre-Concentrator Fin Fan 90-39.2\Planning Package.xls, updated file
MR-1\Final Planning\UT External -Internal Inspection\WO# 98975000224A UT External 90-584 Subchiller Purger\Planning Package Tool.xls, updated file
Date/Time: 8/24/2010 1:30:33 PM Files updated: 2      ============================================================
Date/Time: 8/24/2010 1:40:31 PM                       ============================================================
MDA-2\Final Planning\81-053 Amine Surge Tank (Open)\Planning Package Tool.xls, updated file
Date/Time: 8/24/2010 1:40:31 PM Files updated: 1      ============================================================
Date/Time: 8/24/2010 2:00:32 PM                       ============================================================
MDI\Package Final Plan\Reflux Cleaning\WO# 989750002153 Pull-Clean Crude Column Reflux Nozzle\989750002153 (Pull & Clean Column Nozzle 90-220).xls, updated file
Date/Time: 8/24/2010 2:00:32 PM Files updated: 1      ============================================================
Date/Time: 8/24/2010 2:30:31 PM                       ============================================================
MDA-2\Final Planning\81-053 Amine Surge Tank (Open)\Planning Package Tool.xls, updated file
Date/Time: 8/24/2010 2:30:31 PM Files updated: 1      ============================================================
Date/Time: 8/24/2010 3:10:30 PM                       ============================================================
MDI\Package Final Plan\Valve Replacement\WO# 989750002149 Replace 2 670# Steam Supply Valves\Planning Package Tool.xls, updated file
Date/Time: 8/24/2010 3:10:30 PM Files updated: 1      ============================================================
Date/Time: 8/24/2010 3:40:26 PM                       ============================================================
MDA-2\Final Planning\Piping\WO# 989750003817 081-156 LS-15602 Move Up and  Install PI\WO#989750003817 ISO Template.xls, updated file
Date/Time: 8/24/2010 3:40:26 PM Files updated: 1      ============================================================
Date/Time: 8/24/2010 4:30:31 PM                       ============================================================
MDA-2\Final Planning\81-053 Amine Surge Tank (Open)\Planning Package Tool.xls, updated file
Date/Time: 8/24/2010 4:30:31 PM Files updated: 1      ============================================================
Date/Time: 8/24/2010 4:40:25 PM                       ============================================================
MDI\Package Final Plan\Fabrication\WO# 989630057094 TRACING WATER LEAK @ 1424 COLUMN PUMP\Planning Package Tool.xls, updated file
Date/Time: 8/24/2010 4:40:25 PM Files updated: 1      ============================================================
Date/Time: 8/24/2010 5:10:24 PM                       ============================================================
MDI\Package Final Plan\Fabrication\WO# 989630057094 TRACING WATER LEAK @ 1424 COLUMN PUMP\Planning Package Tool.xls, updated file
Date/Time: 8/24/2010 5:10:24 PM Files updated: 1      ============================================================
Date/Time: 8/24/2010 5:40:23 PM                       ============================================================
MDI\Package Final Plan\Fabrication\WO# 989630057094 TRACING WATER LEAK @ 1424 COLUMN PUMP\Planning Package Tool.xls, updated file
Date/Time: 8/24/2010 5:40:23 PM Files updated: 1      ============================================================
Date/Time: 8/25/2010 7:10:27 AM                       ============================================================
MDA-2\Final Planning\Piping\WO# 989750003687 081 Cond Drain Piping Change Order Check and Block Valve\DSCN2448.JPG, updated file
MDA-2\Final Planning\Piping\WO# 989750003687 081 Cond Drain Piping Change Order Check and Block Valve\DSCN2479.jpg, updated file
MDA-2\Final Planning\Piping\WO# 989750003687 081 Cond Drain Piping Change Order Check and Block Valve\Picture.doc, updated file
MDA-2\Final Planning\Piping\WO# 989750003687 081 Cond Drain Piping Change Order Check and Block Valve\Planning Package Tool.xls, updated file
MR-1\Final Planning\UT External -Internal Inspection\WO# 989750002332 PRODUCT RECEIVER  (UT) 90-054\Book1.xls, updated file
Date/Time: 8/25/2010 7:10:27 AM Files updated: 5      ============================================================
Date/Time: 8/25/2010 7:20:26 AM                       ============================================================
MDA-2\Final Planning\WO# 989750003718 Neutralization Pipe Switch Valve Order of the Check and Block Valve\DSCN2407.JPG, updated file
MDA-2\Final Planning\WO# 989750003718 Neutralization Pipe Switch Valve Order of the Check and Block Valve\DSCN2408.JPG, updated file
MDA-2\Final Planning\WO# 989750003718 Neutralization Pipe Switch Valve Order of the Check and Block Valve\DSCN2450.JPG, updated file
MDA-2\Final Planning\WO# 989750003718 Neutralization Pipe Switch Valve Order of the Check and Block Valve\Picture.doc, updated file
Date/Time: 8/25/2010 7:20:26 AM Files updated: 4      ============================================================
Date/Time: 8/25/2010 7:50:25 AM                       ============================================================
MDI\Package Final Plan\Condenser-Exchanger\WO# 989750002179 Open-Clean 90-335 Isomer Column Vent Condenser\Planning Package Tool.xls, updated file
Date/Time: 8/25/2010 7:50:25 AM Files updated: 1      ============================================================
Date/Time: 8/25/2010 8:20:24 AM                       ============================================================
MDI\Package Final Plan\PUMPS\WO# 989750002205 Pull Last Running Pump 90-514 or 90-513\989750002205 (Pull Last Running Pump 90-513 or 514).xls, updated file
Date/Time: 8/25/2010 8:20:24 AM Files updated: 1      ============================================================
Date/Time: 8/25/2010 12:30:32 PM                       ============================================================
MDA-2\Final Planning\Fabrication\WO# 989750002953 Piping 081-13A Circuit Replacement HCL Line (SHOOT PROFILE ONLY)\Planning Package Tool.xls, updated file
Date/Time: 8/25/2010 12:30:32 PM Files updated: 1      ============================================================
Date/Time: 8/25/2010 4:30:33 PM                       ============================================================
MDI\Package Final Plan\Condenser-Exchanger\WO# 989750002184 Open-Clean 90-504 Final Column Vent Scrubber\Planning Package Tool.xls, updated file
Date/Time: 8/25/2010 4:30:33 PM Files updated: 1      ============================================================
Date/Time: 8/25/2010 4:50:26 PM                       ============================================================
MDA-2\Final Planning\WO# 989750003046 Inspection MDA Intercooler\Planning Package Tool.xls, updated file
Date/Time: 8/25/2010 4:50:26 PM Files updated: 1      ============================================================
Date/Time: 8/26/2010 7:10:24 AM                       ============================================================
MR-1\Final Planning\Exchanger\WO# 989750003750 OCC 90-456 Phosgene Cooler\Planning Package Tool.xls, updated file
Date/Time: 8/26/2010 7:10:24 AM Files updated: 1      ============================================================
Is a section of what I'm dealing with, I want to return the section after the last ===Update=== part, skipping the part before the very last one.
I will have it slice up the line and put specific parts in cells, but I need to know how to make it read the text file backwards or find the last occurrence of ====update and parse the rest of the file in a way I can manipulate it.

I really appreciate your help.
 
Upvote 0
I don't know how to read a text file backwards, but try:

Code: 
Sub Test()
'   *** Change to suit ***
    Const LookFor As String = "===Updated"
    Dim FileName As String
    Dim FileNum As Integer
    Dim r As Long
    Dim ws As Worksheet
    Dim Arr() As String
    Dim Data As String
'   *** Change path and file name to suit ***
    FileName = "C:\Temp\Updates.txt"
    FileNum = FreeFile
    r = 1
    Set ws = Worksheets.Add
    Open FileName For Input As #FileNum
    Do While Not EOF(FileNum)
        Line Input #FileNum, Data
        If Data = LookFor Then
            r = 1
            ReDim Arr(1 To r)
            Arr(1) = Data
        Else
            r = r + 1
            ReDim Preserve Arr(1 To r)
            Arr(r) = Data
        End If
    Loop
    With ActiveSheet.Cells(1, 1).Resize(UBound(Arr))
            .NumberFormat = "@"
            .Value = WorksheetFunction.Transpose(Arr)
    End With
    Close #FileNum
End Sub
 
Upvote 0
Thank you, I like the way you reset the counter when it hit the same searching for text. This should get me started! I very much appreciate your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Excel VBA writing an carriage-return or empty row at the end when saving a text file
Replies
4
Views
306
Tanquen
T
J
  • Question Question
VBA for Excel used to work, doesn't now
Replies
1
Views
197
Joe4
Joe4
R
  • Solved
VBA Function to remove line spaces in an Email
Replies
2
Views
102
Ragham26
R
I
  • Question Question
Unable to Open or Copy Corrupted Excel Files on Network Drive
Replies
1
Views
84
Candyman8019
C
F
  • Solved
Replacing multiple instances of text file lines
Replies
2
Views
340
frateg8r
F

Forum statistics

Threads
1,222,629
Messages
6,167,190
Members
452,104
Latest member
jadethejade

Share this page

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