Unable to insert data from Database record to table via macro

English
, ,
#1

I have a database with a table in it (LO 7.5 on win 11) that has several columns such as membership no, first name, last name, date, last visit, photo (an image field) and about 200 records/rows.
I have a writer document with a table in it. when that document loads, i need it to AUTOMATICALLY fill in the table with some of the data from that database (such as last name, first name, and photo), selecting the record where the membership no is the same as the filename (which can only be one of the records).
First, I am not a coder and won’t pretend to be. Whenever i try to do it myself, i run into 3 problems:

  1. getting the file name and using it to select the database record number in writer
  2. Inserting the image into the table. Every time i try to, all i get is “” in place of the actual image, and I have been unable to find any way to actually get the image into the writer document (although it’s there in the database form i’m using for data entry)
  3. Making a calculation in writer using a formula to determine how many days have past since the member’s last visit, which i can easily do with the formula “=INT(SUM(-)/365)” however, if either of those table cells is a database field, It always returns “Expression Is Faulty” but works fine when there text (as opposed to a field which could be updated) in the table cells.

I give up, conceding that I don’t have the coding experience to even write a macro to do it and that what documentation i can find of the Libre Office BASIC macro language is too vague for me to understand.

Could anyone point me to a simple/extensive library of macros that maybe could be combined to do this? or some “LO macros for dummies” level literature? Or anything else to help me get it to work?

#2

Why don’t you use a report in Base instead? Images will work there, selecting of the source is no problem and you won’t need any macros for this.

#3

I’m not that familiar with reports, but i already have the document ready in writer (which i’ve been just typing the data into) and it’s a fairly complex page that would have to get data from a couple of places and add some sections that are other writer documents, so i didn’t think reports would be a good solution.

#4

[Writer] Stand-Alone Database Reports (somewhat limited, however well tested).

#5

Regarding the calculations and photos, you should resort to queries and database reports. A query delivers the partially calculated content. The report does the layout and formatting. Reports become difficult if there is a lot of running text.

#6

and therein is a major part of the problem, as the final output needs to formatted very specifically to be included in a PDF file. Writer is able to format it exactly, and I don’t know if i could just copy the non-standard sized page into reports and have it insert what it needs to where it needs to.

#7

The other approach is to insert the report, saved as an .odt or .ods file, in a writer document. I do not know whether your text document can be set up for this separation of content.

My experience is that an exact alignment of fields is required to get an .ods file without cells of variable length, splitted/merged, depending on the section: header - grouping - detail - footer

#8

LibreOffice is really integrated, so forms in Base are special Writer-files and the generated reports also.
.
You can therefore either change the “paper-size” of the report or integrate the report in other documents. My usual report is the content being a table, wich is not always desired for me.

#9

I’d have no idea how to do most any of that, despite using LO for a decade. It’s a fairly complex table, with a several nested tables, that need to pull the data from the database. I can obviously do by just putting DB fields in the table, selecting a record, and clicking data-to-fields, but that won 't insert an image in the table (or i haven’t figured out how to get it done instead of just getting “” in it’s place), and I have to manually select the record every time. I’d much prefer if it could just use the filename (say “123.ODF” and insert record #123 along with the image. surely there’s a way to do it, I just have no clue. I don’t see how a report could be formatted properly, there’s just too much static text/graphic on the page.

#10

Have tried a little bit. Created a template in Writer with a table. Copied some code I have already used to fill such a table - not all code is needed for this special thing. Some content is in German - hope it doesn’t matter.

Load both files in one folder.
Open the database.
Open the form.
Click on the button.
Macro will create a new Writer document, adds for every row of the table a new row in an existing table, also adds an image in the existing table.
Print2Writer.odb (91.0 KB)
Printout.ott (11.8 KB)

LibreOffice Base Report - modifying printout
#11

tried it, but when i click the button, nothing happens. I have both files in the same folder downloads/new folder/. German is fine, i can just reuse the macros! Am i supposed to do something more than click that button? (Libre Office 7.5, Windows 11)

#12

Did you enable executing of macros?
Tools → Options → LibreOffice → Security → Macro Security → Medium
I have set a Trusted File Location for a special folder here. So I’m not asked for executing macros in this folder.

#13

Yeah, that did it. this time it asked me to let the macro run, and it generated a table. I can probably work with those macros to get it to do what i need done, so thanks for those.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.