DLSoft Application Note 08021


Barcodes created by mailmerge from Excel to Word using Barcode 
Fonts for Windows are incomplete or following data is corrupted.

Different characters cause problems in Excel/Word 2000 and 
Excel/Word XP combinations.


Cause
Code 128 and some other barcode fonts must contain characters 
representing the full ASCII character set. Further more some 
characters (such as tab, carriage return, form feed, etc.) are 
either not easy to input or are not treated as printable characters 
by Windows, and so require alternative characters to represent them.

All code 128 and EAN128 barcodes contain a check digit character 
which is not displayed in human readable form under barcodes and 
so users may not be aware of its presence. The check digit is 
calculated by an algorithm and may turn up as any character in 
the 107 characters of the code 128 character set.

When Word imports data from Excel through Mail Merge some characters 
are treated in a special way, and when those characters are present 
in the barcode the barcode may be incomplete or data following the 
barcode to be corrupted.


Resolutions

(a) the LogicalNot character (¬)

In the Excel/Word 2000 or XP combinations the character representing the 
Code 128 stopbars (the LogicalNot character, ASCII 172) does not 
pass from Excel to Word (although it does pass from Excel to a Text 
file and from a Text file to Word??) so barcodes obtained by mail merge
from an Excel sheet are incomplete.

A simple workaround it to add the LogicalNot character (¬) to the mail 
merge template immediately after the field name, e.g.

«co_barcode»¬


The alternate stop character is "guillemotleft" ASCII 171, so the 
problem could be fixed by replacing all ASCII 172s in the Excel data
 with ASCII 171s, using a macro similar to that described below. 


(b) the quotation mark (")

There is a problem with Word 2000 during mail merge imports where the data
contains a quotation mark. The only solution we know (apart from moving to
Word XP) is as follows:

In Excel the workaround is to tack on to the macro that generates the 
barcode characters additional code to Replace " with "" (ie. replace 
each quotation marks with two quotation marks), then Word 2000 correctly 
imports the data containing quotation marks. (You can get the same effect 
by doing a Replace from the Edit menu.)

An example of the macro code to do this is:

    Cells.Replace What:="""", Replacement:="""""", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False

If the user is using the "Barcode" macro provide in the Excel sample
installed along with the font, then this code can be inserted into the last
line (before the End Sub) of the Sub Barcode () section of the VBA code.

If the user is not familiar with editing macro code, then the procedure to
create a suitable macro manually is:
a) Select "Record New Macro" from the Macro item of the Tools menu
b) Give it a name - or accept the default name provided - eg Macro1
c) Select the top left cell
d) from the Edit menu chose Replace. The Replace dialog appears
e) into the "Find What" box enter a quotation mark, and into the "Replace
with" box enter 2 quotation marks.
f) push the "Replace All" button
g) Click on the Stop button to complete the macro recording.

In future run this macro each time the barcodes are created.




dLSoft
August 2002