DataMatrix & PDF417 2D Font for use in Microsoft® Excel®
The 2D XLS font by IDAutomation generates Data Matrix, QR Code, PDF417, and
Aztec Barcode Symbols from a single TrueType font within Microsoft Excel
Spreadsheets. This font is compatible with all
IDAutomation 2D Font Encoders. This font has been tested with Excel 2003, 2010 and
2013 and should also be compatible with other versions.
(installs the 2D XLS font and a visual implementation example within Excel)
Implementation and Tutorial:
Download and Install one of the 2D font packages such as Data Matrix, QR Code or
PDF417. This tutorial uses the QR-Code as the example.
Then install the IDAutomation2D XLS.ttf font from the
download. These fonts are also included in the TrueType folder
of the Universal 2D Barcode Font
Download. The IDAutomation2D XLS N5.ttf font is the same except
it has the PDF417 N dimension set to 5.
Open Excel and begin setting up the spreadsheet where the first column will
be the Data to Encode and the second column will be the encoded data
formatted for the barcode.
In Windows Explorer, locate the VBA file included in the zip file of the
font package downloaded. For example in the extracted zip file, the macro is
found in the Integration folder:
in versions prior to 2015, the file will be found in the program files under
the following name:
List of 2D VBA References.
Return back to Excel and select the
Visual Basic option off the
- This will open the Microsoft Visual Basic for Application screen. Select
File - Import File.
Search for the VBA file
that was located in Step 3 above.
This example locates the QR Code VBA, version 2015 naming convention:
The VBA will appear in the modules folder in the Project window:
- Next, still in the Microsoft Visual Basic for Application screen, go to
Tools - References and select the corresponding named DLL.
Click OK, then File - Close and Return to Microsoft Excel:
Note: If unable to locate the dll or receive Compile Error:
'User-defined type not defined', browse the C:\Windows\system32 folder for the
specific barcode type dll that is needed to import. For example, if generating Data Matrix
barcodes, browse System32 for the file IDAutomationDMatrix64.dll.
- Now, enter the formula =IDAutomation_QRFontEncoder(refer to cell of data to encode) in the
column next to the data to encode. In this example, the formula is entered in
cell B2 and is referencing cell A2 to encode the data.
When the cell to encode is referenced, a string of random characters
- At this point the file could be saved with the data encoded for Mail
Merge purposes, as the barcode font would be applied after the mail merge
had been completed.
- To apply the IDAutomation2D XLS.ttf font from the
download, right click on the cell and select Format Cells:
Select the IDAutomation2D XLS.ttf font:
the font was installed prior to beginning the tutorial and the font name
appears but the preview of the font does not, please reboot the computer to
register the font.
- Very important step to complete on the Alignment tab is to select "wrap
text" under the text control section:
- Click ok, and there should be a barcode in place of the encoded data:
may be small lines when viewing on the screen in the barcode image but those
lines will not appear when the barcode is printed.
- When saving the file, a pop up may appear:
Click No, and select .xlsm extension to save the file as a macro-enabled
Below is a list of the file names for the various 2D fonts:
||VBA File Name
||Reference - VBA Project
IDAutomation VBA for QR-Code.bas
|IDAutomation QRCode Barcode 1.000
||=EncQR( ) or
||IDAutomation Datamatrix Barcode 1.803
||IDautomation PDF417 Barcode
||IDautomation Aztec Barcode 1.710
||IDAutomation Maxicode 1.0 Type Library
* The Maxicode package contains it's own fonts and cannot be created from the IDAutomation2D XLS font.
Excel 2D Font Limitations:
Implementation of the 2D XLS font within Excel does have some minor limitations:
The symbol is only formed properly when the cell it resides in is formatted
to "Wrap text" as shown in the image to the right. This is set
from the "Format Cells" dialog. The cell must also be sized so that it is
wide and tall enough to properly contain the entire symbol.
very small line gap appears within the symbol because the line spacing
property is not adjustable within Excel, and multiple rows are required to
generate a 2D barcode. An amplified view of this gap is shown here to the
right. IDAutomation's XLS font reduces the line gap to
about 2-5% of the X dimension size, which allows a barcode scanner to
overlook and ignore the gap. Even if the gap did cause a scanner
issue, it would be corrected within the scanner because each 2D symbol
includes error correction. The line gaps may appear large when viewed on the screen,
however they are small when printed, especially at 600dpi or greater. The
gaps appear larger on lower resolution devices.
Amplified view of the Excel line gap with the 2D XLS barcode font.
A white space will appear at the bottom of the symbol within rows of the
spreadsheet, as is shown on the graphic at the top right side of this page.
This is because of the way Excel manages the size of this font, which is
required to reduce the non-adjustable line spacing.
- Multiple sizes of the 2D XLS font are not possible since this is the
only size that has been found to reduce the line gap spacing to an
- This font cannot usually
be printed from Excel at less than 8 points, which is a X dimension of about 28 mils or
.07CM. Therefore, the symbols can become quite large if much data is being
encoded. When using this font within Excel, IDAutomation recommends encoding
as few characters as possible.