How would i extract certain information from a spreadsheet ?

[Paul Moran]

Hi All,

I am in need of a way to 'scan' information in a spreadsheet and extract certain text patterns and the like, and then reformat into desired specs ...

Is this a relativley easy or hard thing to do, given that i am quite inexperienced with complex VB ...

Thanks for your help guys n girls, much appreciated!!

Paul

[RobDog888]

Welcome to the Forums.

What type of text patterns? Are you wanting/needing to do this from VB 6?

[Paul Moran]

the text patterns vary ... heres the situation:

I get differing invoices from different clients, they are always in the same format (from each relevant client).

What i need to do is extract specific info like:

- Invoice #
- Description of goods
- Country of origin
etc

So its 99% AplhaNumeric extraction.

I would ideally like to keep it all in excel, as this will allow for further use and development with others in the office (that are not software literate) ...however Excel will alllow a bit of futreproofing for the masses

Thanks for your reply mate and words of welcome

Hear from you soon

Thanks again

Paul

[RobDog888]

Well to parse things like an Invoice # and other stuff its best if its in a constant position/cell. If its not then we will need to write something to search for some identifier that will tell us where the starting point is.

Can you make a mockup of how the sheets look?

[Paul Moran]

because i know exactly what i need from each different supplier and i know that the search criteris (alphanumeric string) never changes for each...what i need is some form of recursive statement that 'scans' a document (say pasted into a spreadhseet) and extract info based upon a 'found' item and then extract based upon each 'find'.

By using variables in the recursive search i could use the one generic search function to accomodate every invoice by passing in the specific details of the relevant supplier etc ...

I hope this is making sense...

[RobDog888]

Yes, this is what I am after. Finding the identifying maker that signifies where (what cell) to get the info from.

There is a .Find method of the range object but is the "Invoice #" always the same in all and is the needed data in the next cell below or to the right? This is why a small textual representation of what we are looking at will be helpful.

[Paul Moran]

this is a mock up:

Sales Invoice No.   : CIH/102-04
                  Vessel / Voyage No. : MV.BALTREUM TREDER V.444
                  Departure           : JAKARTA
ETD : 27/04/2006
                  Arrival             : SYDNEY
ETA : 18/05/2006
                  Your Reference      : 31367, 31382, 30765, 30712,
31462, 32046, SAMPLE ALBASIA
                  Our Reference No.   : 2826-18, 2827-18, 2884-15,
2884-16, 2884-17, 2885-6, 2885-7, 2885-8, 2885-9
                                        2885-10, 2885-11, 2893-3, 2893-4, 2955-2, 2955-3, 2977-1
                  Invoice Print No.   : 7336, 7337, 7338, 7339, 7340,
7341, 7342, 7343, 7344, 7345, 7351, 7346, 7347
                                        7348, 7349, 7350
                  Description of Goods:
                  Note / Instructions : LOADING DATE : 25/04/06, PEB NO
: 192296



                  MIX MERANTI FINGER JOINT, MIX MERANTI E2E FOR DOOR KOMPONEN,MERANTI DOOR JAMB PROFILE, MERANTI
                  DECORATIVE FRAME, MERANTIDECORATIVE MOULDING, MERANTI DOORS, MERBAU DOORS

               ------------------------------------------------------------------------
-------------------------------
                  Product           Height   Width    Thick
Volume    Unit     U/Price          Total Price
                  Code      Design    (mm)    (mm)     (mm)   Quantity
(cbm)   of Sale    AUD    Discount    FOB AUD
 
------------------------------------------------------------------------
-------------------------------
                  DSSC       1        2052      29       28     1260.0
2.099     EA        2.01     0.00     2,532.60

                  DSSC       1        2450      29       28     1188.0
2.363     EA        2.40     0.00     2,851.20

                  OTHC       SSF       770      29       28     1260.0
0.788                                     0.00

                  SSF        1         570      29       28     4064.0
1.881     EA        0.30     0.00     1,219.20

                  SSF        1         670      29       28    17820.0
9.695     EA        0.35     0.00     6,237.00

 
CONTINUED ON PAGE  2

this is an example of the file ....

Thanks a bunch for your help mate!!!!!!!!!!!!!!!!

[Paul Moran]

yeah, i looked at the .find attribute in excel as a function, however, it seems like such a 'brute force' way to do it ....

but hey, im open to all suggestions

[RobDog888]

"Sales Invoice No. :"
"ETD :"
"ETA :"
etc. are the identifiers. We will need to perform a search using each one as the rows may differ on the number of rows inbetween each identifier.

Is the colon ":" actual data or is it the representation of a vertical line or cell limit?

[Paul Moran]

its all actual data exactly as it is received (for this supplier) ...even the same unformatted mess

[RobDog888]

I understand. I made a sample of an Excel Workbook. Is the data formatted correctly? Can you correct and fill in a bit more?

[Paul Moran]

yup ...thats looking good...now the other stuff that needed

- product
- unit
- unit price
- volume
- total price

for each line

[Paul Moran]

hey mate, im finishing work now ....hopefully ill check this post tonight when i get home - thanks again!

[RobDog888]

Something like this will find the "Invoice No." and read the cell next to it and set sheet2 cell A1 with its value. This is the technique you will need to use for each of your search items needed.

Workbooks(1).Sheets(1).Activate
Workbooks(1).Sheets(1).Cells.Find(What:="Sales Invoice No.", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
 
Workbooks(1).Sheets(2).Cells(1, 1).Value = Workbooks(1).Sheets(1).Cells(ActiveCell.Row, ActiveCell.Column + 1).Value2