Creating an Invoice in Word using Data in Excel

[mew0717]

Hello,

I've tried searching this form (and google) and I haven't quite found what I'm looking for so i figured I would ask a question. Essentially, I need to take a bunch of information that I have in excel and populate a Word Document to create an invoice.

The way that I was thinking about doing this would be by creating a template in word and using bookmarks. Then I would use VBA to populate those bookmarks based on the person (publisher in this case) i was invoicing.

I'm having a couple of issues logistically though. I'm not sure how to attach the sample invoice, so i'll do my best to write them out and if i figure out how to attach a doc by the end of this i will include a sample so you see what I'm talking about.

Issue 1: Each Invoice form has the publishers Name and Address listed at the top of the form. The problem is, the information i'm pulling the number of sales (and money we owe them) is from a different spreadsheet every month and wouldn't contain this address information on it as it is an aggregate spreadsheet of ALL publishers numbers. Would it be possible to create an initial template for each publisher and then have the rest of the information be populated with the data from the CSV that has their sale information. I could create a seperate database in excel with each publishers address and name but then it might get difficult to match these up. Any Ideas on how to automate that, as we have 100's of publishers and invoices are done every month.

Issue 2: Bookmarks seem like a nice way to use VBA to replace their values with excel data (in theory, i've only read about it and never attemped)... However, in this particular invoice form, I have to create a seperate listing in the description field for each product a publisher sold (including how many they sold and how much money they are owed). Some publishers may have sold 5-10 different products, while some may have only sold 1 or 2. How do i get word to insert a new bookmark (or something) based on the number of products a person has sold.

Attaching a sample document showing the invoice form would really be helpful.. If someone is interested i can do that.

Looking forward to your suggestions.

Thanks!

Matt

 

[Trevor G]

Matt why not keep this in Excel, you can remove the grid lines and create the document and then just pull the values to the rows through VLookups and have sub totals and gross totals. Run a simple macro to give you a new sheet when needed from a template.

I would just use Excel rather than using Word and Book marks.

At another level you could use Access, then importing the CSV files can be automated and once the structure is write you simple run the queries and the report section would be the invoice.

Your Excel skills can be used in Access.

 

[jansy]

Hello,
there is a free online app here generating invoices from excel: http://invoicequest.com/Default.aspx maybe that can help you.
You can download/save them in Word and Pdf formats.

Best regards,
Jan

 

[shyy]

Some ideas.. For each row make sure the first columns are 1,2,3,4 etc.. then you can do a vlookup to pull the data. with VBA create a loop so you can cycle through each invoice.