A practical example using a Word template

 

Consider a small business that uses Microsoft Word to create paper-based invoices and a Microsoft Excel spreadsheet to store financial details associated with each invoice.

The process of manually creating an Invoice probably goes something like this:

1. Open the Excel spreadsheet to obtain the next Invoice number in sequence.
2. Open Word and create a blank Invoice, either from scratch or based on an Invoice template.
3. Put the Invoice number on the Invoice.
4. Enter the Customer details into the appropriate part of the Invoice.
5. Type in the Date Supplied and Invoice Date.
6. Enter the description of work performed; this may consist of more than one item.
7. Use a calculator to add up the prices (with and without VAT).
8. Save the Invoice.
9. Copy the financial details of the Invoice into the next entry in the spreadsheet.
10. Save the spreadsheet.

A tedious, ten-step, error-prone process.

 

Automate this process using Word and Excel

We could create an 'application' using a Word template and an Excel Spreadsheet that would reduce this process to the following three simple steps:

1. Open Word, and click on File » New... and then select the invoice template. A three-part data entry form is displayed, overlaying the blank Invoice
2. Type the invoice details into the data entry form.
3. Click on a button marked Create Invoice - the new Invoice is automatically created and its financial details are stored in the spreadsheet.

 

The Invoice 'application'

There are three features to this application:

• A Word template file (invoice.dot) containing a blank Invoice. You may already have one of these, or we could create it for you, laid out to your specification. The template will contain textboxes where data is to appear.
• A popup data entry form (and associated VBA macros) via which you enter the details that will appear on the Invoice. These are stored in the template.
• An Excel spreadsheet used to store customer details, dates and prices for Invoices that have been saved and which generates the next Invoice number in sequence when a new Invoice is created.

 

Data Entry Form

The only feature of the application that you, the User, has to interact with is the data entry form. This will now be described in detail:

The first part of the data entry form - Order Details - is where you to enter the customer name and address, supply date, invoice date and order reference (if necessary). See below

 

The second part of the data entry form - Work and Prices - is where you to enter details of the work done and prices charged, see below:

Note that, in this example:

• You can enter a general description of the work performed
• You can enter up to four items of work and their corresponding prices
• There is no need to calculate and enter any VAT details (this will be done automatically later)
• There is no need to calculate running totals (this too will be done automatically later)
• Only valid currency amounts are accepted in the Price boxes

 

The third part of the data entry form - Notes - is where you enter notes, if required. See below:

 

At this point you can review all three parts of the data entry form (by clicking on the appropriate tab) and make changes if necessary - nothing has yet been written on the Invoice.

• If you've made a mess of it, click Clear Fields to clear all the entries and start over again.
• If there's an unexpected problem and you find that you don't want to create the Invoice after all, click Cancel and the data entry form and the new Word document will be closed; the Invoice will not be created. The Excel spreadsheet will not be opened.
• However, if you want to go ahead and generate the new Invoice, click on Create Invoice.

 

The resulting Invoice

All the details that you entered into the data entry form are automatically added to the new Word document and formatted according to the template. See below:

 

Also the corresponding entry is updated automatically in the Invoices Excel spreadsheet (see below).

Note that:

• The Excel spreadsheet is opened and the Invoice number is generated automatically to be the next one in the sequence.
• All the details entered on the data entry form appear in the appropriate locations on the Invoice.
• Only those items that were entered into the Work and Prices part of the form appear on the Invoice (two in this case) - there are no blank lines.
• Similarly, only the text that is entered into the Notes part of the form appears on the Invoice (lead-in text and two bullet points in this case). You don't have to enter anything here at all if it's not needed.
• Total Price, VAT and amount due are calculated automatically and written to the Invoice
• The new Invoice is Saved but it remains open in Word to allow you to check it and print it as necessary.
• Once the Invoice has been created, the financial details are automatically written to the Excel spreadsheet which is then Saved and Closed.

 

Restrictions:

The template must be saved in the default Word templates folder. This is necessary so that it can be selected from the list of Word template files that is presented when you click File » New....

It's up to you where you store the Excel Invoices spreadsheet and the saved Invoice documents. The application will automatically ask for details of these locations on first use, or if you subsequently move them.

 

Enhancements:

This is just a simple example to illustrate what is possible. Many enhancements are possible such as converting the newly-created Invoice to PDF format and automatically emailing it to Customer.

It would even be possible to have an Excel file holding a 'database' of customers and their details. The customer details would then not need to be entered manually onto the first part of the data entry form - there would be a dropdown list of all the customers (generated from the database) from which you would choose the appropriate one and the corresponding address details would be filled in automatically.

 

Return to the Business Documents page.