Return next value after string Excel

Hi,

I have a bunch of text files which are ascii copies of invoices taken from an old mainframe system which have no delimiter, example:

Bill Number: 12345678 Customer name : MR JOHN SMITH Communication Name :
Customer address: 123 MADEUP ROAD and Address
Town: MADEUPCITY
Postcode: AB10 1DF
Effective Date: 19/03/98 Another System ID : 1A2L3X Recurring Charge ID :
Bill Status: C Original Value: 17.00 Payments Made:
Vat: 2.97 Value:
Total: 19.97
Added expenses:
Line No : 1
Description: DESCRIPTION OF THE REASON FOR THIS BILL. BLAH BLAH BLAH BLAH DESCRIPTION OF THE REASON FOR THIS BILL. BLAH BLAH BLAH BLAH DESCRIPTION OF THE REASON FOR THIS BILL. BLAH BLAH BLAH BLAH FILE REF. 3BL24X
Value : 17.00
VAT : 2.97
Total : 19.97
DATE DESCRIPTION CHARGES PAYMENTS BALANCE
-------- -------------------------------- ---------- ---------- ----------
19/03/98 Original charge 19.97 0.00 19.97
31/03/98 Charge Cancellation -19.97 0.00 0.00

What i want to do is find a method to extract the bill number, the total value and the dates, description, charge, payments, balance into a bunch of columns and rows.

I'm presuming the only real method is to use a method of searching for a string e.g. 'Original Charge' and storing the next values into an array and putting them into cells etc.

Whats the best way of doing this?

Cheers
eyeisystemsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresident and Btrieve GuruCommented:
You added vb as a tag; if it were me, I would create a simple VBScript process to do this. One loop to read through the entire list of files, and a routine to parse each one. When you find the key word, grab the next value, and you can save that value to a variable. Finally, when you have the values you want, send them out into a csv file and you're done.  There are plenty of examples of each of these steps to be found in a quick Web search to get started.
aikimarkCommented:
I named a couple of test files with an INV file extension and put them in my downloads folder.  I used intrinsic I/O methods.  You might need to use FileSystemObject if the files aren't CrLf delimited.
Sub Q_28857979()
    Dim oRE As Object
    Dim oRE_Detail As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim lngSM As Long
    Const cPath = "c:\users\aikimark\downloads\"
    Dim strFilename As String
    Dim strData As String
    Dim intFN As Integer
    Dim rngTgt As Range
    Dim wks As Worksheet
    
    Set wks = ActiveSheet
    Set rngTgt = wks.Range("A2")

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "Bill Number:\s*(\S*)\s(?:.|\n)*?Effective Date:\s*(\S*)\s(?:.|\n)*?Total :\s*(\S*)\s(?:.|\n)*?----------\r\n((?:.|\n)*)$"
    
    Set oRE_Detail = CreateObject("vbscript.regexp")
    oRE_Detail.Global = True
    oRE_Detail.Pattern = "(\d\d/\d\d/\d\d)\s*(.*?)\s*(-?\d{1,}\.\d\d)\s*(-?\d{1,}\.\d\d)\s*(-?\d{1,}\.\d\d)\s*"
    
    strFilename = Dir(cPath & "*.inv")
    Do Until Len(strFilename) = 0
        intFN = FreeFile
        Open cPath & strFilename For Input As #intFN
        strData = Input(LOF(intFN), intFN)
        Close #intFN
        
        If oRE.test(strData) Then
            Set oMatches = oRE.Execute(strData)
            Set oM = oMatches(0)
            For lngSM = 0 To 2
                rngTgt.Offset(0, lngSM).Value = oM.submatches(lngSM)
            Next
            Set oMatches = oRE_Detail.Execute(oM.submatches(3))
            For Each oM In oMatches
                With oM
                    For lngSM = 0 To .submatches.Count - 1
                        rngTgt.Offset(0, 3 + lngSM).Value = oM.submatches(lngSM)
                    Next
                    Set rngTgt = rngTgt.Offset(1)
                End With
            Next
            wks.Range(rngTgt.End(xlUp), rngTgt.Offset(-1, 2)).FillDown
        End If
        strFilename = Dir()
    Loop
End Sub

Open in new window

eyeisystemsAuthor Commented:
Hi Aikimark,

I think you're on to something! However when i run it i can see it going through the motions if i put a breakpoint in but it doesnt seem to write anything to the cells. As if its not matching?

I've attached an anonymised example txt file so you can see on your side

Any ideas?

Cheers
12345678.txt
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

aikimarkCommented:
Please post one or two of the files.  I based my comment/code on the sample data you posted.  If your actual data does not closely resemble the sample, it will not read or parse correctly.
eyeisystemsAuthor Commented:
Hi Aikimark, there should be a text file with my comment at 04:20, can you see that? If not i can post one again.

Cheers

Brendan
aikimarkCommented:
Ah.  I missed that.  Let me check it out.
aikimarkCommented:
Sub Q_28857979()
    Dim oRE As Object
    Dim oRE_Detail As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim lngSM As Long
    Const cPath = "c:\users\aikimark\downloads\"
    Dim strFilename As String
    Dim strData As String
    Dim rngTgt As Range
    Dim wks As Worksheet
    
    Dim oFS, oTS, oFile
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Set oFS = CreateObject("scripting.filesystemobject")
    
    Set wks = ActiveSheet
    Set rngTgt = wks.Range("A2")

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "Bill Number:\s*(\S*)\s(?:.|\n)*?Effective Date:\s*(\S*)\s(?:.|\n)*?Total:\s*(\S*)\n(?:.|\n)*?----------\n((?:.|\n)*)$"
    
    Set oRE_Detail = CreateObject("vbscript.regexp")
    oRE_Detail.Global = True
    oRE_Detail.Pattern = "(\d\d/\d\d/\d\d)\s*(.*?)\s*(-?\d{1,}\.\d\d)\s*(-?\d{1,}\.\d\d)\s*(-?\d{1,}\.\d\d)\s*"
    
    For Each oFile In oFS.getfolder(cPath).Files
        If oFile.Name Like "*.inv" Then
            Set oTS = oFS.OpenTextFile(cPath & oFile.Name, ForReading, True, TristateFalse)
            strData = oTS.readall
            oTS.Close
            
            If oRE.test(strData) Then
                Set oMatches = oRE.Execute(strData)
                Set oM = oMatches(0)
                For lngSM = 0 To 2
                    rngTgt.Offset(0, lngSM).Value = oM.submatches(lngSM)
                Next
                Set oMatches = oRE_Detail.Execute(oM.submatches(3))
                For Each oM In oMatches
                    With oM
                        For lngSM = 0 To .submatches.Count - 1
                            rngTgt.Offset(0, 3 + lngSM).Value = oM.submatches(lngSM)
                        Next
                        Set rngTgt = rngTgt.Offset(1)
                    End With
                Next
                wks.Range(rngTgt.End(xlUp), rngTgt.Offset(-1, 2)).FillDown
            End If
        End If
    Next
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eyeisystemsAuthor Commented:
You sir, are a hero! Thats going to save me a huge amount of time, many thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.