Forum Post Updated: 11/17/2021
Note: an additional support solution is also available that removes the _x000D_ character from appearing in the Word merge result.
I will provide instructions from beginning to end on how to generate a barcode from Excel to Word for Mail Merge using 2D barcode fonts (Data Matrix) and the Barcode Add In. The _x000D_ is caused when Word cannot properly interpret the carriage return. It is possible to use the SUBSTITUTE function in Excel to correct the issue.
DOWNLOAD AND INSTALL
1. Download and install the 2D barcode package. In the example, I will use Data Matrix.
Other 2D barcode types:
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.
Other 2D barcode types:
PDF417 uses IDAutomation_PDF417_Macro.bas
QR Code uses IDAutomation_QR_FontEncoder_VBA_DEMO.bas or IDAutomation_VBA_QRCodeFE2021.bas (licensed)
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 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. Encode the data by entering the formula. 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. Press return. Drag the corner to encode the remaining data.
The data in Column B is encoded.
10. To avoid the _x000D_ issue that may occur in the Mail Merge, apply the Substitute function and replace the carriage return. =SUBSTITUTE(IDAutomation_DMatrix_FontEncoder(A2), CHAR(13),"")
NOTE: =EncDM() and =IDAutomation_DMatrix_FontEncoder() is specific to Data Matrix.
To encode PDF417 for mail merge use, =SUBSTITUTE(IDAutomation_PDF417(A2), CHAR(13),"")
To encode QR Code for mail merge use, =SUBSTITUTE(IDAutomation_QRCode_FontEncoder (A2), CHAR(13),"") or demo version =SUBSTITUTE(IDAutomation_QRCode_FontEncoder_DEMO (A2), CHAR(13),"")
11. Open Word and perform a Mail Merge. Users inexperienced with Word should use the Mail Merge Wizard or visit the Microsoft Office Website.
12. During the Mail Merge Recipients step, the _x000D_ in the encoded data should no longer appear.
13. Insert the Merge Fields.
14. When the Barcode field is merged, spaces may appear between the encoded data.
Choose the No Spacing option in the Home tab.
The encoded data should appear correct.
15. Select the barcode font to the field. In the example, the IDAutomation2D is used.
Posted 10.1 year(s) ago