LibreOffice using cells with macro in writer document

Question

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

There are many ways to insert values from Calc into Writer. Which method did you use? — Jim K
– Jim K, Commented Dec 11, 2017 at 19:07
Hello @JimK I use the mass-mail-assistent (I hope thats the correct translation). I like to keep it simple, so I do not want to use more vba-scripts. — Johannes C. Schulz
– Johannes C. Schulz, Commented Dec 11, 2017 at 19:24
What does "mass-mail-assistent" mean? Google guesses that it means help.libreoffice.org/Writer/Mail_Merge_Wizard. Please be specfiic about the steps required. See stackoverflow.com/help/mcve. — Jim K
– Jim K, Commented Dec 11, 2017 at 19:58

Jim K · Accepted Answer · 2017-12-13 06:04:26Z

1

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).

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.

Now in Writer, View -> Data Sources:

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

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.

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.

edited Dec 13, 2017 at 6:04

answered Dec 11, 2017 at 19:51

Jim K

14.1k2 gold badges26 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Johannes C. Schulz Over a year ago

Thank you for your very good explanation, but this also does not work. The macro inside a cell isn't executed when inserted/linked to a document. imgur.com/a/SLHGl You see: in row "Barcode" is just error "'#WERT"

Jim K Over a year ago

I edited the answer to show a Data Sources image. In your image, the left side of the Data Sources area does not look good. It says "anschluesse" (connections) but does not seem to list any. Can you take another screen capture that shows the entire left side? Perhaps there is some problem with your installation. What operating system, version and locale?

Johannes C. Schulz Over a year ago

The left side is just cropped - looks good for real ;-) . I tested this with two machines, both on Debian Testing with LibreOffice 5.2 . The macro inside the cell isn't executed if database/spreadsheet is read by a linked document. Now I use a different and not so comfortable way: I set up a second page in spreadsheet (instead of using writer) and placed my document there. Then I copy the desired row to the document-page and print it.

Johannes C. Schulz Over a year ago

See here: imgur.com/a/aTWtX - the column "Barcode" is also "broken". Or here with spreadsheet in background. imgur.com/a/Diqha

Jim K Over a year ago

Let us continue this discussion in chat.

|

Collectives™ on Stack Overflow

LibreOffice using cells with macro in writer document

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related