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, dag@wieers.com, http://dag.wieers.com/
-- dagit linux solutions, info@dagit.net, http://dagit.net/

[Any errors in spelling, tact or fact are transmission errors]

--
Unsubscribe instructions: E-mail to users+help@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