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
