Creating an Invoice in Word using Data in Excel

mew0717

New Member
Joined
Feb 17, 2006
Messages
18
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,672
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,719
Messages
5,524,460
Members
409,582
Latest member
riibuildersinc

This Week's Hot Topics

Top