Search code examples
libreoffice-calclibreoffice-basic

LibreOffice using cells with macro in writer document

I have a LibreOffice calc table and a writer document. I want to use the fields from table inside document. This works for "regular" fields.

But one field (Barcode) has a formula from additional Makro/script. This formula works fine in calc. But if I open the "datasource" in writer I only see "#VALUE" instead. Also if I print a document, there is not the right data inserted.

Use of macro is turned on in writer and in calc. What can I do to get the desired data into my writer document?

best regards Johannes

Solution

  • First, go to Tools -> Macros -> Organize Macros -> LibreOffice Basic. Expand to My Macros -> Standard -> Module1. This location is under the user directory. Managing locations is explained in detail at https://ask.libreoffice.org/en/question/35598/where-are-lo-basic-macros-stored/.

    Paste the following user-defined function.

    Function EAN13_Barcode(index As Integer)
    myArray = Array(_
        "9783598215001","9783598215018","9783598215025",_
        "9783598215032","9783598215049","9783598215056",_
        "9783598215063","9783598215070","9783598215087")
    EAN13_Barcode = myArray(index)
End Function

    Set up the spreadsheet like this (first image shows formulas, second shows values).

    spreadsheet example - formulas spreadsheet example - values

    Note: If the macro is embedded in the Calc document rather than stored in the user directory, then EAN13_Barcode() will work only in Calc, not in Base or Writer.

    Next, create a database link by going to File -> New Database.

    • Connect to an existing database: Spreadsheet.
    • Browse to the .ods file.
    • Yes, register the database for me.
    • Save as New Database.mdb.

    The table in Base looks like this.

    table in Base

    Now in Writer, View -> Data Sources:

    • Expand to New Database -> Tables -> Sheet 1.

    data sources window

    • Click in front of the row for the second record and drag to the main document area.
    • Insert data as: Fields.
    • Database columns are Barcode and FullName.
    • Press OK.

    result

    The fields can be refreshed if the values change. To do this, save the Calc spreadsheet and Writer document, close everything and then open the Writer document again.