read data from pdf file and insert into excel spreadsheet

[crystal990]

My department receives a daily summary report in pdf format of number of products pulled (each day). We have been manually entering the number of each product in a monthly excel spreadsheet. We have nearly 500 products and naturally people enter the totals on the wrong line,etc, and it screws up our inventory report. I would like to use vba to automate most if not all of the steps. I'm slightly familiar with vba but I'm not sure how best to approach this.

I saved the pdf file as a text file and copied it into excel. The pdf file looks like this when I copy it into excel and define the delimiters,etc. Each page has a header of report name and date, page #; so the data in excel is separated by the header.

Daily Summary

Date: 8/28/2015 3:12:22 PM Page 1 of 5


Product Description Component Qty
ADB7 Cebright Express ADB7 GSA DOD FGSA1PLS454 10
GN83 XYZ - GN83 Standard PAY FEXGA5PL665 6
W008 Get Go 1 Plastic FGTG1PLS600 1
I9NC Richards Corp GUL1 Gulf FUL1PLS997 2
SEPC Luna Aviation GUL2 Gulf Business FEGUL2PLS403 126
PJK7 XYZ - GULF COMMERCIAL MILL FGUL3LS838 7
ZF7Y Automotive Express ZF7Y Hayden FHAY1PLS005 2
ASV2 ABCD Company ASV2 High Noon FXHI1PS234 143
JOL8 XYZ - Holiday Oil FGXHOLPS323 34


From this data all I need is the 4 character product name and the number (total) at the end of each line.

What I would like to accomplish is:
- acquire the daily summary data (date, product name, and total);
- in the monthly spreadsheet, locate correct date;
- locate product in col 1 titled 'type';
- enter amount for each product in column titled 'pick list';

also, create a backup of the monthly spreadsheet (just in case)

I'm stuck on how I even want to proceed. I'm open to any ideas and willing to try any other suggestions.

Thanks in advance.