IDAutomation.com, Inc., Computers Hardware, Software & Services, Tampa, FL Contact IDAutomationView your cart in the IDAutomation Store  
Loading
Home > Font Encoders > OpenOffice for Base, Calc and Writer

Apache OpenOffice Tutorial

This tutorial describes how to use IDAutomation's barcode fonts in Apache OpenOffice 4.0. The solutions described here are compatible with Star Office 8 and Open Office 2.0 through current.

Download macros (includes VB source code and examples)

Prepare OpenOffice for the Use of Barcode Fonts

The following will prepare OpenOffice applications such as Calc, Base or Writer for use with IDAutomation's barcode fonts.

  1. The default method OpenOffice uses to display font names is to list them in their own font as a preview. This works well for text fonts; however, barcode fonts viewed this way make it difficult to pick the correct option. To allow viewing of barcode font names in Calc, Base or Writer choose Tools - Options - View, deselect the "Show Preview of Fonts" check box and choose OK. The program will need to be closed and reopened for this change to take effect.
    Prepare Calc, Base or Writer for use with barcode fonts.
  2. The additional items below do not have to be performed when using the Code 39 Barcode Fonts. If a checksum is required for Code 39 or another barcode type is needed, perform the steps below to install the macros. These macros will reformat the data to the barcode fonts and handle any required checksum calculations.
  3. By default, the Macro Security Level is set to Medium, which gives the user the option to confirm if a macro is to run.  To verify this setting choose Tools - Options - Security - Macro Security and to confirm the level is set to Medium and choose OK.
    set macro security
  4. Download and extract the IDAutomationVB.bas file.
  5. Choose Tools - Macros - Organize Macros - OpenOffice.org Basic.
  6. In the macro from area, click on the name of the desired file and choose New. This will embed the macros and Visual Basic source code into the file.
    embedding bar code macros into the Calc file
  7. Remove the Sub Main and End Sub lines that are automatically added.
    remove sub
  8. Click on the Insert Basic Source button and choose the file IDAutomationVB.bas provided in the download.
    insert basic source
    This will automatically add the code into the editor window.
  9. Choose File - Save and File - Exit to exit the VB editor.
  10. Shut down all OpenOffice applications and install the IDAutomation barcode fonts. If unsure of what barcode font type to use, IDAutomation recommends the Code 128 Barcode Fonts.

Barcode Creation in Calc

  1. If the tasks in the Prepare OpenOffice section have not been completed, do so now.
  2. When opening the spreadsheet with the macros imported, a Security Warning will appear, choose "Enable Macros".
    Enable Macros
  3. Open Calc and Create an area in the spreadsheet column for the barcode. Video Tutorial
  4. Size the column to make sure it is wide enough to contain the entire barcode.
  5. Format the column so that any text appearing in it will be centered. This is necessary to create the white space (often called the quiet zone) before and after the barcode.
  6. Enter the formula in the cell that will format the data to the barcode font - this is where the barcode will appear. For use with the Code 39 font, enter the formula of ="*" & A2 & "*" in the cell.
    Enter Code 39 formula.

  7. If unsure of which function or barcode to use, IDAutomation recommends using the Code 128 fonts, using the formula =Code128(A2;0;0) where A2 refers to the cell location of the data to be formatted and encoded in the barcode.
    code128 calc barcode

    After entering the formula, the modified text that is to be formatted and encoded for the barcode font should appear in the cell with the formula.
    barcode in calc

  8. Apply the applicable barcode font to the cell and size the cell so there is at least 4 times the white space before and after the barcode based on the thickest bar in the barcode.
    Center the barcode and widen the column.
  9. To create barcodes for other cells in the column, select the original cell and choose Edit - Copy and select the new cell or cells, then choose Edit - Paste.
  10. To allow for easier scanning of barcodes in cells, select the bar-coded cells and choose Format - Cells - Borders and set the spacing to 4 points.
    formatting the barcoded cells

Barcode Mail Merge Creation in Writer

To create barcodes in an OpenOffice Writer mail merge using macros, it is necessary to use Calc as the data source. The field used for the data source in Writer should be the column in Calc where the formula has been applied, which is used to format the data to the barcode font. IDAutomation suggests using the last column of the spreadsheet in order to avoid any merging problems. To set up the Calc spreadsheet with the macros, please refer to the Calc Tutorial.

  1. In order to import the encoded data calculated in the Calc spreadsheet to Writer, the Calc file will need to be saved in dBASE (.dbf) Format. The .ods file will not retain this encoded information, the cells will only show a "0" in the encoded field. If the following pop up is received, click "Keep Current Format" to continue saving in dBASE format.
    Save in dBASE format
    If you receive an error message that a JRE must be installed to proceed, visit OpenOffice.org for download information.
  2. When asked what character set to save the DBase Export, select Western Europe (Windows-1252/WinLatin 1).
    Select Western Europe (Windows-1252/WinLatin 1)
  3. To begin the mail merge go to Tools - Mail Merge Wizard.

  4. First, select the starting document for the Mail Merge. In this example, the current document will be used. Click Next.
    start mail merge
  5. The following screen will default to Letter. Click Next.
  6. On the Insert Address Block screen, click Select Different Address List. The next screen will list the available Calc spreadsheets available to use.
    address list
  7. Select the Name and Table from the list provided or choose data from a different location by clicking Add. A new table may also be created by selecting Create. Once the selection has been made, click OK.
    select calc sheet
  8. On the Insert Address Block screen, select "This document shall contain an address block" and click "Match Fields".
    select merge fields
  9. Start with the first 'Address elements' field and match the fields wanted to the spreadsheet attached. Click OK.
    match fields
  10. Now click on More and select Edit.
    arrange elements
  11. Remove the address elements not mapped to the spreadsheet. Click OK twice, then click Next.
    Remove empty fields in the Address Block
  12. The salutation should be disabled on the next screen.
    Disable Salutation
  13. Skip Adjust Layout because it will be easier to move the barcode area on the next Edit Document screen, where the text box created may be manually aligned on the original Writer document.
  14. Edit Document will open a page with the frame example. This will be the time where the barcode font will be applied to the encoded data as well as adding any additional information to the document such as data fields if needed.
    Highlight the encoded Data
    Highlight the encoded data and apply the barcode font.
    Apply the barcode font.
  15. Click Return to Mail Merge Wizard and the mail merge will be completed.
    Return to Mail Merge Wizard
    If specific notes need to be applied to individual pages in the merge, the Personalize Document screen will allow users to add notes to single pages.
  16. The Print screen will save the document or print out directly.
  17. After the merge is performed, print and scan the barcode(s) to verify the correct data is encoded. If a scanner is needed to verify barcodes, IDAutomation suggests the easy-to-use USB Barcode Scanners.
  18. When distributing the mail-merge, the font should be embedded in Writer and the Calc data source must be accessible.

Barcode Creation in Base

At this time, the only barcode that can be encoded accurately in Base is Code 39. The database should be created in Calc and then saved as a dBASE (.dbf) Format as outlined in Step 1 above.

  1. Using the file created in Calc, verify that the file has been saved with the encoded text string not having the barcode font applied yet.
    Encoded Data Saved in Calc File
  2. Open Base and the Database Wizard, select "Connect to an existing Database" and then "Spreadsheet" from the dropdown menu:
    Choose Spreadsheet from existing Database options
  3. Select ODF Spreadsheet and the one that the macro barcode data resides, select Next and choose "Yes, register the database for me" on the final screen of the Database Wizard, as well as "Open the database for editing" and save the ODF Database being created.

  4. Base will open the main screen with the new database data embedded.
  5. Click on Reports on the left panel and click on "Use Wizard to Create Report", then choose the fields for the report, update the Label names if desired. Options for Grouping, Sorting and Layout follow to choose.
  6. Open the report to verify that the data is complete and accurate:
    Verify Data in Report
  7. Close out the preview and go back to the main report window. Right Click on the Report and select Edit.
    Edit Report
  8. Highlight the text below the Barcode Column heading and apply the IDAutomation Code 39 Font. Center the barcode so that there is enough white space on each side of the barcode and increase the font size if needed:
    Apply Barcode Font
  9. Close the file and reopen the report in preview mode to see the barcode:
    Barcode Applied to Database Report

The Following Barcode Macros are Available:

The barcode macros listed below are supplied in the file named "IDAutomationVB.bas" which is provided in the download package.

For all functions listed below, the DataToEncode is the string data type. Other data types, such as numbers or dates, may need to be converted to the string data type to be properly encoded. All parameters are required. For example, use Code128("123456"; 0; 0) instead of Code128("123456"). For all number symbologies, such as POSTNET and UPC, dashes or spaces may be imputed for easier readability, the dashes and spaces will be filtered out before the barcode is generated. For UPC-A, UPC-E & EAN-13, the +2 and +5 add-on codes may be created by adding the data to the end of the string.

Function Font to Use Notes
Code128(DataToEncode;
ReturnType;
ApplyTilde)
IDAutomationC128 If unsure which Code 128 set is for an application, then use this one. This "Code 128 Auto" function that will automatically encode any DataToEncode from ASCII 0 to ASCII 127. It will automatically switch to character set C for numbers also. Data may be easily encoded without any options. For example, the formula Code128("123456") will encode the numbers 123456.

It may be necessary to use the ReturnType and ApplyTilde for special purposes.

  • If ApplyTilde is set to True, the tilde will be processed as described here. ApplyTilde is False by default.
  • To encode alpha-numeric UCC/EAN-128, ASCII 202 or character is entered as the FNC1 before each AI. For example, the UCC number of (8100)712345(21)12WH5678 should be entered as: 81007123452112WH5678.
    More information about AIs and UCC/EAN-128 is located here.
    (DataToEncode; 0; 0) formats barcode output string to the Code 128 barcode fonts.
    (DataToEncode; 0; 1) formats barcode output string for the Code 128 barcode fonts with ApplyTilde enabled.
    (DataToEncode; 1; 1) returns the human readable text.
    (DataToEncode; 2; 1) returns only the check digit.
Code128a(DataToEncode) IDAutomationC128 Caution: Entering a lower case character will create a function. Use the character values 64 through 95 from set A to print characters not on the keyboard; the scanner will recognize these functions as from set A. Formats output to the Code 128 barcode fonts.
Code128b(DataToEncode) IDAutomationC128 Returns codes formatted to the Code 128 character set B. Formats output to the Code 128 bar code fonts.
Code128c(DataToEncode;
ReturnType)
IDAutomationC128 This code128 function "interleaves" numbers into pairs for high density.
(DataToEncode; 0) formats output to the Code 128 barcode fonts.
(DataToEncode; 1) returns the human readable text with the check digit included.
(DataToEncode; 2) returns only the check digit.
Code39(DataToEncode) IDAutomationC39
or
IDAutomationHC39
Formats the output to print using Code 39 fonts.
Code39Mod43(DataToEncode;
ReturnType)
IDAutomationC39
or
IDAutomationHC39
(DataToEncode; 0) performs the mod43 checksum calculation for increased accuracy and then formats the output to print using Code 39 fonts. The mod43 checksum is usually required for LOGMARS and HIBC applications.
(DataToEncode; 1) returns the human readable data with the check digit included.
(DataToEncode; 2) returns only the check digit.
Code93(DataToEncode) IDAutomationC93 Formats the output to print with the 2 required check digits using Code 93 fonts.
Codabar(DataToEncode) IDAutomationCB Formats the output to print using Codabar fonts.
Code11(DataToEncode) IDAutomationC11 Formats output to the Code11 barcode fonts. Only the "C" check digit is calculated which is the standard when encoding 10 digits or less.
EAN13(DataToEncode) IDAutomationUPCEAN DataToEncode is a number string of 12, 13, 14, 15, 17 or 18 digits with or without a check digit, add-ons are supported. Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing all zeros.
EAN8(DataToEncode) IDAutomationUPCEAN DataToEncode is a number string of 7 or 8 characters (EAN-8 without the check digit). Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing all zeros.
I2of5(DataToEncode) IDAutomationI25
or
IDAutomationHI25
This function "interleaves" numbers into pairs for high density without check digits and formats the return string to the Interleaved 2 of 5 font.
I2of5Mod10(DataToEncode;
ReturnType)
IDAutomationI25
or
IDAutomationHI25
(DataToEncode; 0) performs the mod10 checksum calculation for increased accuracy and formats the return string to the Interleaved 2 of 5 font. MOD 10 checksums are required by USPS for special services and for SSC-14 when using Interleaved 2 of 5 for that purpose.
(DataToEncode; 1) returns the human readable data with the MOD10 check digit included.
(DataToEncode; 2) returns the MOD10 check digit.
MOD10(DataToEncode) not applicable Returns the MOD 10 check digit for a given string of numbers according to the UCC/EAN method.
MSI(DataToEncode;
ReturnType)
IDAutomationMSI
or
IDAutomationHMSI
(DataToEncode; 0) formats output to the MSI barcode fonts.
(DataToEncode; 1) returns the human readable data with the check digit included.
(DataToEncode; 2) returns only the check digit.
Postnet(DataToEncode;
ReturnType)
IDAutomationPOSTNET
or
IDAutomationPLANET
Enter a single string of Zip, Zip + 4 or Zip + 4 + Delivery Point or any number of digits for the planet code. The DataToEncode must be a number and can include dashes and spaces.
(DataToEncode; 0) formats output to the POSTNET barcode fonts.
(DataToEncode; 1) returns the human readable data with the check digit included.
(DataToEncode; 2) returns only the check digit.
This function will not create the Intelligent Mail Barcode.
RM4SCC(DataToEncode) IDAutomationRM Formats the output to print using RM4SCC fonts. Checksum calculated according to the mail sort customer bar-coding specification.
SpliceText(DataToEncode;
SpacingNumber; ApplyTilde)
Text font Returns text with a space inserted every SpacingNumber of digits to improve readability. If ApplyTilde is set to True, the tilde will be processed as described here.
UCC128(DataToEncode) IDAutomationC128 This symbology option calls the Code128() method with the Tilde option enabled and the FNC1 character in set C as required. It is often an easier method to use when encoding an even number of number digits. For example, to encode an SSCC-18 barcode, enter 00000123455555555558 as the data input. For more information, please visit here. Formats output to the Code 128 barcode fonts.
UPCa(DataToEncode) IDAutomationUPCEAN DataToEncode is a UPC-A number string of 11, 12, 13, 14, 16 or 17 digits with or without a check digit, add-ons are supported.  Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing all zeros.
UPCe(DataToEncode) IDAutomationUPCEAN DataToEncode is a UPC-E number of 6, 7 or 8 digits or a UPC-A number string of 11, 12, 13, 14, 16 or 17 digits with or without a check digit, add-ons are supported only with 13, 14, 16 or 17 digits. Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing "00005000000". UPC-E1 is supported when the first character is "1" instead of "0".
Functions for the Universal Barcode Font
All functions listed below are preceded with IDAutomation_Uni_. They may only be used with the IDAutomation Universal Barcode Font Advantage™ and are embedded in the IDAutomationUniversalOpenOfficeVBExample.ods spreadsheet provided within the Universal package.
Barcode Type Universal Barcode Function Methods and Notes
Code-128 IDAutomation_Uni_C128 (DataToEncode; ApplyTilde)

Note: Code128() is the recommended method to use. Code128() is also used to create GS1-128.

IDAutomation_Uni_C128A (DataToEncode)
IDAutomation_Uni_C128B (DataToEncode)
IDAutomation_Uni_C128C (DataToEncode)
IDAutomation_Uni_UCC128 (DataToEncode)

Example:
IDAutomation_Uni_C128 ("81007123452112WH5678"; 1)

Excel for Mac:
idautomation_uni_c128 ("81007123452112WH5678"; 1)


Required Font: IDAutomation_Uni
not applicable IDAutomation_Uni_C128HR (DataToEncode; ApplyTilde)
Returns text for Code 128 barcodes, such as for GS1-128.

Required Font: Text Font
Code-39 IDAutomation_Uni_C39 (DataToEncode; N_Dimension; IncludeCheckDigit)

Example:
IDAutomation_Uni_C39 ("123456789"; 3; 1 )

Excel for Mac:
idautomation_uni_c39 ("123456789"; 3; 1 )


Required Font: IDAutomation_Uni
Codabar IDAutomation_Uni_Codabar (DataToEncode; N_Dimension; CodabarStart; CodabarStop)

Example:
IDAutomation_Uni_Codabar ("123456789"; 3;"A";"B" )

Excel for Mac:
idautomation_uni_codabar ("123456789"; 3;"A";"B" )


Required Font: IDAutomation_Uni
Interleaved 2 of 5 IDAutomation_Uni_I2of5 (DataToEncode; N_Dimension; IncludeCheckDigit)

Required Font: IDAutomation_Uni
MSI / Plessey IDAutomation_Uni_MSI (DataToEncode; N_Dimension; IncludeCheckDigit)

Required Font: IDAutomation_Uni
USPS Postnet IDAutomation_Uni_Postnet (DataToEncode; IncludeCheckDigit)

Required Font: IDAutomation_Uni
USPS Planet IDAutomation_Uni_Planet (DataToEncode; IncludeCheckDigit)

Required Font: IDAutomation_Uni
SpliceText IDAutomation_Uni_SpliceText(DataToFormat; SpacingNumber; ApplyTilde)
MODU10 MODU10(M10NumberData)
ProcessTilde IDAutomation_Uni_ProcessTilde(StringToProcess)

Back to Top

 © Copyright 2014 IDAutomation.com, Inc., All Rights Reserved. Legal Notices.

 Barcode Fonts | Components | Scanners | Support | Contact Us
Training Videos on YouTube Join us on Google Plus Join us on LinkedIn Follow us on Twitter Like us on Facebook
Over 70% of Fortune 100 companies use IDAutomation's products to automate their businesses.