[SOLVED] Transfer a data set from openoffice base to calc

Transfer a data set from openoffice base to calc

After I did a query in openoffice-base over a customized form I want to transfer a selected set of data into a template openoffice-calc table. I know I can access the data set in openoffice-calc via pressing the Data Source (F4) button but then I only get access over the query. The best solution would be after the database query over a form a button event is required to open a openoffice-calc table from the template and insert the data from the data set.

Solution

First go to Tools -> Macros -> Organize Macros -> LibreOffice Basic and add this code. Change the path of the template file.

Sub Copy_Record_To_Calc(oEvent)
    Dim oForm
    Dim templatePath As String
    Dim oServiceManager As Object, oDesktop As Object
    Dim oFileProperties As Object
    Dim oDoc As Object, oSheet As Object, oCell As Object
    Dim column As Integer
    oForm = oEvent.Source.getModel().getParent()
    If oForm.isAfterLast() Then
        Print "Hey, you are after the last element."
        Exit Sub
    ElseIf oForm.isBeforeFirst() Then
        Print "Hey, you are before the first element."
        Exit Sub
    End If
    templatePath = "file:///C:/Users/JimStandard/Desktop/Untitled 2.ots"
    Set oServiceManager = CreateObject("com.sun.star.ServiceManager")
    Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")
    Set oFileProperties(0) = new com.sun.star.beans.PropertyValue
    oFileProperties(0).Name = "AsTemplate"
    oFileProperties(0).Value = True
    Set oDoc = oDesktop.loadComponentFromURL( _
        templatePath, "_blank", 0, Array(oFileProperties))
    oSheet = oDoc.Sheets(0)
    For column = 1 to 2
        oCell = oSheet.getCellByPosition(column - 1, 0)
        oCell.String = oForm.getString(column)
    Next column
End Sub

Then in form design mode, right-click on the button and choose Control. In the Events tab, click the three dots next to Execute action. Click Macro... and find the Copy_Record_To_Calc macro that you added.

Now turn design mode off. Go to a record and click the button. It will open the Calc template and copy the first two columns of the current record into column A and B of the spreadsheet.