Chapter 4. Working with Microsoft Office Programs

Some office programs, such as Excel and Access, support programming using VBA (Visual Basic for Application). Some programs support embedding ActiveX controls. All office programs seem to accept copy/paste of RTF and EMF objects. The table below summarizes the support options of various Office components (version 2007).

Table 4.1. Interoperability with Microsoft Office Programs

ApplicationRTF/EMF (from GUI encoder)ActiveX controlProgrammable

[a] Excel does not format line gaps properly. Consequently you can't put RTF in an Excel cell.

4.1. Access (ActiveX Control)

This section explains the steps to add PDF417 barcodes in a Microsoft Access report using Morovia PDF417 Control.

  1. The PDF417 ActiveX control must be installed and registered on the computer.

  2. Open a report in design view and choose InsertActiveX Control...

  3. From the list of controls, select Morovia PDF417 Control.

  4. After the control is placed on the report, right click on it and choose Properties...

  5. Modify the Control Source property to point to the table and filed of the data you wish to encode into the barcode.

  6. Modify other properties, such as ModuleWidth, Cols, Rows and AspectRatio. After you are satisfied with the result, close the property dialog.

  7. Save the run the report. You should see the barcode appear in the report.

4.2. Access (PDF417 Font)

You can also add PDF417 barcodes to Access report using font-based solution.

  1. Before creating barcodes in Microsoft Access, you must import the required module. This module adds VBA function so that you can put into the report. Choose ModulesImport and select Access Example.mdb file, located in the program folder.

  2. Choose PDF417FontEncode_Module from the other database. After it is properly imported, it will appear as one of the modules in the database.

  3. Open a report in design view and add a text box to the report. The text box will be modified to contain a barcode.

  4. Right click on the text box and choose properties.

  5. Place the formula =PDF417Encode([TestData.Data],0,7,9,True,0,3.1) in the control source property of the text box where [] is the field that contains the data to be encoded into the PDF417 barcode. The following parameters are rows, cols, security level, full size PDF flag, aspect ratio and y-height. Note that the y-height value should match the one in the font to be used. In our case, the font we will use is MRV PDF417 N3, which has a fixed y-height of 3.1.

  6. Run the report. You should see lines of hexadecimal characters appear in the place of the text box. This is the barcode string in the raw text form.

  7. Go back to the design view and change the font of the text box. In our case, choose MRV PDF417 N3 and 8 points. Adjust the size of the text box to fit the whole barcode.

  8. Save and run your report. You should see the barcodes appear in the report.

4.3. Excel (ActiveX Control)

Excel has line gap issues with PDF417 fonts. You can paste EMF image from GUI encoder, or use ActiveX control as outlined below.

  1. After you finished other parts of the spreadsheet, choose ViewToolbarsControl Toolbox

  2. When Control Toolbox appears, click on More Controls button.

  3. From the list of controls presented, choose Morovia PDF417 Control.

  4. Select the area to place the control in the spreadsheet.

  5. Right click on the control, choose Properties and change the Linked Cell property to the name of the cell that contains the data you wish to encode.

  6. Change other properties as necessary such as Rows, Cols and AspectRatio to adjust the size of the barcode.

  7. After editing the properties, click on Exit Design Mode button to exit design mode. The barcode will appear in the spreadsheet.

  8. The barcode is now bound to the cell. Change the data of the linked cell, the barcode will change accordingly.

    Note: to subsequently modify or delete the barcode control, Excel must enter Design Mode. This can be done by pressing the Design Mode button on the Control Toolbox.

4.4. Microsoft Word

Using PDF417 control in Microsoft Word is similar to the one in Excel, except that Word does not provide a way for data binding.

  1. choose ViewToolbarsControl Toolbox.

  2. In the toolbox, choose the more controls button.

  3. Select Morovia PDF417Control from the list of available ActiveX controls. After selecting it, the control will appear in the document, the control may be sized as necessary. To change the properties of the control right click on the control and choose Properties.

  4. When finished, exit the design mode by choosing the design mode button.

  5. To edit the properties of the control the program must be in design mode. If there are problems editing the properties of the control, press the design mode button to enable it.

4.5. Word Mail Merge

This tutorial uses Excel file Word Mail Merge DataSource.xls as mail merge data source. The data looks like this:

We want to print address information as well as a PDF417 barcode that encodes such information in Avery label paper 5163. The Print Preview looks like the one below:

  1. Before we start, we need to import a module into Excel. To do that, open Visual Basic Editor. In Excel 2007, this is done by selecting DeveloperVisual Basic.

    In Visual Basic Editor, choose FileImport File. Navigate to the PDF417 Fontware installation folder, and select the Morovia.PDF417FontDLL.bas.

    Close Visual Basic Editor.

  2. Add a new column in the spreadsheet that will hold the barcode string. In our case, we use column H. In cell H2, enter the definition as below:

    After hitting Enter, you should see a hexadecimal string result show up. If not, examine the formula you entered.

    Note the use of Excel function CONCATENATE here. This function is used to combine several fields, as well as line return characters.

    See the DLL API for the meaning of each field. Here we require the number of columns as 4.

  3. Copy the formula to other cells of the same column. This can be done by selecting cell H2, highlighting the cells that the formula is copied, and select Paste.

    Close the Excel file and start Microsoft Word.

  4. In Microsoft Word, choose MailingStart Mail MergeLabels. Select Avery 5163 as the label we will work on.

  5. Select Select Recipients Select Existing List. In the file dialog, navigate to the spreadsheet we just created. If it asks for Select Table, choose Sheet1$.

  6. Click on Address Block. This is for the address line. Microsoft Word has the intelligence to select the address block.

  7. Hit Enter once to move the cursor below, and click on Insert Merge Field. Select Barcode as the merge field. The document looks like the one below:

    Click on Review Results. You should see the first record show up, with hexadecimal characters in the place of barcode.

  8. Now adjust the font for the address block and the barcode field. For the address block, we use Arial 16 points. For the barcode, use MRV PDF417 N3 14 points. Note: the font should match the yHeight value in the formula.


    Make sure that there is no gap between two lines in the barcode field. If you see blanks inside the barcode, adjust the paragraph setting by selecting Paragraph Spacing. Use 0 for Before and After spacing, and Single for Line spacing.

  9. Copy the format to other labels by selecting Update labels.

  10. Select Preview Results to view the sheets of labels.