Open Source Team

  • Increase font size
  • Default font size
  • Decrease font size

Automating invoice creation using a template with macros

E-mail Print

This article will show, by taking an example, how macros in templates can be used to support the administrative task of creating an invoice.


Suppose you are a service provider, who bills his customers on a monthly basis. Also, suppose in this example, that your customers expects the invoice date to be set at the last day of the previous month. The invoice period is then the previous month. We will show how, once the template has been setup, an invoice can be created by nothing more than entering the number of days performed.

Starting with a templatehttp://opensourceteam.be/administrator/index.php?option=com_content&sectionid=1&task=edit&cid[]=4

To start with this invoice, we will use a template. By using a template, you make sure that you are starting each time from a clear slate, while maintaining the same consistent look and structure in all your invoices. A pictureThe template described in this article, is stored here. The image to the right shows the start of our template. In the upper left corner, is your company logo. In the center, it contains a table that shows how the total invoice amount is calculated, based on days performed, daily rate and and VAT. How calculations in tables are done, was explained in a previous article. In this example, a daily price is charged for the services. However,the template could easily be adapted to an hourly rate. Some elements will be fairly stable over time: the daily rate and the VAT rate. We store those elements in custom properties of the template document. Custom properties of a document are also sometimes referred to as 'user fields'. They are defined in -> File -> Properties, tab: Custom.

A pictureAfter the customer properties are defined, position your cursor at the location in the document where the value should appear. To insert the value of the customer properties, choose from the menu: -> Insert -> Fields -> Other, or CTRL + F2. Select the tab DocInformation and expand the entry 'Custom'. Click 'Insert' to insert the value in the document. As said before, the only element that should be modified, when using this template is the number of days, labelled in this template as 'Quantity'. To avoid unintentional changes to other elements in the table, it is good to protect those cells.

Setting invoice date and invoice period

We are now left with the task to automatically set the invoice date, to the last day of the previous month, as well as the invoice period to the previous month. We again use customer document properties here. Such a custom document property can be inserted at the right location where the values have to appear. The initial values of those properties, which we name InvoiceDate and InvoicePeriod, will be empty. The logic of the macro will consist of checking if those properties are empty, and if yes, initializing them to the correct values. Although the task of writing macro may appear a bit daunting at first, it is really worthwhile investing some time and effort into it, since it offers such a rich functionality. The macros in this example have been developed in BASIC. Some useful links can be found at the bottom of this article. To edit your macro, or to have a look at the code, go to ->Tools -> Macros -> Organize Macros -> OpenOffice.org Basic ... This opens a dialog box from which you can startA picture. In [4] you find some more information about the organization of macros. If you have a macro that you want to re-use, it mostly not a good idea to store them in the document itself. However, in this case that is exactly what we did, because it allows us to distribute them together with the template (.ott) document. In this case I have added a module 'OpenSourceTeam' to the library 'Standard' in the template itself.

The code

The more interesting parts of the subroutines in this macro are where the customer document property (or 'user field') is read and set.
Below is the generic piece of code for reading it:

Function GetUserField(sUserFieldName As String) As String
 Dim oDocument As Object, oDocProp As Object, oUserDefProp As Object
 Dim sValue As String
 oDocument = ThisComponent
 oDocProp = oDocument.getDocumentProperties
 oUserDefProp = oDocProp.getUserDefinedProperties
 GetUserField = oUserDefProp.getPropertyValue(sUserFieldName)
End Function 'End GetUserField

And here the code for setting a customer document property:

Sub SetUserField(sUserFieldName As String, sValue As String)
 Dim oDocument As Object, oDocProp As Object, oUserDefProp As Object
 oDocument = ThisComponent
 oDocProp = oDocument.getDocumentProperties
 oUserDefProp = oDocProp.getUserDefinedProperties
 oUserDefProp.setPropertyValue(sUserFieldName, sValue)
End Sub 'End SetUserField

Invoking the macro

A pictureThe subroutine that sets both the invoice date and period, for a template in English is 'SetInvoiceData_ENU'. This routine can be invoked when the template is used by linking it to the event 'New Document'. This is the event that is raised when the template is used. To link the macro to this event, select -> Tools -> Customize. In the dialog box select the tab 'Events'.

In order to use the template, import the template in your templates. To do this, select ->File ->Templates ->Organize ... Select 'My Templates'. Select the Commands button. From the drop down list, select 'Import Template ...'. Then select the template 'MyInvoice.ott'. Before using this template, make sure that your OpenOffice security settings allow you to execute macros. This is defined in: Tools -> Options .... In the dialog box select in the left explorer view 'OpenOffice.Org'. Click on the button 'Macro Security ...'. Set the security level to 'Medium'. A picture
Now to use the template, select -> New -> Templates And Documents. In My Templates, select the 'MyInvoice' template. You will see a warning message, saying 'The document contains document macros. Macros may contain viruses. Disabling macros for the document is always safe. If you disable macros you may lose functionality provided by the document macros.' Click 'Enable Macros'. A new document will now be created, based on the template and the invoice date and period will be set. Now, all you need to do is to enter the quantity of performed days, and click on F9 to refresh the calculations.

Enjoy!

Useful links:

[1] The OpenOffice.org 3.2 BASIC Guide (PDF) provides an introduction to programming with OpenOffice.org Basic.
[2] API of the Document object: is a pointer to the place in the OpenOffice API documentation that describes the document module.
[3] OpenOffice.Org macros explained by Pitonyak, is a hands-on description of how to develop macros
[4] Getting started with macros in 3.x gives more information of the organization of macros