Data Sort and Manipulation

[timitfc]

Hi All,

I'm using a third party database software which handles jobs, orders, etc but also does invoicing.

Now this is where I would like some help, attached below is a sample of the invoice is stored.

Currently the invoice is printed out in courier font and looks exactly like it does in the InvoiceText field. Now the problem that I want to produce my own invoices from this, but the only place where the Sch No, with the description, quantity and price is stored is in this InvoiceText Field.

To make matters more annoying Schedule number doesn't always start on the same line, sometimes the description runs over two lines and the gap between line numbers doesn't always increase by 1.

What is the best way of getting this data? Once I have got to the data then I need to split it up, probably using Instr (unless anyone else knows of a better way) to then store this in a more manageable & usable way.

I would appreciate any help you may be able to give me!

 

[Norie]

Do you have this data in Access or Excel?

This sort of thing would probably be easier to do in Excel, and you might not even get the required results in Access by looping through a recordset.

Wherever you are doing it you should probably look for SCHED No. Description and NETT TOTAL#.

Looking for a symbol like _ might not be such a good idea.

Here's my first attempt, which isn't perfect, it gets all the data but doesn't parse it in anyway and it doesn't deal with the items with more than one line.

Option Explicit
 
Sub test()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rngInvStart As Range
Dim rngInvNo As Range
Dim rngDst As Range
Dim lngItems As Long
Dim I As Long
Dim LastRow As Long
 
    Set wsSrc = Worksheets("qryInvText")
    Set rngInvNo = wsSrc.Range("A1")
    Set wsDst = Worksheets.Add
    Set rngDst = wsDst.Range("A1")

    While rngInvNo.Value <> ""

        If rngInvNo.Offset(, 4) Like "*SCHED.No.*" Then
 
            Set rngInvStart = rngInvNo.Offset(2, 4)
 
            Do

                rngDst.Value = rngInvNo.Value
                
                rngDst.Offset(, 1) = rngInvStart
 
                Set rngInvStart = rngInvStart.Offset(1)
 
                Set rngDst = rngDst.Offset(1)
 
            Loop Until rngInvStart Like "*NETT*"
 
        End If
 
        Set rngInvNo = rngInvNo.Offset(1)
 
    Wend
    
End Sub