Generate page for each row for printing

English
,
#1

Hey there,
I have a sheet containing 2000 rows (one row per person). I need to print half a page with a summary of the data present on a row, for each row.

Currently, we have 4 sheets containing 500 people each, formatted so 2 people fit per page when printing those sheets. It contains their name, phone number, and a lot of links to different sheets with complex formulas. We manually incremented all values and fetched all the data per person within different sheets to create this “template”. The downside is that opening, saving, and working inside the document is obviously very slow and LO crashes often. This template was created a decade ago, I must believe we have better tools now to do this.

Is there a way I can select start and end person at the top of the sheet and generate the data on the fly when the user wants to print?

Or is there an alternative that doesn’t require an external program? Everything needs to be in this Calc/Excel document.

EDIT
I have some constraints (I cannot change those as it is out of my hand):

  • Ability to send a single document via email back and forth
  • The file, as an attachment, may be opened “without download”, so any external files might be missing or misplaced (Most people involved are not tech-savvy people)
  • The document may be edited by the recipient and sent back via email
  • The recipient need to easily print the summary sheet of the persons without any heavy know-how (I thought about putting simple instructions on a separate sheet)
  • 80% of the people uses Microsoft Office

The recipient cannot have to install extra pieces of software as they might find it hard or time-wasting and they’ll blame me for anything going wrong.

#2

What you describe is a serial letter aka mail merge.

#3

It shouldn’t be a problem to create the half-page printout using one sheet with some fix elements and probably lots of formatting, and to pull in the data for one current row (or person or…) which is identified by a row-number or a few specific data elements, with the help of formulas.
Why do you try to keep the “printable variant” in a ready-made part per data set?
Simple principle:

  • One sheet (or a few sheets) for data
  • One sheet for the “prettyprint” version of data for a selected identity.

Having prepared this it may need a bit of user code to get selections of data-sets printed.

You may see this as a kind/case of “mailmerge”. I would prefer to design it differently - and to not use a text processor for the task…

Did I simply not understand the question?

#4

I don’t think so…
Even 2013 we had databases available to hold the data and retrieve by query and mail-merge was available too. But you rule a lot out, if your condition is mandatory:

The usual database-aporoach would have one data-source, then a connected Writer-Template, wich would either print directly or to an intermediate file. It is however possible to have Calc-files as datasources…
.
If you really need everything in one file, you may look for advanced filters to solve the following problem to select records.

Note the ability to redirect filtered data to other sheets.
.
My question would be: Why did you rule out databases or more than one file 10 years ago? Or why everything needs to be in one file? I usually recommend project-folders, instead of putting everything in one file…

#5

Sorry, but no, it’s not possible to have anything else than a database document as datasource. However, in most cases the database document is linked to some spreadsheet.

Base and Writer are not external programs. You are working with one application running in one process, handling all kinds of documents.

It is simple as this:
menu:File>New>Database…
Connect to existing database
Type: Spreadsheet
Specify your spreadsheet document
[X] Register the database
Save the database.
Close and forget the database for now.

In Writer, get the data source window (Ctrl+Shift+F4).
In the left pane, navigate to your database table.
Drag column headers from the right pane into the Writer document which inserts place hoders for the respective spreadsheet column.

When you print this Writer document you get one document for each row in your data source, printed to a printer or to a file or to one file per data row.

#6

EDIT When I reply, I see who I’m replying to, but my replies just stack at the bottom of the thread. :confused:

I have some constraints (I cannot change those as it is out of my hand):

  • Ability to send a single document via email back and forth
  • The file, as an attachment, may be opened “without download”, so any external files might be missing or misplaced (Most people involved are not tech-savvy people)
  • The document may be edited by the recipient and sent back via email
  • The recipient need to easily print the summary sheet of the persons without any heavy know-how (I thought about putting simple instructions on a separate sheet)
  • 80% of the people uses Microsoft Office

The recipient cannot have to install extra pieces of software as they might find it hard or time-wasting and they’ll blame me for anything going wrong.

The simplest solution IMO would be a basic RDBMS server somewhere in the office or something and manage a way to put those in a spreadsheet, it would be way more flexible.

#7

EDIT When I reply, I see who I’m replying to, but my replies just stack at the bottom of the thread. :confused:

One sheet for the “prettyprint” version of data for a selected identity.

I’d love that. Might be feasible if the person needs to print 1 or 2 person’s data later on. Change the name in a dropdown, print, change the name again and print the page again.

But how do I manage the scenario where the person initially need to print a summary to give 500 people?
For example, the first time they fill their own data in our template and have 150 persons – they each need a printed summary.

#8

Yes. This should natively be supported by mailmerge.
Since I never use mailmerge I don’t know the details. But, of course you also need to define the selection before the job can start.
However, it isn’t excatly difficult to define such a job by a few entries into a spreadsheet, and then to print the job (based on always the same prettyprint range) serially with the help of user code.

#9

So you need to mail data of lots of persons. I hope this not tech-savy people handle encryption or you have liberal legislations on this…

So forget searching for anything new.
.
My suggestion would be a MySQL or MariaDB on the the web. Data-security is no big topic, if you use email now.

#10

All I wanted to say is: If somebody already has a spreadsheet with (proper formatted) data, it can be used an some kind of input-source for mail-merge, without conversion to a sql or other database, even, if I would usually recommend using one.
.
In this thread the existence of an .odb-file as connecting datasource is of no further importance as anything specific to LibreOffice is ruled out by

1 Like
#11

But only one needs to produce the serial letter with Calc, Base and a Writer template.
For a Calc-only approach, you need a macro like this:

sub printSerial()
xnames = ThisComponent.NamedRanges
iCount = xnames.getByName("RowCount").getReferredCells().getValue()
oCell = xnames.getByName("RowIndex").getReferredCells()
for i = 1 to iCount
  oCell.setValue(i)
  ThisComponent.print()
next
end sub

Define a named cell RowCount (calculated or constant) where the macro gets the row count.
Define a named cell RowIndex where the macro writes the row index of the current print row.
Define a row with array formula like =INDEX(A2:F99;RowIndex) which pulls the current print data from the list A2:F99.
Define a print range (“report”) with the variable contents referencing the array formula.

The macro will count from 1 to RowCount, enter the value into cell RowIndex which will fill out the print area with the values from the array formula and finally, the macro prints the print area to the default printer.

topic95932.ods (18.0 KB)

#12

Hahaha, you’re funny :stuck_out_tongue: Not my problem
Obviously, I know you’re right. I was shocked when I saw this, but there is nothing I can do. I sadly was only tasked with “fix it so it works”, nothing more.

#13

I understand that there is a Calc database with 2000 rows, and each row represents data from a person. And you want to print n cards, 2 per page.

Suggestion, create a template for the form with the reference and data using VLOOKUP, 2 per page.

In two cells the initial and final form are entered. Using a macro, it fills in 2 references on the page and prints, and so on filling and printing until the final one.

Post an example file, such as the data and the printing model, it will be easier to give a better suggestion.

#14

The Mail Merge feature of office suites, suggested to you in comments above, appeared decades ago, is common in the office suite industry, and is the proper way of implementing exactly your described task. It is implemented using a single application (here: LibreOffice, using its components only: Writer as the primary UI; Base (internally); and in your case, Calc as the data store); it has all tools, and proper UI, to help not tech-savvy users to use it. Basically, opening the correct Writer file, and simply pressing Print button, shows the proper Mail Merge dialog.

You insist on some specific artificial limitation. OK, it’s up to you to ignore huge supported development and resources available in the application you use, and instead, try to come with a set of home-made macros. Yet, I post this anyway, because IMO following it is wiser (and could be helpful to other people finding this question).

I am not sure what specifically was meant by this. If you referred to some internal implementation details, then yes, you are correct. But at the top level, Mail Merge may involve only two files: an ODT (the Writer document, having the database fields, and - since v.5.1 - internally also a data source definition, making a separate ODB database file unnecessary), and an ODS with the respective data.

Theoretically interesting, if it would be easy enough to create a macro, that would generate a new Writer document on fly, embed the proper data source definition in it from the opened ODS, start the mail merge operation, and then clean up it all - all the code being inside the ODS… possibly that could be the best “single file” solution, but it still much of work, requiring own support, for very little benefit…

1 Like
#15

Mail merge embedding

1 Like

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