Looking for a macro to create PDFs from Writer's templates and Calc's data

English
, ,
#1

Hello there. I have a Calc’s sheet with data and a Writer’s template. I need a macro that can insert data from Calc’s sheet into Writer’s template and save it as a PDF file.

I’m well aware I can do it via mail merge, but I want to do it in one click via macro, because in fact, I have more than one Calc’s sheet with data and more than one Writer’s template.

I’ve found the video showing a macro doing almost exactly what I want to achieve, but in Excel: https://www.youtube.com/watch?v=tBmSYgZsLwA

Please help, I do not want to migrate to Excel.

#2

menu:File>New>Database…
Connect to existing database of type “Spreadsheet”
Specify your spreadsheet.
[X] Register database
Save the database document. Nothing has been copied or converted. Your data are still in the spreadsheet only.

Now you have 2 options:

  1. Create a database report which is more or less like a text document filled with table data. Just play around with the report wizard to get the idea.
  2. In Writer, hit Ctrl+Shift+F4 for the data source window and drag your table from the left pane into the Writer document.

Both results can be exported to pdf.

#3

I already said that I’m aware how to do it manually.

I need a macro to do it automatically in one click from Calc itself, because I have a lot of data and a lot of templates.

#4

I would be happy to congratulate you on this.
We have a lot of code snippets and ready-made programs for all occasions. You can congratulate us on this.
We will be stuck at this point for a long time: you do not show us samples of your templates and data, we do not show you the code that could process them.

Welcome!

#5

While I didn’t included samples of my data or templates, I’ve included a short video showing Excel version of the macro I need. I thought that was enough. Here’s a picture version:

te
te837×565 45.8 KB

#6

Oh, that’s so much better! Now we know that we should focus on paired percent sign tags. Judging by the name of the resulting document “Untitled 2”, it is indeed created from a template with some fixed name (and this name will be permanently written into the macro code). It remains to find out in which folder the resulting PDF should be saved and what name is preferable for it.

#7

Why not solve this with a simple mail merge or mail merge driven by macro code? What is the difference between %my tag% and a regular mail merge field?

#8

This is just an example illustrating what I need. The problem can be solved in multiple ways, the exact implementation doesn’t matter for me.

If you know a mail merge macro that can do what I need (automatically create PDFs for certain data entries w/o leaving Calc), that’s great, please link it, it would be a solution for my problem.

#9

For certain data entries? The brackets in your picture indicated all the available entries

#10 
Sub Main
REM test mail merge service with emails from Bibliography and some mail addresses in column "Custom5"
    mm = createUnoService("com.sun.star.text.MailMerge")
    mm.DocumentURL = ThisComponent.URL
'    mm.OutputType = com.sun.star.text.MailMergeType.PRINTER
    mm.OutputType = com.sun.star.text.MailMergeType.FILE
'    mm.OutputType = com.sun.star.text.MailMergeType.MAIL
'    mm.OutputType = com.sun.star.text.MailMergeType.SHELL
    mm.DataSourceName = "Bibliography"
'    mm.CommandType = com.sun.star.sdb.CommandType.TABLE
'    mm.CommandType = com.sun.star.sdb.CommandType.QUERY
    mm.CommandType = com.sun.star.sdb.CommandType.SQL
'    mm.Command = "biblio"
    mm.Command = "SELECT * FROM ""biblio"" WHERE NOT ""Custom5"" IS NULL"
    mm.EscapeProcessing = True
    mm.AttachmentFilter = "writer_pdf_Export"
    mm.AttachmentName = "biblio.pdf"
    mm.execute(Array())
End Sub

See also LibreOffice: MailMerge Service Reference for more properties, i.e. FileNameFromColumn and SaveAsSingleFile

2 Likes
#11

Thanks. Not exactly what I wanted, but thanks anyway. I thought the problem was common enough for the complete solution to be available somewhere, but I guess I’ll just have to code it myself.

#12

This is a great idea!
If so, you may find this discussion helpful, chapter
5.8.4. Create a new document from a template from the Book. And also chapter 7.14. Search And Replace and Listing 6.6: Is anything selected in a Calc document.

And of course, we would be interested to see what you came up with.

#13

AskMMSample.ods (13.6 KB)
AskMMSample.odt (11.8 KB)

2 Likes
#14

Thanks, that’s exactly what I needed!

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