Planning your Wall Planner in advance

Between September and December, most of my working days are devoted to preparing school diaries and planners. One pain-point that I encounter with some diaries is the addition of a year-to-two pages planner. The planners usually look like this:

Apart from the events for each day that are on a layer above and updated each year, the base planner itself has two major wholesale changes:

  • The initials for the days of the week; and
  • The shading for weekends.

The initials are usually 365 threaded text frames that sits above a table containing the weekend shading. The text frames contain the initials of the days of the week, corresponding to the month and day they relate to, so changing the initials is easy. However, shading the cells containing Saturdays and Sundays on the table below takes time. Even if cell styles were used, they still have to be removed from the previous year’s appearance and applied to the appropriate year’s cells – subject to operator error.

Ideally, I was after a solution that would:

  • Easily update all planner base dates in one go
  • Shade all of the weekends without having to do this manually
  • Adjust automatically for leap years
  • Reduce any mis-dating via operator error

Didn’t I already write a script for this?

In 2018, I’d prepared a script to assist in the creation of wall planners that would allow for four types of layout configuration; and in 2020 was further improved to allow an output in one of 14 languages.

That said, this article isn’t meant to be a shameless plug of this script. The script is a great solution for creating new yearly planners. However, this solution requires updating existing planners. An additional solution had to be created.

The simple solution first

As I mentioned earlier, linked text frames that contain the appropriate days of the week initials. However, rather than use one paragraph style for these initials, each day of the week was given its own paragraph style e.g. Monday, Tuesday, etc.

The paragraph styles are based upon a base style that defines its basic appearance, with flourishes added to Saturday and Sunday in the form of paragraph shading. Each paragraph starts in its own text frame, and each paragraph style also refers to a next style feature to go to the next day’s style.

The days of the week are easy enough to prepare. The first way is quick but allows for operator error, and that is to type the 7 initials and their respective line breaks; and copying them 52 times, starting January 1st on the appropriate day.

The second (and more accurate way) is to make a new Excel file, type the start date in cell A1 and then with the cell selected, go to Fill, Series, and fill out the dialog box as shown

Once done, go to cell B1 and type: =CHOOSE(WEEKDAY(A1), “S”, “M”, “T”, “W”, “T”, “F”, “S”) and press return.

In the resulting cell, double-click the green square on the bottom right corner of cell B1 to automatically fill the remaining entries in the column.

From here, select column B, copy and paste it into the threaded text frames in InDesign.

Note – if it came in as a table, make sure to briefly change InDesign’s clipboard handling preferences to handle text and tables from other applications as text only.

Last thing to do is apply the paragraph styles. Take note of what day’s initial appears on January 1 (Friday in this case) and with the text cursor still visible in the text frame, select all type (Command A). From the paragraph styles panel, right click on the Friday paragraph style and choose Apply “Friday” then Next Style.

But this solution only fills two of the four criteria:

  • Easily update a planner dates in one go
  • Shade all of the weekends without having to do this manually
  • Adjust automatically for leap years
  • Reduce any mis-dating via operator error

This solution does not take February 29 in leap years into account. In these instances, an extra text frame needs to be threaded to take in February 29, so is not completely automatic.

The over-engineered solution

I did prepare a solution outside of work hours that would meet all four criteria; but the setup of the file took more time than simply adjusting the previous planner. It would also be difficult for other operators unfamiliar with my techniques to carry out the alterations unless they were trained beforehand. Ultimately, the solution wasn’t practical for my day-to-day work and was never implemented, but it is worth looking at the solution as it uses a variety of InDesign’s features such as:

  • GREP styles
  • Table and Cell styles
  • Rule above within Paragraph styles
  • Linking to an Excel file

Both the InDesign file and Excel file can be downloaded here.

First thing is to create an XLS file that contains the yearly information that changes and link it in InDesign. Linking to an excel file can be done via InDesign’s preferences.

Linking to an XLS file can be fiddly and I’ve found the best way to maintain appearances of linked tables that are updated is to make sure that table and cell styles are applied to the table that will contain the incoming XLS data.

A glimpse at the XLS file shows that it contains the initials of the appropriate days of the week. Note that the initial letter is represented by two letters rather than one (which will be explained shortly). The year is off to the right-hand side for ease of updating the planner by just adjusting the year.

An even closer look at the XLS file shows hidden columns. If we unhide these columns, the workings behind the technique are revealed. To compensate for leap years, the calculation for February 29 has an if-else statement that says if a date appears here, add the appropriate weekday initial, but if there is nothing, leave it blank.

Back in the InDesign file, all cells contain a paragraph style that have a GREP style instruction that makes the first letter minuscule in size and width.

Why two letters for the days of the week and not one?

It is to do with how the shading of the cell is automated. The first letter is used to identify a weekend with the initial S and that letter is colorised with an appropriate color for the weekend. If the letter was a weekday, the first letter is set to none. To control the background color of the cell, the rule above feature of the paragraph style is used as it allows the [Text Color] option to be selected, and this color is the first character that the paragraph style encounters. The rule above is then made thick enough to fill the cell, and then the offsets are applied to fill the remaining parts of the cell.

Why not use the paragraph shading feature? Unfortunately, this feature does not have access to the [Text Color] option from the color dropdown.

Updating the data can present a trap, as instead of simply changing the date in excel and updating the file, the art needs to be relinked and then selected with the show import options dialog to only bring in specific cells, as the date at the far right isn’t required. In this instance, it is changing the cell range from A1:AA32 toA1:Y32 as shown:

So this will make the base for the planner, and all the further information into the particular dates is done on a layer above using a table with the same cell dimensions.

As mentioned, this is an over-engineered solution that wasn’t implemented, but its use of combining linked Excel files, GREP styles and Cell styles may have application for other purposes.

Adding other languages to the Colecandoo scripts

As this site has become more widely known around the world, the issue of localization has been raised. The scripts I’ve written are based on my initial use as an English speaker with the International English version of Adobe InDesign. That’s fine for myself and other anglophones, but there are also times when scripts that are run on different language versions of Adobe InDesign:

  • Have an English user interface or output; or
  • Didn’t work because the script relied on coding that required a code reference based on the English language version of Adobe InDesign.

To this end, I’ve rectified issues concerning non-functioning scripts based on coding issues. However, translating the scripts into other languages is a task that I cannot undertake on my own as I do not speak other languages besides English, and would never solely rely on automatic translation software or services such as Google Translate.

I’m also aware that some of the scripts on this site gain more traction from countries where English is not the first spoken language, such as the following videos:

Though recently, the stars have somewhat aligned. I was approached to update my wall-planner script so that it could contain German and French user interfaces and outputs. With the assistance from the requester, as well as further assistance that has expanded this to Portuguese as well, this script has been updated.

In addition, the script can provide a wall planner in one of fourteen languages:

  • English
  • dansk
  • deutsch
  • español
  • ελληνικά
  • français
  • italiano
  • Nederlands
  • norsk
  • polski
  • português
  • Русский
  • suomi
  • svenska

The updated script can be found on the scripts page. Ultimately, I would like to update this – and other scripts on this site that contain user interfaces or outputs – to feature other languages besides English. If this is of interest to you, please contact me via my contact page.

Data Merge to Single Records Pro: Now Available

Since 2016, Colecandoo has provided the free version of the Data Merge to Single Records script for Adobe InDesign – a script that allows single records to be exported from Data Merge with unique filenames available from the Data Merge database itself. This improves Adobe InDesign’s default – naming each file Untitled-N and is only available for InDesign files, not PDFs.

On that note, the PRO version of this script is now available!

This script improves upon the free original by:

  • Exporting to various additional file formats, such as interactive PDF, EPS, PNG, JPG, direct to print, or PDF via InDesign first;
  • Add a primary key to either the start or the end of a filename;
  • When exporting to certain file formats – the ability to run a user-selected additional script before the export.

The script can be purchased for A$15 from the Buy Now button below.


The original Data Merge to Single Records script offered by Colecandoo remains free and can be downloaded from the scripts page.

Referencing pages of a multi-page PDF file during data merge… workaround

At the time of writing, there are three multi-page/artboard file formats that Adobe InDesign can import when placing a file via the File/Place function. These formats are:

  • PDF
  • Adobe Illustrator
  • Adobe InDesign

(While it is possible to create many artboards in Adobe Photoshop, it is not possible to import a specific Photoshop artboard into Adobe InDesign… – at the time of writing that is – but that is another article!)

When placing one of these three formats, it is possible to control several import functions using the show import dialog box, such as:

  • Which page (or pages) to import;
  • How the pages should be cropped;
  • Whether or not to place the pages with a transparent background; and
  • What layers to show and their visibility;

However, when importing these file types as variable images during a data merge, these options are unavailable and replaced with the following:

  • Only the first absolute page of the file is imported (not always the page numbered 1 as the first page can also be – for example – in roman numerals or start at a page other than one); and
  • Page cropping, transparency and layer visibility is determined by the same variables as the last file of that type to be placed into the artwork.

For now, there is no workaround to control the latter issues during a data merge, other than to be familiar with this behaviour and plan the merge accordingly. There is a workaround for importing pages beyond the first page of a PDF file… but not an Illustrator or InDesign file.

Workaround: Split the PDF

The term “workaround” is used loosely in this context. Unfortunately, the solution is to break the PDFs into single page records. This can be done within Acrobat using the split button from the organise pages panel.

This feature also allows multiple files to be split at once.

By default, the resulting files will maintain the same filename with the addition of _Partx prior to the filename, with x representing the absolute page number.

Otherwise, I’ve prepared an action that you can download here that will save the PDFs to the Documents folder of the machine running the action.

(Yes, I’m also aware that there are quite literally hundreds of websites out there that will split multi-page PDFs to single PDFs for free. However, the methods outlined above will do so without involving a third party).

The next part of the workaround involves the data itself, and I’ll be using Microsoft Excel to create formulas to make the numbering for the resulting pages. All variable images being referenced will also be in the same folder as the data file, meaning only the filename is required and not the full path and the filename.

For data where the page number is known

Add a column to the database that references the absolute PDF page number that needs to be imported.

Absolute vs Section numbers abridged:

Absolute numbers refers to a page number based on the total count of pages in the document, while section numbers refers to the page number that was applied using page numbering in the application that made the PDF.

For example, take a PDF that contains 20 pages with the first six pages being in roman numerals, and the remainder being in decimal numbers. These two different styles of numbering are section numbers, while absolute page numbers refer to the total count of pages. To reference page iv of the PDF, the absolute page number to reference is 4. To reference page 5 of the PDF, the absolute page number reference is 11.

In this example, the A column represents the PDF to reference, the B column represents the absolute page number, and C represents the result. To obtain this result, the following formula can be used:

=SUBSTITUTE(A2,".PDF","_Part"&B2&".pdf")

This formula will look at filename reference and substitute the .PDF portion of the filename for _Partx.pdf, where x represents the figure in the B column. Using this formula, only filenames with the PDF extension will be affected, while filenames in other formats will be unaffected.

For data where the page reference needs to increment by one more than the row above

The same formula can be used for the naming, but another formula is used to determine if the page reference should increase if the same base file is being referenced in the row directly above.

In this example, the N column represents the PDF to reference, the O column represents the absolute page number, and P represents the result. A 24 page file NS91912 is being merged and needs to have the page reference incremented by one so that the filenames are NS91912_Part1.pdf to NS91912_Part24.pdf. The following formula can be used to change the page reference:

=IF(N2=N1,O1+1,1)

This formula will look at the filename and determine that if the filename is different to the row above, put the number 1 in the cell, BUT if the filename is the same as the row above, take the page value from the cell above and add 1 to it into this cell.

In a perfect world

Again, this is a workaround – it will only work for PDFs and requires some upfront work to prepare. Ideally, if I had my way and could implement some improvements, I’d like to see:

  • Not just the ability to choose a specific page, but choose the correct trim box and layers as well. For example, a file reference such as myFile.pdf;1,trim;Layer1,Layer2 where 1 represents the absolute page number, trim represents what trim box to use, and Layer1,Layer2 represent the layers I would like to appear (or leave the layer bit blank if all layers should be visible).
  • The ability to perform a similar task for incoming INDD, AI or PSD files.
%d bloggers like this: