[libreoffice-users] Invoice automation using macros
Hi, I have quite simple LibreOffice invoices, one file per invoice. There are 2 items in each invoice I would like to make dynamic. 1. Invoice number 2. Due date For the invoice number, I created a user field InvoiceNumber and a macro UpdateInvoiceNumber, which automatically extracts the invoice number from the filename and updates the field when the file is opened. This works great as I simply can copy my invoice to the new name (invoice--MM-#NR-company) and the document is updated automatically with /MM/#NR. Now for the due date its more simple, but I cannot make it work. I used to have 2 fields, invoice date and due date. Both are fixed date fields, the second is 30 days off. To update them, I have to open the new invoice copy, double-click the invoice date field, disable the 'fixed' (so that is shows today's date), then make it fixed again, so it doesn't change the next time I may edit it. Repeat the same procedure for the due date fields. (twice, because I repeat the due date at the end in bold) Now, what I intended to do was to create another user field of the due date and automatically update both with the date set in the invoice date field (which remains a fixed date) and add 30 days. But the problem is that, contrary to user fields, date fields cannot be referenced in Basic (likely because they do not have a name). Or at least I cannot find it anywhere. Below is the code I am using in LibreOffice to update the InvoiceNumber. Can someone tell me how I can get the value of a (fixed) Date field ? Or is there an easier way to update dates, and manipulate them elsewhere ? Sub Main UpdateInvoiceNumber() RemUpdateDueDate() End Sub Sub UpdateDueDate Rem Don't know how to get to the invoice date fixed date field in the document InvoiceDateField = thisComponent.getTextFieldMasters().getByName( com.sun.star.text.fieldmaster.InvoiceDate ) end Sub sub UpdateInvoiceNumber InvoiceNumberField = thisComponent.getTextFieldMasters().getByName( com.sun.star.text.fieldmaster.User.InvoiceNumber ) InvoiceNumberField.Content = InvoiceNumber() disp = createUnoService(com.sun.star.frame.DispatchHelper) disp.executeDispatch(thisComponent.CurrentController.Frame, .uno:UpdateFields, , 0, Array()) end Sub Function InvoiceNumber InvoiceNumber = ERROR If (Not GlobalScope.BasicLibraries.isLibraryLoaded(Tools)) Then GlobalScope.BasicLibraries.LoadLibrary(Tools) End If InvoiceNumber = FileNameoutofPath(thisComponent.getURL(), /) InvoiceNumber = Mid(InvoiceNumber, 9, 11) InvoiceNumber = ReplaceStr(InvoiceNumber, -, /) End Function Function ReplaceStr(myString As String, str1 As String, str2 As String) ReplaceStr = join(split(myString, str1), str2) End Function PS Is there an easier way to look at the document object model than using a watch in your macro editor ? Preferably something to search. That would be so useful ! Thanks for your insights ! -- -- dag wieers, d...@wieers.com, http://dag.wieers.com/ -- dagit linux solutions, i...@dagit.net, http://dagit.net/ [Any errors in spelling, tact or fact are transmission errors] -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Invoice automation using macros
On Mon, 4 Apr 2011, Dag Wieers wrote: But the problem is that, contrary to user fields, date fields cannot be referenced in Basic (likely because they do not have a name). Or at least I cannot find it anywhere. With some help from the OpenOffice forums I solved this by not using fixed date fields, but rather using user fields (variables) with a Date format. The downside is that editing them is editing the number of days since Epoch (although it does accept a 2011.04.04 string). To use the below code as macro in your documents, simply create three user fields named InvoiceNumber (string), InvoiceDate (Date formatted) and DueDate (Date formatted), and use a filename that matches invoice-2001/03/011-customer.odt (year/month/number) or simply modify the code to do what you like ;-) And then link the below event-handlers to their respective event from 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 Hope this is useful to you. -- -- dag wieers, d...@wieers.com, http://dag.wieers.com/ -- dagit linux solutions, i...@dagit.net, http://dagit.net/ [Any errors in spelling, tact or fact are transmission errors] -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
