Using InDesign CS and Access for Data-generated Page Composition: A Basic Example
by Max Dunn, Silicon Publishing 		 
  Adobe InDesign can be used as a powerful data-generated page composition system with virtually any sort of database, thanks to the scripting support that InDesign provides on the Macintosh and PC platforms. On the Macintosh, this can be done with AppleScript, while on the PC this can be done with VB or any of its variants (VBA, VBScript, VB.NET).

Let's look at a basic example of data-generated page composition with InDesign: a watch catalog scripted with VBA from within Microsoft Access.

 
  Running the Example  
  Example Files

To run the example, download the .zip file and extract its contents into a new folder, leaving "use folder names" checked so that unzipping creates an "Images" sub-folder within the folder you extract to. First*, open InDesign. Then open the database, and click on "Run Example". If all goes right, the program should create a simple watch catalog.

Pretty cool, eh? Imagine, all you have to do is change the database content, perhaps change the watch images, and re-running the program will recreate the directory with updated content.

* Note: The template depends on two fonts: Myriad Pro Condensed, and Myriad Pro Bold Condensed. If you don't have these fonts, before running it you will want to open StartTemplate.indd, set the paragraph and character styles to reference fonts that are on your system, then save and close the file.

 
  Coding the Catalog  
  How did that work? Let's go step-by-step through the creation of the catalog. Keep in mind that there are many ways to do this sort of thing, this example uses but one approach.

Before working in VBA, we should understand the Indesign output that we are going to want for the catalog. In this case, we first create a blank Indesign template (StartTemplate.indd) that contains the basic layout preferences, as well as Paragraph and Character styles that we'll use when we actually add some content. The script will start by opening this file.

Beyond the template, we need to determine the characteristics of the InDesign objects we'll actually be generating from the script. In this case, the entry for a single watch consists of:
  • A rectangle containing the watch and its description (the outermost box)
  • A rectangle frame containing the image for the watch
  • A line dividing the watch from its description
  • A text frame containing the description of the watch

To dynamically create these objects, we need to know their coordinates (available from the Transform palette), as well as any style parameters such as stroke width and line color. Once we know the characteristics of the objects we will be creating in InDesign, we are ready to work in Access.

 
  Creating a Source Query in Access  
  The table we'll be generating our catalog from has the following design:

However, rather than basing our output directly on the table itself, we will base it on a query (or in some cases a SQL statement defining a query). This lets us control the way the output is filtered and/or sorted. So in the Access database we will create a query, "qryOutput" which we will reference in the VBA code. In this case it is a very simple query - in design view it looks like:

This query outputs all of the fields from the table "product" and orders them by ProductCode. The output looks very much like the table in this simple example, but queries can be quite powerful, pulling in data from multiple tables and/or other queries, or applying VBA functions to the data being processed, such as case conversion.

 
  Working with VBA in Access  
  To code the VBA in access, we will first create a new module. From the Database window in Access, click on "Modules" then "New." This will open up the VBA editor, where we'll write the code.

The first step in working with VBA in Access is to set the references properly: Access by default doesn't know about InDesign, and we are going to use a data access method that is not the default method of Access either. So it is necessary to tell Access where these libraries are:

  1. Choose Tools\References
  2. Un-check the reference to "...ActiveX Data Objects..."
  3. Scroll down and check the reference to "Microsoft DAO 3.6 Object Library"
  4. Scroll down and check the reference to "Adobe InDesign CS Type Library"
The references dialogue should look something like the following:

Now we can start coding. First, let's type "Option Explicit" at the top of the module: this forces us to declare every variable we use - this may sound tedious, but it pays off in the long run. Then, we'll create the function that creates the catalog. You can paste the source code of this function into your module (below Option Explicit), then run the code by clicking anywhere in the function and Choosing Run\Run Sub (or by clicking on the triangular blue "Run" button on the standard toolbar). 		 
  Looking at the VBA Code  
  While it might at first look like a huge program, the VBA function can be analyzed in terms of its sections and their sub-sections and things will become more clear. There are four main sections:
  • Variable declarations
  • Object and variable initialization
  • Looping through recordset, creating the catalog
  • Saving the finished catalog and closing up
Of these, only the loop through the recordset is very involved. Variable declarations simply declare the objects that we will use throughout the function: comments in the script explain each variable. Object and variable initialization is the process of opening up InDesign, instantiating the main objects we will use, and initializing variable values. Saving the finished catalog and closing up entails saving the file as something else (we want to keep the StartTemplate file for the next catalog) and closing and/or destroying objects that we've instantiated in the course of the program. 		 
  The Main Loop  
 

Looping through the recordset is of course where the main work is done, and that itself can be divided into subsections.

Let us consider the loop itself. The loop is the whole body of code going from: 

    Do While Not rs.EOF
until: 
    Loop
The first bit, "Do While Not rs.EOF" means "keep going until the recordset (rs) has an end-of-file condition (EOF)": in other words, move through the whole recordset. One cycle of the loop corresponds to one of the 12 products on a page.

Importantly, right before the "Loop" line (which means "go back to the 'Do While...' at the top"), there is the line: 

    rs.MoveNext
which means "move to the next record" (if we didn't have this, the loop would be infinite, as it would stay on the current record and EOF would never be met). So the loop iterates through each row of the query that we've set our recordset object (rs) to refer to.

Within the loop, what are we doing? We are creating one box on the page for the current record, including the rectangle, line, image, and text that we identified earlier when we were considering the layout. Within the loop the program follows the following steps:

  1. Figures out which row and column it is, and adds a page if necessary.
  2. Creates the outer rectangle.
  3. Creates the rectangle containing the image and places and fits the image.
  4. Draws the line between the image and the text.
  5. Creates the text frame and puts text within it for each field in the record (Product Number, Manufacturer, etc.), applying paragraph and character formats as appropriate.
The first step involves figuring out which box we're creating, based on counter variables for the row and column. The column and row counter values are then used to determine the position of the outer rectangle. Based on that position, the position of subsequent objects are determined, and the image, line, and text frame are added to the current page at the appropriate positions. 		 
  Writing Text from the Database  
 

Populating the text frame itself involves using insertion points to output static text as well as values from the database. The output for each of the fields is similar: 

    ' Product code
    Set oPoint = oPoints.Item(oPoints.Count)
    oPoint.AppliedParagraphStyle = oDocument.ParagraphStyles.Item("Body Text")
    oPoint.AppliedCharacterStyle = oDocument.CharacterStyles.Item("Bold")
    oPoint.Contents = "Product Code: " & vbTab
    Set oPoint = oPoints.Item(oPoints.Count)
    oPoint.AppliedCharacterStyle = oDocument.CharStyles.Item("Plain")
    sBuf = rs.Fields("ProductCode")
    oPoint.Contents = sBuf & vbLf
First the current insertion point is set to the latest one in the current story (in this example each text frame has its own story: the 12 textframes on a page are not threaded together). Then the paragraph style is set, which will apply to the entire story. Then the "Bold" character style is applied to the insertion point, before writing the static text corresponding to the current field. A tab ("vbTab" is the Visual Basic variable for a tab, the VB concatenation operator is "&") is concatenated to the static text to properly position the field value after its name: before outputting the value, the current insertion point is established and the "Plain" character style set.

Most fields are separated by a linefeed ("vbLf" is the Visual Basic variable for a linefeed): Retail Price and Sale Price, however, stay on the same line, so the output of the sale price ends with a tab instead of a linefeed.

  
  Conclusion
 
  This should provide a general introduction to data-generated page composition with InDesign and Access. Feel free to e-mail any questions or suggested improvements to the code. A great source of additional information is the InDesign Scripting forum at Adobe user to user forums.