Itemized bills in Word

Question

I'd like to generate itemized bills for a non-profit as a Word mail merge.

Right now, the source data is stored in Excel in a pivot-table-like structure (this wasn't my idea) with two cells (owed and paid) per item type per customer. (Each customer has one row, and each item type has two columns). The existing data is in this structure, but if there's a way to automatically convert it to something better, I'm open to ideas.

I currently have a Word mail merge document generated by a Word macro that writes a Word IF field code for every pair of columns (item type). (Not all customers have all item types, so I need IF fields to exclude the ones each customer doesn't owe).

The problem is that only I know how to operate this system, and I no longer have time to generate all of the bills myself.

Ideally, I'd write my own billing app (this would also solve a number of other problems), but I don't have time for that, either.

Is there any way to (reasonably quickly) make a system to allow somewhat less technical people to do all of the billing without me?

We've looked into QuickBooks, but it cannot import the existing transactions.

Answer 1

Here is one solution I've thought of:

Manipulate the Excel data into a non-pivoted form with one row per transaction (per item per customer, or per pair of cells in the current structure). I actually already did this in a failed attempt to import the transactions to QuickBooks.

Each row would turn into one item in the bill (as opposed the the current, pivoted, form where each row turns into an entire page with many items)

Then, make a Word merge document that checks whether the current row is the first row for a different customer, and, if it is, include the entire text of the bill (as opposed to the items) in two (very large) IF field codes.

All of the rows would generate an item in the bill.
Each customer's last row would follow that with the second half of the text for the old customer (the part after the items).
Each customer's first row would precede it with the first half of the text for the new customer (the part before the items).
Two IFs are necessary to handle the first and last customer.

I would have to add a formula column to the spreadsheet that tells me whether to insert the prefix, the suffix, or neither, as that's impossible to do in Word. (You can't peek the next record)

The merge generator would still be a complicated monstrosity, but it would never need to change; new item types would be picked up from the source data. (Unlike the current merge generator which requires a new macro-generated field code for each new item type). Even so, I'd probably make a macro to generate it in case the texts change. (Editing texts inside field codes is very annoying).

Answer 2

Both Word and Excel have VBA built-in and if you use these, then you are not limited in what you can do. This type of application really should be driven from a database (Access) which would generate itemized bills, add a preamble, and a bottom total and miscellaneous after the itemization.

However, if you need to stick with the Excel data, then think of Excel as the database, write your VBA code in Excel to produce the bills, and then either assemble the word document in Excel (VBA) or place it in a worksheet format that only requires trivial work from a Word template to pull it all in.

Answer 3

As is apparent from some of my more recent questions, I ended up finding the time to write my own billing system.