Tutorial Updated 12/23/2019 There are two formula options for formatting Data Matrix data in Excel
Updates to steps 3, 7, and 9.
1. Download and install a font package that includes Data Matrix. The package installs the necessary encoders. To generate barcodes directly in Excel, download and install the IDAutomation 2D XLS Font
2. Open Excel and set up the spreadsheet. In the example, data in column A2 will include the data to convert to the barcode. A formula will be written in B2 to produce the encoded data.
3. Minimize Excel and locate the VBA file named IDAutomation_DataMatrix_ActiveX_Macro.bas. The VBA is located in the Integration folder of the product download.
There is an IDAutomation_DM_FontEncoder.bas file that uses the formula =IDAutomation_DMatrix_FontEncoder(). If importing this .bas file, skip only step 7 of this tutorial.
4. Maximize Excel and select the Developer Tab.
5. Select Visual Basic to enter the Editor.
6. To import the VBA file select File -- Import File then search for and select the .bas file.
The VBA file will appear in the Modules folder.
7. NOTE: If you have imported the IDAutomation_DM_FontEncoder.bas file instead of the IDAutomation_Datamatrix_ActiveX_Macro.bas, skip this step. Select Tools -- References and search for the Data Matrix encoder; it will be named similar to IDAutomation Data Matrix and have a version number at the end of it. Choose OK.
8. Select File and choose Close and Return to Microsoft Excel.
9. In cell B2, enter the formula =EncDM and reference cell A2 to encode the data. If using the FontEncoder.bas file, use =IDAutomation_DMatrix_FontEncoder.
The encoded data for Data Matrix should appear as several alpha characters.
To generate GS1 Data Matrix
Setting the second parameter to 1 enabled ApplyTilde which allows you to insert ~1 before any Application Identifier (AI). Here is an example of A2 for GS1-Data Matrix:
(01)3456789012345 must be entered as ~1013456789012345
or using the Substitute function:
10. Save the file.
HOW TO DISPLAY SCANNABLE 2D BARCODES IN EXCEL
GENERATE BARCODE IN EXCEL
(1) Apply the IDAutomation2D XLS font.
(2) Right-click the column and choose Format Cells.
(3) Select the Alignment tab and then check Wrap text.
HOW TO MAIL MERGE
To generate the barcode the encoded file must be used in a mail merge/data merge. Video Example of 2D mail merge. The only difference between the video and this tutorial is that the video uses the Barcode Add-In encoder and Data Matrix. Begin the video at 2:35.
CARRIAGE RETURN ERROR
After merging the data into Word, a group of characters, such as _x000D_ or barcode
, may appear at the end of the merged data. To resolve the issue, return to Excel and implement the SUBSTITUTE function.
Posted 2344 day(s) ago