Creating Catalogs with Microsoft Office Publisher 2003
Microsoft® Office Publisher 2003
Summary: Create catalog publications and Web sites programmatically using the catalog merge functionality exposed in the Publisher 2003 object model. (12 printed pages)
Overview of the Catalog Merge Feature
Connecting to a Data Source
Creating the Repeating Catalog Merge Area
Insert Merge Fields
Add Other Elements to the Catalog Merge Area
Executing the Catalog Merge
Deleting Catalog Merge Items
You can quickly create catalogs, product and service datasheets, inventories, directories, photo albums, and other kinds of publications in Microsoft® Office Publisher 2003 by using the catalog merge functionality exposed through the Publisher object model. You can use text and pictures from a data source, such as a spreadsheet, to quickly create catalogs, directories, inventory lists, photo albums, and other publications that display multiple items per page.
The catalog merge feature is an extension of the mail merge functionality included since Publisher 2002. Both processes combine information from a data source with a template. The template repeats once for each selected record in the data source. Unlike a mail merge, you can repeat the catalog merge template multiple times per publication page.
Use the catalog merge feature to create catalogs, directories, photo albums, or any other type of publication that displays one or more items per page. Catalog merge lets you merge multiple records from a data source into a single publication.
When you use catalog merge, you combine information from a data source with a catalog merge template to generate merged pages, which you can save as a new publication or add to the end of an existing publication. You can also save your catalog merge template and use it again, whenever you update the information in your data source, to create additional sets of merged pages.
Figure 1. Example of Catalog Merge
1 - Data source with record information, such as item names and descriptions.
2 - Catalog merge template with catalog merge area containing merge fields that are placeholders for record information.
3 - Resulting merged pages displaying multiple records per page.
For example, suppose you wanted to create a catalog of items your business sells. You have a database of product information with 33 items, including photos of each. You create a catalog merge template that takes up a third of a page. When you execute the catalog merge, Publisher would repeat the catalog merge template once for each record; in this case, 33 times. Based on the size of the template, three records would appear on each page. The resulting publication would therefore contain eleven pages.
There are several steps to the catalog merge process:
- Connect to the data source you want to use. Filter and sort the data source records as desired.
- Create a catalog merge template that repeats once per record.
- Insert text and/or picture data fields from the data source into the merge template as desired.
- Add any other shapes you want to repeat to the catalog merge area. Position and format the fields and shapes as you desire.
- Choose the destination of the merge results (a new or existing publication) and execute the catalog merge.
The resulting publication pages (and the shapes they contain) retain no link to the catalog merge template; that is, they have no "memory" of how they were generated. They are not connected to the data source.
The MailMerge object represents the mail merge and catalog merge functionality in a publication. Use the OpenDataSource method of the MailMerge object to connect the publication containing your catalog merge template to a data source. Although you can have one catalog merge template on each page of a publication, you can have only one data source connected to a publication at a time.
Use the IsDataSourceConnected property of the Document object to determine if a data source is currently connected to a publication. Once a data source is connected, the MailMergeDataSource object, which represents the data source, becomes available. The Name property of the MailMergeDataSource object returns the name of the data source connected to a publication.
You can specify data sources in the following formats:
- Microsoft Access (all versions)
- Microsoft Excel (versions 3.0 and later)
- Microsoft FoxPro (version 2.0 and later)
- Microsoft Outlook® (all versions)
- Microsoft SQL 6.x, 7.0, and 2000
- Microsoft Word tables or merge data documents
- Microsoft Works for MS-DOS 3.0 (if database contains no formulas)
- Microsoft Works for Windows® 3.0 and later (if database contains no formulas)
- Oracle 7.3, 8i (8.1.6) (if client software is installed)
- dBase III, IV, and V
- ASCII text files (except for fixed-field width files)
- Any data source with an OLE DB provider
You can use the various members of the MailMergeDataSource object to:
- Sort the data source records.
- Filter data source records based on criteria you define.
- Access the data fields you want to include in the catalog merge template. (This is discussed later in this article.)
For more information on sorting and filtering data source records, see Sorting and Filtering Your Data for Mail or Catalog Merges in Microsoft Office Publisher 2003.
When you open a data source, Publisher copies that data source into application memory. If you then make changes to the original data source file, they do not get propagated to the copy of the data source with which Publisher is working. You must close the data source and open it again to include those changes. Use the Close method of the MailMergeDataSource object to disconnect the data source from the publication.
The catalog merge results are based on a template, called a catalog merge area, that you create in your publication. When the merge is executed, the catalog merge area is repeated once for each selected record in the data source. Everything contained in the catalog merge area is repeated. Data fields, which may be pictures or text, are populated with the actual record data.
Use the AddCatalogMergeArea method of a page's Shapes collection to add a catalog merge area to a page. You can add only one catalog merge area to each page in a publication. This method returns a shape whose Type property equals pbCatalogMergeArea.
Sizing the catalog merge area is discussed in the Sizing the catalog merge area section of this article. Adding shapes to the merge area is discussed in the Add other elements to the catalog merge area section of this article.
Merge fields are placeholders you insert into a catalog merge area. Each merge field is linked to a column in your data source. When the catalog merge is executed, the merge field placeholder is replaced with the record data from that data source column for that record. Merge fields can contain either text or picture data.
Text Data Fields
Text data fields contain string data. You cannot insert a text data field directly into a catalog merge area. Rather, you must first insert a text box into the catalog merge area, and then insert the text data field into the text box. You can insert multiple text data fields into the same text box.
Use the AddTextBox method of the Shapes collection to add a text box to a page. Then use the AddToCatalogMerge area method of the text box shape to insert the text box into the catalog merge area. The AddToCatalogMerge method is discussed in greater detail in the Add other elements to the catalog merge area section of this article.
Use the InsertMailMergeField method of the TextRange object to insert a text data field. The varIndex parameter of this method specifies the index or name of the column in the data source. This method inserts the text data field as a TextRange object.
Note You can use the members of the TextRange object (such as the Font property) to apply character formatting to this object, which is then applied to your text data once the merge executes.
The following example adds a text box to the publication page containing the catalog merge area. The text box is then added to the merge area, and a text data field, representing the data from the List Price column of the data source, is inserted into the text box, along with a label for the field.
'Add a text box to hold data text field 'and add it to the repeating region Set pbTextBox1 = ThisDocument.Pages(1).Shapes.AddTextbox _ (1, 100, 100, 175, 25) pbTextBox1.AddToCatalogMergeArea 'Insert a text label and the data field in the textbox With pbTextBox1.TextFrame.TextRange .Text = "List Price: " .InsertMailMergeField "List Price" End With
Picture data fields
Picture data fields act as placeholders for graphics listed in your data source. You can use any image format that Publisher supports, such as .jpg, .bmp, or .gif for the picture data.
The actual image file is not stored in the data source. Rather, the data source should contain a file path to each image file. You must specify the file path in one of the following formats. The formats are listed in the order in which they are tried. For each image file, Publisher tries each of the following formats until it can resolve the image file path. If Publisher cannot resolve the file path and find the image file using any of these formats, the image file is skipped when the catalog merge is executed.
- URL. For example, https://www.example.com/catalogphotos/toy0911.jpg.
- Absolute file path. For example, C:\Documents and Settings\Username\My Documents\My Pictures\House Listings\123SophiaLane_exterior.jpg.
- Relative file path. Based on the saved location of the publication containing the catalog merge area. For example, House Listings\123SophiaLane_exterior.jpg, where House Listings is a folder in the same directory as the publication.
- Relative file path. Based on the location of the My Pictures folder. For example, House Listings\123SophiaLane_exterior.jpg, where House Listings is a folder in the My Pictures directory.
Before you can insert a picture data field, you must define the field specifically as a picture field. By default, data fields are considered to contain string data. To define a field as a picture data field, set the FieldType property of the MailMergeDataField to pbMailMergeDataFieldPicture.
Picture data fields are inserted directly into the catalog merge area, using the Insert method of the MailMergeDataField object. The Insert method applies to the catalog merge area on the active page only. The picture data field is inserted as a Shape object whose Type property is pbPicture.
The following example inserts a picture data field into the catalog merge area on the active page, and then sizes and positions it.
Dim pbPictureField1 As Shape 'Define the Photo field as containing pictures With ThisDocument.MailMerge.DataSource.DataFields .Item("Photo:").FieldType = pbMailMergeDataFieldPicture End With 'Insert a picture field (Photo), then size and position it Set pbPictureField1 = _ ThisDocument.MailMerge.DataSource.DataFields.Item("Photo:").Insert With pbPictureField1 .Height = 100 .Width = 100 .Top = 85 .Left = 375 End With
You can add borders, background images and other design elements to the catalog merge area. When the merge executes, these elements also repeat once for each selected data record.
To add a shape to a catalog merge area, you must first add the shape to the Shapes collection for the page on which the catalog merge area appears. Use the various Add methods of the Shapes collection, for example, AddCallout, AddLabel, or AddPicture, to add the specific type of shape you want. Once you add the shape to the page, call the AddToCatalogMergeArea method to add it to the catalog merge area. Note that this method does not move the shape into the catalog merge area, but adds it at its present location on the page. If necessary, the catalog merge area expands to include the shape.
Each shape that represents a catalog merge area, that is, whose shape type is pbCatalogMergeArea, contains a CatalogMergeShapes collection, which is accessible through the shape's CatalogMergeItems property. The CatalogMergeShapes collection contains the shapes included in the catalog merge area. This includes any data fields (text or picture) that you inserted into the catalog merge area. As discussed earlier, you can add shapes representing data fields to the catalog merge area using the Insert or InsertMailMergeFields methods; the AddToCatalogMergeArea method does not pertain to them.
Sizing the Catalog Merge Area
The number of times a catalog merge area repeats on the publication page when the merge executes is determined by the size of the catalog merge area. The width of the area determines how many times it repeats horizontally across the page. The height of the area determines how many times it repeats vertically down the page. The HorizontalRepeat property of the CatalogMergeShapes collection returns the number of times the catalog merge area repeats across the publication page; likewise, the VerticalRepeat returns how many times it repeats down the page.
The catalog merge area automatically expands if necessary. For example, if you add a shape outside of its boundaries to it, or add a shape that is larger than the merge area, or if you move a shape in the area to outside the merge area's current boundaries, then the area expands automatically. However, the catalog merge area does not automatically contract if the area used by the shapes included in it decreases. For example, if you decrease the size of a shape, delete or move shapes closer together, the area remains the same size.
Programmatically, the Shape object representing the catalog merge area behaves as any other grouped shape. You can use the Height and Width properties to get or set the dimensions of the catalog merge area. If you decrease the size of the catalog merge area to below the total area taken up by the shapes included in it, you can reposition or resize those shapes to remain within the merge area. This may lead to unpredictable results. Note that the ability to decrease the size of the catalog merge area to below the total area used by the shapes included in it is not available through the user interface.
The final step of creating a catalog merge-generated publication is performing the catalog merge. Using the Execute method of the MailMerge object, you can perform the catalog merge either to a new publication, or append the results of the merge to the end of an existing publication. Unlike a mail merge, you cannot execute a catalog merge directly to a printer. The Execute method applies to the catalog merge area on the active page only.
The resulting publication pages and the shapes they contain retain no link to the catalog merge area or the publication that includes it; that is, the publication pages have no "memory" of how they were generated. They are not connected to the data source. There is no way to distinguish publications, pages, or shapes generated through a catalog merge from elements created by any other means.
Because of this, catalog merge results are not and cannot be dynamically updated if the data records they were based on change. However, you can always execute the catalog merge again to re-create the merge results with new or updated records.
There are several ways to remove catalog merge areas and the shapes contained in them from a publication programmatically. Each method differs in what shapes are removed, whether shapes remain connected to the data source, and whether the data source remains connected to the publication:
- The Close method of the MailMergeDataSource object disconnects the data source from the publication, but leaves the catalog merge area and the shapes it contains unchanged.
- If you apply the Delete method to a Shape that represents a catalog merge area, that is, a shape whose Type property is set to pbCatalogMergeArea, the selected catalog merge area and all the shapes contained in it are deleted. The data source remains connected to the publication.
- Use the RemoveCatalogMergeArea method of the Shape object to remove the specified catalog merge area, but leave the shapes contained in the catalog merge area. All shapes contained in the catalog merge area are placed on the publication page at their current location, but are disconnected from the data source and are no longer a grouped shape. However, the data source remains connected to the publication.
- You cannot programmatically delete a shape that belongs to a grouped shape such as a catalog merge area. Instead, use the RemoveFromCatalogMergeArea method of the Shape and ShapeRange objects to remove a single shape or selected shapes from the catalog merge area. The specified shapes are placed on the publication page at their current location. If the shapes represent data fields, they are disconnected from the data source. Once a Shape object is removed from the CatalogMergeShapes collection in this way, you can delete it using its Delete method.
The following example tests to see if there is a catalog merge area on the first page of the active publication. If there is, each shape is removed from the catalog merge area and deleted, and then the merge area itself is deleted.
Dim mmLoop As Shape Dim intCount As Integer Dim strName As String 'remove catalog merge area and all the shapes it contains 'from page 1 of the active publication For Each mmLoop In ActiveDocument.Pages(1).Shapes If mmLoop.Type = pbCatalogMergeArea Then With mmLoop.CatalogMergeItems For intCount = .Count To 1 Step -1 strName = mmLoop.CatalogMergeItems.Item(intCount).Name .Item(intCount).RemoveFromCatalogMergeArea ActiveDocument.Pages(1).Shapes(strName).Delete Next End With mmLoop.RemoveCatalogMergeArea End If Next mmLoop
Below is a table containing the objects and members most often used in the various step of creating catalog merge publications.
Table 1. Objects and members used for creating catalog merge publications
|Connect to a data source|
|Document||IsDataSourceConnected||Returns True if the specified publication is connected to a data source.|
|MailMerge||OpenDataSource||Attaches a data source to the specified publication.|
|Create merge area template|
|Shapes||AddCatalogMergeArea||Adds and returns a Shape object that represents the specified page's catalog merge area.|
|Insert data fields into merge template|
|MailMergeDataFields||Insert||Adds and returns a Shape object that represents a picture data field inserted into the publication's catalog merge area.|
|FieldType||Returns a pbMailMergeDataFieldType constant that represents the type of data contained in the data field (either text or picture).|
|TextRange||InsertMailMergeField||Adds and returns a TextRange object that represents a text data field for a catalog merge.|
|Add shapes to merge area and format them|
|Shape||AddToCatalogMergeArea||Adds the specified shape or shapes to the publication page's catalog merge area.|
|CatalogMergeItems||Returns a CatalogMergeShapes collection that represents the shapes included in the catalog merge area.|
|Type||Returns a PbShapeType constant that represents the shape type. Catalog merge areas return a shape type of pbCatalogMergeArea.|
|CatalogMergeShapes||HorizontalRepeat||Returns a Long value that represents the number of times the catalog merge area repeats across the target publication page when the catalog merge executes.|
|VerticalRepeat||Returns a Long value that represents the number of times the catalog merge area repeats down the target publication page when the catalog merge executes.|
|Execute the merge|
|MailMerge||Execute||Performs the specified catalog merge operation. Returns a Document object that represents the new or existing publication specified as the destination of the merge results.|
|Remove merge shapes from publication|
|Shape||RemoveCatalogMergeArea||Deletes the specified catalog merge area. All shapes contained in the catalog merge area remain in place on the page, but are no longer connected to the catalog merge data source.|
|RemoveFromCatalogMergeArea||Removes (but does not delete) a shape from the specified catalog merge area.|
|MailMergeDataSource||Close||Disconnects the data source from the publication.|
While the feature is called catalog merge, the type of publication you can create with this new functionality is really limited only by your creativity. You can create any kind of publication that takes information stored in a data source and presents it in publication form such as catalogs, product and service data sheets, inventories, directories. If you update your catalog publications regularly, or create a number of catalog publications for different audiences, programmatically automating the catalog merge process can make the process even quicker, and minimize errors and rework.