Hi,
sorry if this is a long story but hopefully it has ane easy answer.
In our office the sales team use excel invoices, the template they open gives them a unique number and when they save the invoice a copy of the invoice is saved and the information is also stored in a lookup table which is an excel spreadsheet in subdirectory called invdata.
I believe that all this system was created using the excel wizards.
The problem I originally had was that one of the staff saved a blank invoice over the top of the invdata.
In order to find my way around this I wrote a script that created a new worksheet put in the relevant headings opened up each invoice file in turn in the directory and placed the relevant values in the new sheet using the following sort of command
DBWorksheet.Range("A" & lngRow) = InvWorksheet.Range("L17")
This seemed to work a treat at populating the sheet, the problem I then had was that the template then throws up an error saying that the new excel file is the wrong structure even though all the columns seem to be identical.
To get around this I then took a copy of the despatch note db which is identical, this placed in the invoice db directory allowed them to add new records by saving their invoices. So I thought excellent I'll copy and paste all the information from the sheet I created with my script.
This however still has a problem as the excel template seems to have some sore of row counter somewhere so that if new records are added now they overwrite the records I pasted in.
I cannot look at the VB macros in excel to see what the template is doing as these seem to be password protected, even though the person here who set up the system insists none were set, so assume this is because the macros were wizard generated. - any and all feedback on this are welcome
Kieron
Start Free Trial
