How to integrate Labeljoy into your data management software
If you want to add label printing to an existing software infrastructure, whether it’s a simple Excel file or an enterprise ERP system, here are some guidelines on how to integrate Labeljoy into your desktop application setup.
1 – The basics: a print shortcut
First, if you have already created your labels and all you need to do is print them from time to time, you don’t need start Labeljoy each time.
The easiest way to print a Labeljoy file without firing up the program is to use the Print with labeljoy context menu. Just right-click the file and select the second menu item:
In reality, this operation does start Labeljoy but just to process the printing operation. The program then quits.
The method described above requires at least two mouse clicks, let’s see how we can further speed up this operation by creating a Print shortcut.
We need to locate the Labeljoy executable program file Labeljoy5.exe. Its location may vary depending on what was entered during setup. If you left the default location untouched, you should be able to find it under C:\Program files\Labeljoy (or C:\Program files (x86)\Labeljoy for 64 bit systems):
Note that the .exe extension might not be visible depending on your Windows settings.
Right click the Labeljoy program file and select Copy:
Now go to the desktop, right click on an empty area and select Paste shortcut:
You have just created a shortcut that when clicked will start Labeljoy. Let’s see how to turn this into a print shortcut.
Right click the newly created shortcut and select Properties:
This is how the shortcut properties window should look like:
We need to modify the text in the Target text box. We need to add the prefix /P and the full path and name of the Labeljoy file we want to print. For example, if the file is located on the desktop of the Windows user Henry, our resulting string will be:
“C:\Program files\LabelJoy\Labeljoy5.exe” /P “C:\Users\Henry\Desktop\My label.lpa”
What this shortcut is doing is telling Windows to open the File C:\Users\Henry\Desktop\My label.lpa with Labeljoy.
But it’s also telling Labeljoy to print the file and close.
You now have a 1-click print shortcut for your most often used labels.
It might be a good idea to also rename your shortcut to something meaningful to you.
2 – Printing data connected labels
Labeljoy enables you to display data on labels read from external data sources, such as Excel files, Access files or professional database systems such as SQL Server, Oracle, or MySql. Please refer to this video tutorial to find out all the details.
If you want your labels to be updated before they are printed, you need to activate a special option found in the Group settings form. It’s the Update data when this file is loaded or printed option:
What this option does is re-load the data whenever the file is loaded or before it’s printed. The data will be reloaded using the parameters entered during the first connection, so in the case above, the file C:\Logistic label.xlsx must still exist and the sheet Logistic labels must also be there or else data updating will fail.
This option enables you to always print labels with the latest data available. So if the print shortcut we created earlier points to a data connected – auto updating Labeljoy file, all you have to do is click it when new data is available for printing.
IMPORTANT: Labeljoy saves the name of the printer used to print each file. This means that the printer used to print the file when doing a 1-click printing operation is always the one used the last time the Labeljoy file was edited.
3 – Excel integration using VBA
If the data to be printed resides in an Excel file, by tapping into the power of the Visual Basic for Application (VBA) programming language we can create a shortcut within the very same Excel file to print the labels.
This requires some programming skills and some knowledge of the VBA environment.
For this we’ll use a variation of the files created in the GS1-128 tutorial.
We’ll assume that both files are located under the root directory of the C drive, C:\.
Download this file
First, open the Labeljoy file and ensure that the Update data when this file is loaded or printed options is activated, as described in the previous chapter.
If you saved the linked Excel file in a location other then the root of the C: drive, make sure you update the location in Group settings/Data source.
Save the Labeljoy file and close Labeljoy.
From this moment on we can forget about Labeljoy, we only need to worry about the data to be printed.
The Excel file contains a single row of data and a button linked to a VBA Macro:
Let’s open the Excel file and open the VBA editor by pressing ALT+F11 to take a look of what’s going on behind the scenes:
In the modLabelPrinting module you’ll find a single routine named LabelPrint. It’s a simple macro that first saves the Excel workbook and then executes the Labeljoy file for printing. This last step is achieved through the use of the ShellExecute Windows API function (more information on the MSDN website).
Again, if you saved the Labeljoy file Software integration.lpa in a location other then the root of the C: drive, make sure you change the following line accordingly:
sLabelJoyFileNameAndPath = [Enter here the correct file location enclosed in quotation marks]
So when the Save and print button is clicked, the Macro is executed: the Excel file is saved and the Labeljoy file is first updated and then printed.
Try changing the data in the Excel file and click the button to see the resulting printed labels.
4 – Integration in an ERP system
If your company manages its data in an ERP system developed and maintained internally, you can apply the previous approach by writing a corresponding routine using the programming language of your choice.
The only requirement is that Labeljoy is installed on the client machine where label printing takes place.
Following is a sample application written in VB.NET (version 2010, frameworks 2.0 and 3.5). The code was intended to run on a machine where Labeljoy 5 is installed.
This sample requires a basic knowledge of the VB.NET programming language, along with some ADO.NET familiarity.
When the application is run the first time, it copies two files that are stored in its resource area:
- Logistic label.mdb. An Access database file containing two tables: The LogisticLabel table used to hold the data that will be printed. The ISO3166 table which contains country data identifiers, needed to properly handle the fields CountryCode and CountryPrefix.
This file is saved in the Data folder under the Labeljoy install directory (i.e. C:\Program files\Labeljoy 5\Data). If the folder doesn’t exist, it will be created.
- Logistic label.lpa. The Labeljoy file to be printed. This file is saved in the Labeljoy document folder (C:\Users\UserName\Documents\Labeljoy). This file is data connected to the LogisticLabel table of the mdb file.
The application is made up of a single form, used to gather the necessary data to be printed on the logistic label:
Once the form has been filled with the data we want to print, the user clicks the Print button.
The data entered is first validated in the CheckData function. If any incoherence is found, a message box is displayed. Printing won’t be performed unless all errors have been resolved. See in line comments for more details:
Once the data has been validated, it is saved in the LogisticLabel table of the mdb file via the UpdateData function. As you can see in the code a Delete statement is always issued before the new data is stored as for this example we only need one line of data in the source table:
Next, by using the ShellExecute Windows API approach, the label is then printed:
The file Logistic label.lpa is loaded into Labeljoy and data update takes place reading the newly inserted data from the mdb file. Then printing takes place and Labeljoy shuts down.
Note that the data update process is also made possible by the exploitation of a hidden Labeljoy functionality: when a Labeljoy file is linked to a data source originally stored inside Labeljoy’s install directory (or any sub-directory), subsequent data update calls will always refer to a location within the install directory, even if it’s not the same exact path as originally.
For example, suppose you link a Labeljoy file to a data source in C:\Program files\labeljoy 5\Data where C:\Program files\labeljoy 5 is the install directory. You then move the Labeljoy file and the data file to a different PC. This time, since it’s a 64 bit Windows version, the install directory is C:\Program files (x86)\labeljoy 5. You put your data file under C:\Program files (x86)\labeljoy 5\Data. You then open the Labeljoy file and launch data updating: Labeljoy recognize that the original data source was found under [INSTALLDIR]\Data and adapts the path according to the install directory of the current machine.