CorelDraw & VBA
Summary & Introduction
Guide to CorelDraw VBA
Creating Variable Data
Variable Data From Access
Variable Data From Excel
Variable Data Without a Data File
CorelDraw BarCode Wizard
EAN13 BarCodes Without The Wizard
Code 39 BarCodes Without The Wizard
ITF-14 BarCodes Without The Wizard
Code 128 BarCodes Without The Wizard
QR BarCodes
Variable Pictures
Sorting for Guillotining
Repositioning Data
Pantone Colors
Saving VBA Code to a Previous Version of CorelDraw
Trim Marks
PhotoPaint
Miscellaneous VBA
Help
 
Creating Variable Data


Sometimes you want numbers of fixed width so you want numbers with preceding zeros such as 000xxxx. Excel will normally dismiss the zeros and record only xxxx where the first x from the left represents the first non-zero. eg If you type 000000405670 Excel will record 405670. It deletes all the zeros to the left of the first non-zero digit. There are two solutions.

1. Specify that the format of the cell is to be text before entering the data. Select the cell you wish to enter a number in then in the toolbar select "Format", "Cells", "Text". Now enter the number with preceding zeros and the number will appear as text with zeros at the start. Then copy the trend down the page.

2. Specify that the format is a custom format. Select the cell you wish to enter a number in then in the toolbar select "Format", "Cells", "Number", "Custom" and then in the box under the word "Type:" insert your format. eg "000##" This will ensure that the displayed number has at least 5 digits and if it needs extra digits it will add zeros to the front of the number. Then copy the trend down the page.

For variable numbers beginning with one or more letters, Excel is smart enough to continue the trend. eg HG0458 will trend down such that the next number is HG0459 and so on.


Excel in Office 2000 & 2003, and maybe other versions, does not allow the use of numbers with more than 15 digits. This is usually enough for barcodes but sometimes you need more than 15 digits. Numbers of more than 15 characters cannot be stored in access or excel but can be stored as text. Excel will not fill empty cells if the text consisting of more than 15 characters. For numbers, Excel will incrementally fill empty cells if you drag the bottom right hand corner of a selection of adjacent cells that exhibit a incremental trend. For text, Excel will not incrementally fill empty cells if you drag the bottom right hand corner of a selection of adjacent cells that exhibit a incremental trend.

If you need to generate numbers with more than 15 digits in Excel consider saving the last 15 digits in a cell & the remainder, as though they are a prefix, in another cell You can either use the 2 data fields as they are or concatenate to automatically create text. This same method can be used for up to 32767 alpha-numeric characters in each cell of Excel 2000 by combining multiple cells. This should be more than enough for any variable data. The diagram below shows what I mean. The second column shows the last 15 or less digits of the number. The first column contains what digits will not fit into the second column. In the third column is the concatenated data, that is the result of using "&" in a formula. The "&" function combines two pieces of text.

Prefix
Varying Portion
18 Character Number Using the Formula (=A8&B8)
123
456789012345678
123456789012345678
123
456789012345679
123456789012345679
123
456789012345680
123456789012345680
123
456789012345681
123456789012345681
123
456789012345682
123456789012345682
123
456789012345683
123456789012345683

Here is the actual Excel file called "Numbers_with_more_than_15_digits.xls" that contains the table above.



2015_08_06