Export data to existing Excel invoice file to specific cells (1 Viewer)

[dlambert]

Hello All,
I have an existing Excel invoice template that i would like to integrate with an Access database.
In the database I have various fields for client information, and queries to get the data i want to invoice.
I am looking for recommendations of how to link the two together, here are a few options i thought of:

1- Export data to basic excel table using standard Access functionality then use scripts from Excel to get the data from the exported excel onto my invoice file?
2- Get everything laid out nicely in a Report then export that to Excel? could i still have active formulas in the fields etc?
3- Script in Access that generates new Excel document based on my template and automatically fills in the data?
4- (I am completely open to suggestions?)

One important thing is i need to be able to have a fully functional (equations etc) Excel spreadsheet after the "export" (whichever way it ends up happening), as i need to be able to do modifications/additions to the information on the invoice.

Any advice would be much appreciated.
Thanks in advance!

 

[arnelgp]

you're just adding complication to your app.
either use Access or Excel.

 

[Minty]

I'm with Arnelgp.
Having created a couple of invoicing based apps, do the whole thing in Access (create a report that is your invoice), and do all your other calculations /modifications in access.
I'm intrigued by these other "modifications"? What are they and why do you think they need to be done in excel?

 

[dlambert]

Thanks for your responses,
Taking them into consideration and doing further research i am now going as you advise towards doing it all within Access.
My only concern is as i described before the ability to freely modify the invoiced information, let me explain a bit more.
The company provides both supply of materials and services for installation, troubleshooting, engineering, etc. The employees input data (client, job number, description of works, hours spent) into time-sheets (that information ends up in the database), and it often is the case that the information needs to be modified at the point of invoicing.

For example:
-There might me multiple entries of similar data over multiple days by different employees that we would like to invoice as a single line item
-There might be entries where the description is something along the lines of "Online research for what materials is best to use in this project" - 4 hours , or "Travel to workshop to pick up materials" - 2 hours, and we know that this particular client will not like to see this on his invoice so we need to be able to modify the description to for example "Project Management and procurement", or to move those invoice-able hours it to another job number entirely, bit WITHOUT changing the original data entries in the database (only modify in the invoice document).

So ideally i would have to way to use a query to chose the database information i want to put into the invoice document, but then this would only be used as a base from which i can freely modify all aspects as if they were in a simple spreadsheet, whilst keeping the basic calculations expected of an invoice document (quantity * price = total for each row then a sum of all the totals, etc)

And i of course need this to be saved so i can get back to it any time in the future to see what was invoiced in a particular invoice number (as i would with a load of Excel spreadsheets in a folder).

Is this something that can be accomplished in Access? Since my original post i have seen an Access based program that implemented this but i have no idea how this flexibility would be achieved...

Once again, thankyou for your valuable advice, much appreciated

 

[Minty]

Yes - In a "very view from the top" description, you would take your timesheet data and create invoice lines with it, but as a "prepared but not ready to invoice" status.
You would then have the ability to edit those lines, as you see fit, delete, add etc. then submit them as an invoice at which point they are a fixed record, and can't be changed.

I would probably also update the relevant source timesheet lines with the invoice number, as an audit trail so you know they were covered, even if not directly shown on the invoice.

 

[The_Doc_Man]

Having worked with some systems for time/action invoicing with the Navy, you need to consider that there is a "preliminary" status and a "submitted" status. If any of this has a formal auditing procedure, there will come a time when you cannot be permitted to just walk in and change history. There can be formal ways to amend a submission, but if there is any fiduciary responsibility involved, be clear in whatever you design that there is a point of no return.