LibreOffice: Creating invoices using Writer
From the very start I have been using OpenOffice (now LibreOffice) for creating my own invoices. The first thing you have to decide is whether to create your invoice with Calc or with Writer. I decided to use Writer, as it is harder to create a proper layout with a spreadsheet document (especially when using window envelopes) that comes out well on different printers, but that's up to one's preference.
However for the calculation of VAT and totals I use an embedded spreadsheet object. Everything else (eg. logo, company info, customer info, invoice number, invoice date, conditions) is part of the layout of the invoice in Writer.
There are two items on the invoice in Writer that I have automated:
- Invoice number
- Due date
The invoices are named invoice-YYYY-MM-#NR-customer.odt and stored in a single directory so it is easy to see what the next invoice number should be. For new invoices I always start from an existing invoice and modify the customer info if needed.
The invoice number on the invoice is automatically extracted from the filename in the format YYYY/MM/#NR. This helps to avoid an old invoice number appears on a new invoice.
The due date is automatically set to be the invoice date plus 30 days so that you only have to add/update the invoice date. I implemented this recently and it was not that easy to do with BASIC macro's and UNO.
So how was this implemented ? The easiest solution I found is to work with custom user fields (Insert > Fields > Variables > User field) as they can easily be named and modified from UNO. I created InvoiceNumber (string), InvoiceDate (Date format) and DueDate (Date format).
Then use the following BASIC macro and attach the various 'event-handlers' of your document to the below functions. (Tools > Customize > Events)
REM For testing purposes Sub Main updateInvoiceNumber() updateDueDate() End Sub REM Event handler when opening a document Sub _Open updateInvoiceNumber() End Sub REM Event handler when a document is being saved Sub _Save updateDueDate() End Sub REM Event handler when a document is modified Sub _Modified updateDueDate() End Sub REM Update a user field (DueDate) with the invoice date + 30 days Sub updateDueDate DueDate = getDueDate() DueDateField = thisComponent.getTextFieldMasters().getByName( "com.sun.star.text.fieldmaster.User.DueDate" ) If (DueDateField.Value <> DueDate) Then DueDateField.Value = DueDate thisComponent.TextFields.refresh() EndIf End Sub REM Retrieve the user field (InvoiceDate) from the document and make DueDate = InvoiceDate + 30 Function getDueDate If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then GlobalScope.BasicLibraries.LoadLibrary("Tools") End If InvoiceDateField = thisComponent.getTextFieldMasters().getByName( "com.sun.star.text.fieldmaster.User.InvoiceDate" ) getDueDate = InvoiceDateField.Value + 30 End Function REM Update a user field (InvoiceNumber) with a slice of the document name Sub updateInvoiceNumber InvoiceNumber = getInvoiceNumber() InvoiceNumberField = thisComponent.getTextFieldMasters().getByName( "com.sun.star.text.fieldmaster.User.InvoiceNumber" ) If (InvoiceNumberField.Content <> InvoiceNumber) Then InvoiceNumberField.Content = InvoiceNumber thisComponent.TextFields.refresh() End If End Sub REM Extract the invoice number from the document name (slice 9-20) Function getInvoiceNumber If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then GlobalScope.BasicLibraries.LoadLibrary("Tools") End If getInvoiceNumber = FileNameoutofPath(thisComponent.getURL(), "/") getInvoiceNumber = Mid(getInvoiceNumber, 9, 11) getInvoiceNumber = ReplaceStr(getInvoiceNumber, "-", "/") End Function REM Return a string with str1 replaced with str2 Function ReplaceStr(myString As String, str1 As String, str2 As String) ReplaceStr = join(split(myString, str1), str2) End Function
How do you make your invoices ? Any feedback regarding my BASIC programming ? :-) I am interested to know.