Word documents need to be created from data in excel files using VBA.
Sales records are stored in an excel file. Each record has an payment reference XXXX, and the program needs to find all the rows with a given reference number, and produce a word document with the data from the rows with the given reference number.
I've attached sample data from the excel file, and an example of the word document that should be generated for reference 9245 (payment reference is stored in column AA 'GP PO' in the excel file). The reference is for a payment to docB, a payment reference is always to one doctor code in column C.
In the excel data, each row can contain up to four products,with their rates and quantities (columns I to T). Each product should appear on a seperate line in the word document.
A word template file will be provided with the required layout and style.
There are two rows with payment reference 9245 (9 and 12). The first row with payment reference 9245 has one product and the second row with the reference has two products, so a total of three lines are produced in the word document. The rate is given in columns I, L, O and R for the four products, and the quantities are given in K, N Q and T respectively. The first product column I is type 'Surgery', second product column L is 'Visits'. If third and fourth products exist, they are type 'Other'.
Lookup tables will be provided in another excel file to replace DocB with name+address and SurC,SurB with real name.
The Date for each row is obtained from columns D and E (not F - description, as this may not be entered for some records). D and E are Yearweek and Day, for example in row 9 this is 1034 and 3, which means year 2010 week 34, day 3 = Wednesday 25th August.
Also the program should have the ability when the user requests to produce several invoices XXXX to XXXX or XXXX,XXXX and XXXX (like when selecting page numbers to print). Each payment file should be saved to a seperate word document named DocX-XXXX.doc. A bonus would be if a pdf file could be automatically be generated using ghostscript of something similar for each document.