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