IDAutomation's Bar-Code Tutorial
for Microsoft® Excel®
IDAutomation.com provides several products for implementing barcodes
in Microsoft Excel. Bar-Code Fonts
are available that create barcodes in Excel with an entire column, in addition
to ActiveX Controls that
can be used in a single cell. IDAutomation also offers
Barcode Label Software
that can print pre-designed labels from data in columns of an Excel spreadsheet
or a Barcode Image Generator
which is used to copy and paste static bar-code images into spreadsheets
one at a time.
|
Using
Self Checking Barcode Fonts
with Microsoft® Excel®:
|
Self-Checking Barcode
Fonts such as Codabar
(for numbers only) and
Code 39 (for numbers
and/or uppercase letters) are the easiest to use in Excel when encoding
numbers and/or uppercase letters. If
Self-Checking Barcode
Fonts cannot be used, or another barcode types is needed, refer to the
Barcode Tutorial for Excel
or the VBA
Barcode Macro site for Excel. Self-checking fonts have checking code
built-in so that calculation of check characters is not required.
IDAutomation's
self checking fonts
may be easily printed or generated as a calculated field in Excel. The following
tutorial demonstrates how this is accomplished:
- To create a proper barcode with
self checking
fonts, a character must be added to the beginning and ending of
the data being encoded. This is referred to as the start/stop character,
which is easily added to IDAutomation's
Codabar (for
numbers only) and
Code 39 (for
numbers and/or uppercase letters) fonts with the asterisk character.
- The start/stop character may be easily added to the beginning and
ending of the data being encoded with a simple Excel formula of
="*" & CELL & "*" where CELL is the
cell of the data being encoded in the bar-code.

- After the formula is entered in the cell, select the correct barcode
font for the cell and a symbol should appear. The cells may need to
be sized to contain the entire symbol.

- Print and scan the barcode(s) to verify the correct data is encoded.
If a scanner is needed to verify barcodes, IDAutomation recommends the
IDAutomation
USB Barcode Scanner.
Tabs and returns may be added with Code 39 fonts (in
extended39
mode) between cells by inserting "$I" (for a tab) or "$M" (for a return.)
For example, the following formula encodes a tab between cells A1 and B1:
=("*"&A1&"$I"&B1&"*")
Additional information about combining cells is provided at the
OfficeBarcode
Site.
|
Creating
a Column of Barcodes in a Spreadsheet
|
IDAutomation's barcode fonts provide an easy method of barcoding a column
in Excel. Code 3 of 9 barcodes are created in an Excel spreadsheet in this
example, with the IDAutomationC39 font that
is included in the Code
39 Barcode Font Package.
Codabar Fonts may
also be used to create smaller bar-codes when using numbers only. To use
fonts other than Codabar
and Code 39, refer
to IDAutomation's
VBA Macro Tutorial for Microsoft Office or the
Excel Barcode Integration Guide.
- Barcodes are created in column B from the data in column
A in this example. Enter the formula ="*"&A2&"*"
in cell B2 to append the required asterisks to the text from cell A2:

- Select the cell containing the formula and choose Edit - Copy.
Highlight the entire column by selecting the rectangle labeled "B" at
the top of the spreadsheet and choose Edit - Paste. The formulas
may take some time to re-calculate if the spreadsheet is large.

- Select the Code 39 barcode font for the column. With the entire
column that contains the formula selected, choose the IDAutomationHC39S
font from the Code
39 Barcode Font Package or another appropriate font. The
barcodes will then appear in the entire column.

- With the entire column that contains the formula still selected,
choose the appropriate point size. In this example, 12 points is used.
If there is a need to use barcodes smaller than 12 points, the
IDAutomation
Plug 'n Play USB Barcode Scanner dependably scans the Code 39 font
when printed as small as 6 points. With the entire column that contains
the formula still selected, choose the center text option so the barcode
is in the center of the field. Size the column so it is wide enough
to contain the generated symbol.

- Print and scan the barcode(s) to verify the correct data is encoded.
If a scanner is needed to verify barcodes, IDAutomation offers several
USB Barcode Scanners.
|
Tutorial
for Using VBA Barcode Macro Functions in Excel®:
|
IDAutomation's VBA macro tutorial for excel has moved to the
VBA Macro Tutorial
for Microsoft Office site.
|
Using
the Bar Code ActiveX Plug-in:
|
IDAutomation's ActiveX Controls may be easily used in Excel on Windows
systems. After purchasing
or downloading
the demo of the ActiveX Control, it may be dragged, dropped and sized
on a spreadsheet. This control can only be linked to one cell. If an entire
column of cells needs to be linked, the barcode
fonts would be the best solution.
- In Microsoft Excel, choose View - Toolbars - Control Toolbox.
Then a tool box toolbar will appear.
- On the tool box dialog, choose the More Controls button that
looks like this:

- Then, select the barcode control from the list of available ActiveX
Controls. Select the control that was installed starting with "IDAutomation,"
and drop it on the spreadsheet.

- After selecting it, "draw" the image for the control to appear in
the spreadsheet like a graphic image.

- After this, size the plug-in as necessary. To change the properties
of the control such as bar-code height and symbology type, right click
on the control and choose properties.

- To link a cell to the data that should be encoded in the barcode,
enter the cell location in the "LinkedCell" property of the control.
If an entire column of cells needs to be linked, the barcode fonts would
be the best solution.

- When finished, exit design mode by choosing the design mode button
that looks like this:
- Print and scan the barcode(s) to verify the correct data is encoded.
If a scanner is needed to verify barcodes, IDAutomation recommends the
IDAutomation
USB Barcode Scanner.
Design mode must be used to edit the properties of the control.
If problems are encountered when editing the properties of the control,
press the design mode button to enable it.
|
Linking
to Excel® with the Bar-Code Label
Software:
|
IDAutomation Barcode
Label Software is an easy to use WYSIWYG bar code label printing software
application that prints barcodes, text and graphics on standard laser and
inkjet printers, as well as thermal transfer printers.
This software easily links to columns in Microsoft Excel or a CSV text
file as a data source for a text or barcode object, which allows printing
of labels from data in Excel.
- Download and install the
IDAutomation Barcode
Label Software.
- Choose Tools - Data Sets - Link External Data.
- Choose the data type and choose Search to find the file.
- After the source path is found, choose the appropriate table if
applicable.
- Choose Link Data and there should be a portion of the linked data
visible on the screen.

- After the data is linked, choose Close.
- To link a column of data to a barcode or text object, click the
down arrow next to "Value Type" and select "External DataSource", and
click on the source column to link the value. The example window will
show what is linked.

- Increment the label number in the design screen to verify the data
is linked.
- When printing, choose "all rows data source" as the option, but
only after printing a single page first to verify the alignment.
- When linking to the CSV or Text files, the format should be as follows:
CSV: "Column1","Column2","Column3"
TEXT: Column1,Column2,Column3
- Print and scan the barcode(s) to verify the correct data is encoded.
If a scanner is needed to verify barcodes, IDAutomation offers several
hand-held USB Barcode Scanners.
©
Copyright 2001-2007 IDAutomation.com, Inc., All Rights Reserved. Microsoft
and the Office logo are trademarks or registered trademarks of Microsoft
Corporation in the United States and/or other countries. All
other trademarks
mentioned are the property of their respective owners.
Additional Legal
Notices.
|
Over 70% of Fortune 100
companies use IDAutomation's products to automate their businesses.
|
|