NOTE: A new product is now available since this issue was resolved that offers a better way to generate barcodes in Excel on Windows and Mac, the IDAutomation Native Generator for Excel.
Tutorial Updated 10/28/2021
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_VBA_DataMatrix_FE2021.bas or (IDAutomation_Datamatrix_Macro.bas--Demo verison). The VBA is located in the Integration folder of the product download.
4. Maximize Excel and select the Developer Tab.
Microsoft Support - Enable 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.
7. NOTE: If using the demo file (IDAutomation_Datamatrix_Macro.bas), you must complete this step. Otherwise, skip to step 8. Select Tools -- References and search for the Data Matrix encoder. Choose OK.
8. Select File and choose Close and Return to Microsoft Excel.
9. In cell B2, enter the formula:
Licensed Version use: =IDAutomation_DMatrix_FontEncoder()
Demo Version, use: =EncDM()
and reference cell A2 to encode the data.
The encoded data displays as several uppercase 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 9.2 year(s) ago