This page describes how to create and configure MS Excel for use with Barcode Alpha. This process consists of three main procedures:
1. Prepare Excel workbook.
2. Create Data Source connection (ODBC).
3. Setup Barcode Alpha to Excel connection and label elements.
In order to use Excel as a data source for printing barcodes, following convention must be used:
1. First row contains column names.
2. Sheet name should start with letter and can contain only alpha numeric characters (a-z, A-Z, 0-9).
Example:
Sheet name: Products
Column names: PRODUCT, BARCODE and PRICE
Save the workbook on the hard drive. In this example we will save it the c:\barcode\datasource.xls
Click Start > Settings > Control Panel on the Windows menu.
The Control Panel window appears:
Double-click Administrative Tools on the Control Panel window.
The Administrative Tools window appears:
Double-click Data Sources (ODBC) on the Administrative Tools window.
The ODBC Data Source Administrator window appears:
Click the System DSN tab and click the Add button:
Select "Microsoft Excel Driver (*.xls)" from the list and then click Finish. The ODBC Microsoft Excel Setup dialog box appears.
Enter a name in the Data Source Name field. In this example "excel2barcode" is entered.
Click the Select Workbook button in the Database group box.
The Select Workbook dialog box appears:
Find the Excel workbook that was previously prepared and click OK. (In this example: C:\barcode\datasource.xls)
To complete ODBC setup, Click OK on the ODBC Setup dialog box.
Click the New button and the Edit Page size/Label stock dialog box appears:
Select paper size, label size and number of labels per page (on the image above, Avery 5160 size is selected - in millimeters).
Click on OK button to complete paper and label size setup. The Label Designer dialog box appears.
Since we need to setup database connection before specifying label design, click OK button to dismiss Label Designer window.
The Label Designer dialog box can be invoked from the toolbar icon "Design Sticker Layout" when needed.
You are returned to the Barcode Alpha main window. Click on "Database connection wizard" icon on the toolbar.
The Data provider connection wizard dialog box appears:
Enter a name of data source in the DB field. This is the name of data source previously explained in the Create Data Source connection (ODBC) topic.
In this example "excel2barcode" is entered.
Click Connect button to test connection. If the ODBC data source is specified correctly, you will notice "Success!!!" message in the lower left corner of the Data provider connection wizard dialog box.
Click Next to specify table name and source columns. The next page of the Data provider connection wizard dialog box appears.
MS Excel uses a specific syntax for table names so we need to check Expert mode check box and to specify SQL select manually.
General syntax is: SELECT (column names) FROM [TABLE NAME$]
Note that MS Excel expects TABLE NAME to be followed by a $ sign!
Example: Select product,barcode,price from [Products$]
Click Next to verify SQL query. If the SQL query is valid you will notice appropriate message in the message in the lower left corner of the Data provider connection wizard dialog box. If the query is not valid, a helpful message will be displayed. Please don't hesitate to contact us to help you setup ODBC connection or any other part of the Barcode Alpha software.
Click Finish button to activate Barcode Alpha to Excel connection. Barcode Alpha is now ready to use MS Excel as a data source for printing barcodes.
You are returned to the Barcode Alpha main window. Click on "Design sticker layout" icon on the toolbar to specify barcode and text position on a label.
The Label Designer dialog box appears.
Using Split vertical / Split horizontal buttons, area of a label can be divided in the separate sections (cells) and in each of those sections, barcode, text or image can be printed.
In this example we will split label in the three horizontal sections.
Click on the label view and click on the Split Horizontally button twice. You will notice that label area is divided in the three sections separated by a divider line.
You may click-drag divider line to adjust size of the label sections.
Click on the top section to select it and then click on Add Barcode to specify barcode type which will be printed in this area of label.
The Sticker Section Property Designer dialog box appears.
Click on the Retrieve data from database radio button to specify that barcode data is a column in Excel.
Click on Column list box and scroll down the list until you find the Excel column that you wish to use as a data source for printing barcodes.
In this example selected column is BARCODE which contain barcode numbers.
Click on BarCode type list box and scroll down the list to find desired barcode type. In this example EAN13 is selected.
Note that some barcode types support only numbers, some support both alpha-numeric characters.
For more details about barcode types and supported chars please visit http://www.barcodealpha.com/symbols.htm.
Click OK button to confirm barcode setup. You are returned to the Label Designer dialog box.
Click on the middle section of the label to select it and then click on the Add Text button to define text that will be printed on this part of the label.
The Sticker Section Property Designer dialog box appears.
Click on the Retrieve data from database radio button to specify that text area data is a column in Excel.
Click on Column list box and scroll down the list until you find the Excel column that you wish to use as a data source for printing text part of a label.
In this example selected column is PRODUCT which contains product name.
You may specify font size and other parameters.
Click OK button to confirm text element setup. You are returned to the Label Designer dialog box.
Click on the bottom section of the label to select it and then click on the Add Text button to define text that will be printed on the bottom section of the label.
The Sticker Section Property Designer dialog box appears.
Click on the Retrieve data from database radio button to specify that text area data is a column in Excel.
Click on Column list box and scroll down the list until you find the Excel column that you wish to use as a data source for printing text part of a label.
In this example selected column is PRICE which contains product price.
You may specify font size and other parameters.
Click OK button to confirm text element setup. You are returned to the Label Designer dialog box.
Click on OK button to confirm label design and to see a preview page displayed on the Barcode Alpha main window.
