Transfer a data set from openoffice base to calc

Question

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.

I do not understand several parts of this question. What is a "query over a form?" Does "template openoffice-calc table" mean a [table AutoFormat](https://help.libreoffice.org/Writer/AutoFormat_for_Tables)? Also, I am unsure what you mean by a button event required. Where is the button -- on a Base form, or in Calc, or in a dialog or message box? When the button gets pressed, what do you want to happen? — Jim K, Feb 21 '16 at 01:20
With "query over a form" is meat a query which was created with the query assistant which will be executed by a form (created with the form assistant). You can specify that a document (in calc) is a template this is meat by that. The button on a base form is meant when it gets pressed the transfer of the data to the calc template should happen. - Sorry for the bad english! — Tobias L., Feb 21 '16 at 07:50

score 3 · Accepted Answer · answered Feb 22 '16 at 22:59

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.

Transfer a data set from openoffice base to calc

1 Answers1