Friday, June 13, 2014

Barcode and QR-Code Generator Using Excel and Latex

Background

For a document scanning project I needed to find a (cheap) way to create barcode and QR-code labels to stick on the documents to scan. This blog post bescribes a Latex based Excel-driven label generator which is able to create sticky labels on A4-pages commonly available on the market.

System Requirements

I was working in the following system environment:
  • Windows 7
  • Office / Excel 2010
  •  Miktex 2.9

Latex Requirements

To generate bar- or QR-codes with the following code examples you need to have the following Latex packages installed:
  • pstricks
  • pst-barcodes
  • makecell
  • newdimensions
  • lmodern
Note: the Latex-code generated by the code generator will not compile with pdflatex because of the usage of pstricks. Use the command "xelatex" which is coming with the Miktex installation.

Output

Barcodes 70 x 37 mm

The barcode generator creates the following pdf-file which is formatted to fit on an standard A4 3 x 7 labels page

QR-Codes 38 x 21,2 mm

the QR-codes can be produced either as 3 x 7 labels per sheet (label size: 70 x 37 mm) or as 5 x 13 labels per sheet (label size: 38 x 21,2 mm). Here an example of the latter case:

Rough Architecture

In an Excel workbook you can define document names / IDs. These IDs are being read by a latex code generator (VBA macro) which creates either an output file for barcodes or an output file for QR-codes based on the IDs of the barcodes. This is due to the fact that we are using barcodes to label document folders and QR-codes to label the documents in these folders...
As soon as the output latex-file is written to the hard disk, the VBA macro calls a Windows batch file calling the xelatex-compiler with the newly created output file. Xelatex compiles the *.tex-file and generates a PDF in the end which can be printed.

Latex Files

Barcodes.tex

The file Barcodes.tex is the main file for the generation of barcodes. It only contains the definition of formats, etc. to generate the desired output (pages with 3 x 7 labes of size 70 x 37 mm). It calls a latex file "labelfile.tex" whic contains the actual label definitions.

\documentclass[a4paper,11pt]{article}
\usepackage{pst-barcode}
\usepackage[newdimens]{labels}
\usepackage[T1]{fontenc}
\usepackage{lmodern}
\renewcommand*\familydefault{\sfdefault}
\LabelCols=3
\LabelRows=7
\LeftPageMargin=5mm% These four parameters give the
\RightPageMargin=10mm% page gutter sizes. The outer edges of
\TopPageMargin=5mm% the outer labels are the specified
\BottomPageMargin=5mm% distances from the edge of the paper.
\InterLabelColumn=25mm% Gap between columns of labels
\InterLabelRow=5mm% Gap between rows of labels
\LeftLabelBorder=5mm% These four parameters give the extra
\RightLabelBorder=5mm% space used around the text on each
\TopLabelBorder=5mm% actual label.
\BottomLabelBorder=5mm%

\begin{document}
\include{labelfile}
\end{document}


labelfile.tex

File "labelfile.tex is generated by the VBA macro "MakeBarcodesForFolders" (code see below) and has the follwoing structure:

\begin{labels}

GSI-GF/K-
\begin{pspicture}(2,0.5in)\psbarcode[scalex=0.7,scaley=0.4,transy=3mm]{ARC-00001}{}{code39}\end{pspicture}
\vspace{-9mm}
\bf{ARC-00001}

GSI-GF/K-
\begin{pspicture}(2,0.5in)\psbarcode[scalex=0.7,scaley=0.4,transy=3mm]{ARC-00002}{}{code39}\end{pspicture}
\vspace{-9mm}
\bf{ARC-00002}

GSI-GF/K-
\begin{pspicture}(2,0.5in)\psbarcode[scalex=0.7,scaley=0.4,transy=3mm]{ARC-00003}{}{code39}\end{pspicture}
\vspace{-9mm}
\bf{ARC-00003}

GSI-GF/K-
\begin{pspicture}(2,0.5in)\psbarcode[scalex=0.7,scaley=0.4,transy=3mm]{ARC-00004}{}{code39}\end{pspicture}
\vspace{-9mm}
\bf{ARC-00004}

[...]

\end{labels} 


QR-CodesMain.tex 

Similar to "Barcodes.tex", "QR-CodesMain.tex" contains information concerning the formatting options for the desired output. QR-CodesMain.tex is generated by VBA macro "createMainFile" (code see below) to avoid the neccessity to adjust this file manually if you want to switch from label size 70 x 37 mm to 38 x 21,2 mm. QR-codesMain.tex looks as follows for labels of size 38 x 21,2 mm (5 x 13 labels per sheet):

\documentclass[a4paper,11pt]{article}
\usepackage{pst-barcode}
\usepackage[newdimens]{labels}
\usepackage[T1]{fontenc}
\usepackage{lmodern}
\usepackage{makecell}
\renewcommand*\familydefault{\sfdefault}
\renewcommand\cellalign{lt}
\LabelCols=5
\LabelRows=13
\LeftPageMargin=3mm% These four parameters give the
\RightPageMargin=5mm% page gutter sizes. The outer edges of
\TopPageMargin=10mm% the outer labels are the specified
\BottomPageMargin=5mm% distances from the edge of the paper.
\InterLabelColumn=-1mm% Gap between columns of labels
\InterLabelRow=1mm% Gap between rows of labels
\LeftLabelBorder=0mm% These four parameters give the extra
\RightLabelBorder=0mm% space used around the text on each
\TopLabelBorder=0mm% actual label.
\BottomLabelBorder=0mm%
\begin{document}
\include{QR-Codes}
\end{document}\begin{document}
\include{QR-Codes}
\end{document}

QR-Codes.tex

The Latex file"QR-codes.tex" is generated by VBA macro "Button_Weiter_Click()" and has the following structure:

\begin{labels}

\begin{tabular}{ll}
\makecell[{{p{14mm}}}]{\begin{pspicture}(0,0in)\psbarcode[]{GSI-GFK-ARC-00003.001}{}{qrcode}\end{pspicture} \\ ~ \\ ~}
& \makecell[b]{GSI-GFK- \\ ARC-00003 \\ .001 \\ ~}
\end{tabular}

\begin{tabular}{ll}
\makecell[{{p{14mm}}}]{\begin{pspicture}(0,0in)\psbarcode[]{GSI-GFK-ARC-00003.002}{}{qrcode}\end{pspicture} \\ ~ \\ ~}
& \makecell[b]{GSI-GFK- \\ ARC-00003 \\ .002 \\ ~}
\end{tabular}

\begin{tabular}{ll}
\makecell[{{p{14mm}}}]{\begin{pspicture}(0,0in)\psbarcode[]{GSI-GFK-ARC-00003.003}{}{qrcode}\end{pspicture} \\ ~ \\ ~}
& \makecell[b]{GSI-GFK- \\ ARC-00003 \\ .003 \\ ~}
\end{tabular}

[...]

\end{labels}

 Excel-UI


UI-Form of QR-Code Generator

Here the user form of the QR-code generator with the corresponding technical names of the UI-elements:

Batch Files

You need to create two batch files in the directory where the Excel workbook is stored.

ExcelLatex.bat

Code:
xelatex barcodes.tex

QR-CodesCompile.bat

Code:
xelatex QR-CodesMain.tex

VBA Macro Code

Barcode Generator: MakeBarcodesForFolders()

The code runs over cells A3 to A500 and generates a latex output file "labelfile.tex" which is used as input by a latex driver / format file "Barcodes.tex"
Sub MakeBarcodesForFolders()
Dim theOutput As String
theOutput = "\begin{labels}" & vbCrLf & vbCrLf
Worksheets("Ordnerlabel").Activate
For Each theCell In Range("A3:A500")
    theOutput = theOutput _
    & "GSI-GF/K-" & vbCrLf _
    & "\begin{pspicture}(2,0.5in)" _
    & "\psbarcode[scalex=0.7,scaley=0.4,transy=3mm]{" _
    & theCell.Text _
    & "}{}{code39}" _
    & "\end{pspicture}" & vbCrLf _
    & "\vspace{-9mm}" & vbCrLf _
    & "\bf{" & theCell.Text & "}" _
    & vbCrLf _
    & vbCrLf
Next theCell
theOutput = theOutput & "\end{labels}"
'Write Output
    Dim theFile As String, lFile As Long
   
    theFile = Application.ActiveWorkbook.Path & "/labelfile.tex"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile(theFile, True)
    a.WriteLine (theOutput)
    a.Close
End Sub



QR-Code Generator: Button_Weiter_Click()


The Sub "Button_weiter_Click()"(please excuse this very ugly name...) is the core latex code generator for the QR-code Latex-files. Depending on the user's choice of the label size (controls "Radio_21PerPage" or "Radio_65PerPage") it writes a string file with the required Latex markup:

Private Sub Button_Weiter_Click()

Dim theOutput As String
Dim theID As String

theOutput = "\begin{labels}" & vbCrLf & vbCrLf

If Form_ZwischenblattConfig.TextBox_BarcodeCount.Text <> "" Then

    If Form_ZwischenblattConfig.ListBox_Folder.ListIndex <> -1 Then

        For theCounter = 1 To Form_ZwischenblattConfig.TextBox_BarcodeCount.Value
       
            theID = "GSI-GFK-" & Form_ZwischenblattConfig.ListBox_Folder.Value & "." & Format(CStr(theCounter), "000")
           
            If Radio_21PerPage = True Then '21 labels per A4 page, 70 x 37 mm
                theOutput = theOutput _
                & "\begin{pspicture}(0,0in)" _
                & "\psbarcode[]{" & theID _
                & "}{}{qrcode}" _
                & "\end{pspicture}" & vbCrLf _
                & theID _
                & vbCrLf _
                & vbCrLf
            ElseIf Radio_65PerPage = True Then '65 label per page, 37 x 21,2 mm
                theOutput = theOutput _
                & "\begin{tabular}{ll}" & vbCrLf _
                & "\makecell[{{p{14mm}}}]{" _
                & "\begin{pspicture}(0,0in)" _
                & "\psbarcode[]{" & theID _
                & "}{}{qrcode}" _
                & "\end{pspicture}" _
                & " \\ ~ \\ ~}" & vbCrLf _
                & "& \makecell[b]{" _
                & "GSI-GFK- \\ " _
                & Form_ZwischenblattConfig.ListBox_Folder.Value & " \\ " _
                & "." & Format(CStr(theCounter), "000") & " \\ ~}" & vbCrLf _
                & "\end{tabular}" & vbCrLf _
                & vbCrLf
            End If

        Next theCounter
       
    End If
   
    theOutput = theOutput & "\end{labels}"
   
    Call writeFile("QR-Codes.tex", theOutput)
   
    Call createMainFile
   
    Call compileOutput
   
End If

Form_ZwischenblattConfig.Hide

End Sub





Private Sub createMainFile()

Dim theOutput As String

theOutput = _
"\documentclass[a4paper,11pt]{article}" & vbCrLf _
& "\usepackage{pst-barcode}" & vbCrLf _
& "\usepackage[newdimens]{labels}" & vbCrLf _
& "\usepackage[T1]{fontenc}" & vbCrLf _
& "\usepackage{lmodern}" & vbCrLf _
& "\usepackage{makecell}" & vbCrLf _
& "\renewcommand*\familydefault{\sfdefault}" & vbCrLf _
& "\renewcommand\cellalign{lt}" & vbCrLf

If Radio_65PerPage = True Then
    theOutput = theOutput _
    & "\LabelCols=5" & vbCrLf _
    & "\LabelRows=13" & vbCrLf _
    & "\LeftPageMargin=3mm% These four parameters give the" & vbCrLf _
    & "\RightPageMargin=5mm% page gutter sizes. The outer edges of" & vbCrLf _
    & "\TopPageMargin=10mm% the outer labels are the specified" & vbCrLf _
    & "\BottomPageMargin=5mm% distances from the edge of the paper." & vbCrLf _
    & "\InterLabelColumn=-1mm% Gap between columns of labels" & vbCrLf _
    & "\InterLabelRow=1mm% Gap between rows of labels" & vbCrLf _
    & "\LeftLabelBorder=0mm% These four parameters give the extra" & vbCrLf _
    & "\RightLabelBorder=0mm% space used around the text on each" & vbCrLf _
    & "\TopLabelBorder=0mm% actual label." & vbCrLf _
    & "\BottomLabelBorder=0mm%" & vbCrLf _
    & "\begin{document}" & vbCrLf _
    & "\include{QR-Codes}" & vbCrLf _
    & "\end{document}"
ElseIf Radio_21PerPage = True Then
    theOutput = theOutput _
    & "\LabelCols=3" & vbCrLf _
    & "\LabelRows=7" & vbCrLf _
    & "\LeftPageMargin=20mm% These four parameters give the" & vbCrLf _
    & "\RightPageMargin=5mm% page gutter sizes. The outer edges of" & vbCrLf _
    & "\TopPageMargin=20mm% the outer labels are the specified" & vbCrLf _
    & "\BottomPageMargin=5mm% distances from the edge of the paper." & vbCrLf _
    & "\InterLabelColumn=10mm% Gap between columns of labels" & vbCrLf _
    & "\InterLabelRow=10mm% Gap between rows of labels" & vbCrLf _
    & "\LeftLabelBorder=5mm% These four parameters give the extra" & vbCrLf _
    & "\RightLabelBorder=5mm% space used around the text on each" & vbCrLf _
    & "\TopLabelBorder=5mm% actual label." & vbCrLf _
    & "\BottomLabelBorder=5mm%" & vbCrLf
End If

theOutput = theOutput _
& "\begin{document}" & vbCrLf _
& "\include{QR-Codes}" & vbCrLf _
& "\end{document}"

Call writeFile("QR-CodesMain.tex", theOutput)

End Sub


Private Sub writeFile(fileName As String, textToWrite As String)

    'Write Output
    Dim theFile As String, lFile As Long

    theFile = Application.ActiveWorkbook.Path & "\" & fileName

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile(theFile, True)
    a.WriteLine (textToWrite)
    a.Close

End Sub

Private Sub compileOutput()
    theCommand = Application.ActiveWorkbook.Path & "\QR-CodesCompile.bat"
    ChDir Application.ActiveWorkbook.Path
    Call Shell(theCommand)
End Sub

Thursday, June 5, 2014

Updating the Epson Scanner Software

Background


Recently, we bought an EPSON WorkForce DS-7500N network-scanner for our company. The installation of the device and the software coming with it went all fine. We started using the scanner and we were all very content with it.
The software package on CD coming with the scanner had the following version numbers:
  • EPSON Scan: unknown version
  • EPSON Scan PDF EXtensions: 1.3
  • EPSON Scan OCR Component: 1.3
  • Document Capture Pro: 1.0.9
  • Abbyy FineReader 9.0 Sprint: 9.00.631.58228

  • System Environment: Windows 7 - 64bit
Up to this point, the configuration ran without problems, then I was looking for updated version of the software.

Update Software Components

I found the following files on the German EPSON pages:
  • Document Capture Pro: DCU10601_Euro_Web.zip (version 1.6.1)
  • Epson Scan: epson378390eu.exe (version unknown)
  • Abby FineReader 9.0 Sprint: 582_32_ABBYY_FineReader_Sprint_9.0.exe (version 9.00.15.58232)
I installed the updated versions (in a sequence I do not remember) and experienced the following symptoms and problems:
1)  One important feature for us is to create searchable PDF. In the corresponding output settings of Document Capture Pro the option to create searchable PDFs was enabled, but the selector for the document language was greyed out and "Japanese" was selected:
 2) When producing an output (via job or manual) Document Capture Pro did not start the OCR process but saved the PDF without searchable text.

Solution

With the help of the EPSON support I found the following solution to the problem above:
What was missing was an updated version of the EPSON Scan OCR Component, which was not (yet) available on the European Web pages of EPSON. On the American web pages ist is available at this link.
Furthermore, it is essential to deinstall the old software versions of Document Capture Pro and EPSON Scan (deinstallation of EPSON Scan OCR Component ist not requred), then re-boot the computer and to install the updated version in the following sequence:
1) EPSON Scan
2) Document Capture Pro
3) EPSON Scan OCR Component

After doing this, the software worked like expected again. The language option was available fpr selection again:

 and the scan produced searchable PDFs like expected too.

I hope this post helps you to solve your own scanne issue, all the best, your WolfiG

Friday, May 9, 2014

Unique ID-Generator using SQL Server

Background

In a project of mine I had the challenge to generate IDs which are profixes of file names of QM documents we want to store on a file server. These IDs have to be unique and have the follwoing structure:

<area of application, char(4)>-<document type char(3)>-<responsible unit, char(4)>-
<running number, char(5)>-...... (user title)

The parts "area of application", "document type" and "responsible unit" are prescribed by guidelines, the challenge was to build some central tool where users can "pick" new ID which has to be unique.

DB-Tables

I build four DB-tables:

ApplicationAreas with columns
  • ApplicationAreaID (char4), PK)
  • Description (nvarchar(100))

DocumentTypes with columns:
  • DocumentTypeID (char(3), PK)
  • Description (nvarchar(100))
ResponsibleAreas with columns:
  • ResponsibleArea (char(4), PK)
  • Description (nvarchar(100))
DocumentIDs with columns:
  • ApplicationArea (char(4), PK, FK to ApplicationAreas.ApplicationArea)
  • DocumentType (char(3), PK, FK to DocumentTypes.DocumentType
  • ResponsibleArea (char(4), PK, FK to ResponsibleAreas.ResponsibleArea)
  • RunningNumber (int, PK)

Stored Procedure

My first attempt was to make column DocumentIDs.RunningNumber an identity column and to calculate the number automatically via the table. Unfortunately, this approach failed, as the identity functionality does not take into account the multi-column primary key and simply counts up the number with every new entry.
Finally I choose to take an approach via a stored procedure which gives a string output with the automatically determined Document-ID. Here the code:

USE [theDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pickDocumentID]
@ApplicationArea char(4),
@DocumentType char(3),
@ResponsibleUnit char(4)
AS
BEGIN
DECLARE @counter int
DECLARE @counterchar char(5)
DECLARE @theOutput nvarchar(255)
DECLARE @theLength int
SET NOCOUNT ON;

        -- Retrieve highest existing entry for given values
SET @counter = (SELECT MAX(RunningNumber) FROM DocumentIDs
WHERE
ApplicationArea = @ApplicationArea AND
DocumentType = @DocumentType AND
ResponsibleUnit = @ResponsibleUnit);
        -- If there is no entry for the given combination of first three
        -- columns initialize @counter
IF @counter IS NULL
SET @counter = 0;
SET @counter = @counter + 1;
        -- write newly determined Id to DB
INSERT INTO DocumentIDs
        (ApplicationArea, DocumentType, ResponsibleUnit, RunningNumber)
VALUES
        (@ApplicationArea, @DocumentType, @ResponsibleUnit, @counter)
-- Output for Document ID ready to use
SET @counterchar = CONVERT(char(5),@counter);
SET @theLength = LEN(@counterchar);
SET @counterchar = CASE
WHEN @theLength = 1 THEN '0000' + @counterchar
WHEN @theLength = 2 THEN '000' + @counterchar
WHEN @theLength = 3 THEN '00' + @counterchar
WHEN @theLength = 4 THEN '0' + @counterchar
WHEN @theLength = 5 THEN @counterchar
END
SET @theOutput = @ApplicationArea + '_' +
                         @DocumentType + '_' +
                         @ResponsibleUnit + '_' +
                         @counterchar ;
SELECT @theOutput;
END

So, this was it dear reader. I hope you could get something out of my post and you enjoyed.

Monday, April 14, 2014

Fetching Geo-Coordinates from Mapquest-API using Excel-VBA

Background

For some little visualization project I had the idea of putting markers of each of our institute's partners on a world map. All I had was a list with names of about 1500 research institutes, addresses were not in the list.
So my plan was to find a suitable web service which I could query from MS Excel via Visual Basic for Applications (VBA) and programmatically fetch data from this service.

Implementation

After some web-research I finally stumbled over a video by DontFretBrett, where he explains how to fetch geo-data using VBA in Excel via the google maps API. For me the breakthrough was the usage of the XML-maps feature of Excel, where a web service can be called an can be databound to an Excel worksheet.
Furthermore I decided not to use the google maps API, but the openmaps "nominatim" web-service by openstreetmap.org which is free of charge and which can be used to search for non-well-formed geo-information, such as names of research institutes. I found out that openstreetmap restricts the number of calls per day so I finally switched to the free service of mapquest, which is based on nominatim.

Excel XML-Mapping

To do databinding of XML-data to an Excel worksheet, go to "developer tools" (mind that this tab needs to be activated in Excel-options) and there to the "XML-Source" button (please forgive my German Excel..):

When clicking on this button you'll get a new window to the right of your worksheet:

Therein click on the button "XML-Map" on the lower right of this window.

in the corresponding dialog enter the URL to the query you want to execute and give it some hany name (in my case "searchresults"). When you execute the search query, you will get something like this:

After achieving this, drag & drop the fields from the search results to a new worksheet. In my case this were the fields "lat", "lon" and "display_name" (address). Now you can update data in the XML-map window by executing the web-service and copy it to you driving worksheet with the information you have.

VBA Code

I implemented three routines:
1) One driver routine looping over the master worksheet
2) One routine calling the mapquest web service
3) One helper function which does a bit of data cleansing to get rid of special characters

Driver Routine

Sub GetAddressForInstitute()

Dim theSearchString As String
Dim selectedRow As Integer
Dim selectedColument As Integer

Worksheets("Collaborations").Activate

For Each theCell In Worksheets("Collaborations").Range("A5:A2000")
    theCell.Select
    selectedRow = Selection.Row
    selectedColumn = Selection.Column
    theSearchString = Replace(theCell.Text, " ", "+") + "+" + Cells(selectedRow, selectedColumn + 1).Text
    If (theSearchString <> "") Then
        theSearchString = cleanStringFromSpecialCharacters(theSearchString)
        If (theSearchString <> "") Then
            GetDataUpdateSheet (theSearchString)
        End If
    
        Cells(selectedRow, 10).Select
        Selection.Value = Worksheets("lat_lon").Cells(2, 1).Value 'Latitude
        Cells(selectedRow, 11).Select
        Selection.Value = Worksheets("lat_lon").Cells(2, 2).Value 'Longitude
        Cells(selectedRow, 12).Select
        Selection.Value = Worksheets("lat_lon").Cells(2, 3).Value 'Address
    End If
Next theCell
End Sub

Service Caller

Sub GetDataUpdateSheet(searchString As String)
    Dim theMap As XmlMap
   
    Set theMap = ActiveWorkbook.XmlMaps("searchresults")
    On Error Resume Next 'Special characters cause program to dump --> simply go over errors
    theMap.DataBinding.LoadSettings ("http://open.mapquestapi.com/nominatim/v1/search.php?q=" + searchString + "&format=xml")
    theMap.DataBinding.Refresh
End Sub

Special Character Cleaner

Function cleanStringFromSpecialCharacters(inString As String) As String
    Dim outString As String
    outString = Replace(inString, "ä", "ae")
    outString = Replace(outString, "ã", "a")
    outString = Replace(outString, "à", "a")
    outString = Replace(outString, "á", "a")
    outString = Replace(outString, "â", "a")
    outString = Replace(outString, "Ä", "Ae")
    outString = Replace(outString, "ç", "c")
    outString = Replace(outString, "í", "i")
    outString = Replace(outString, "ö", "oe")
    outString = Replace(outString, "ü", "ue")
    outString = Replace(outString, "Ö", "Oe")
    outString = Replace(outString, "Ü", "ue")
    outString = Replace(outString, "ß", "ss")
    outString = Replace(outString, "ó", "o")
    outString = Replace(outString, "é", "e")
    outString = Replace(outString, "è", "e")
    outString = Replace(outString, "É", "E")
    outString = Replace(outString, " ", "+")
    outString = Replace(outString, ",", "")
    outString = Replace(outString, "+-", "")
        
    cleanStringFromSpecialCharacters = outString
End Function

I hope, dear reader, this article was helpful for your own project. All the best
WolfiG