Easily Generate Barcodes using Microsoft Excel for Free
By: Siva Katir - 4/15/2014

<p>One of the nicest things about the <a href="https://posguys.com/barcode">POSGuys Online Barcode Generator</a> is that you can use its API to create a barcode on pretty much anything that has an internet connection. Today we'll be taking a crash course in how you can easily make pages of barcodes using Microsoft Excel 2013.  
</p>
<h2>Get the Document Ready</h2>
<p>Start with an Excel document that contains a list of items that you want to generate barcodes for. For our example we used the following list of UPC codes. (To paste this into Excel copy the table below. Paste, then right click and set the cell formatting to "Text" and then paste again.) </p>
<table>
<thead>
<tr>
	<th align="left">UPC</th>
</tr>
</thead>
<tbody>
<tr>
	<td align="left">0036282944961</td>
</tr>
<tr>
	<td align="left">0087508002965</td>
</tr>
<tr>
	<td align="left">8228885781995</td>
</tr>
<tr>
	<td align="left">0032247223157</td>
</tr>
<tr>
	<td align="left">0037753000056</td>
</tr>
<tr>
	<td align="left">0616513721006</td>
</tr>
<tr>
	<td align="left">0610140401515</td>
</tr>
<tr>
	<td align="left">0197043000095</td>
</tr>
<tr>
	<td align="left">0030509945533</td>
</tr>
<tr>
	<td align="left">0043311046343</td>
</tr>
</tbody>
</table>
<p>In the next empty column we'll generate the URL. If your starting row and column isn't "A2" change "A2" to the proper starting row and column of your data.   
</p>
<pre><code>=CONCATENATE("https://posguys.com/Api/GenerateBarcodeImage/?content=", ENCODEURL(TRIM(A2)), "&amp;height=40&amp;width=200&amp;hideText=true&amp;symbology=CODE_128") 
</code></pre>

<p>This will trim excess spaces from the field, encode it for the URL and then combine the strings to create the final URL. Please see the <a href="https://posguys.com/blog/code-comments/article/introducing-barcode-api">Barcode API blog post</a> to see how to generate other types or sizes. </p>
<table>
<thead>
<tr>
	<th align="left">UPC</th>
	<th align="left">Code</th>
	<th></th>
</tr>
</thead>
<tbody>
<tr>
	<td align="left">0036282944961</td>
	<td align="left"><code>=CONCATENATE("https://posguys.com/Api/GenerateBarcodeImage/?content=", ENCODEURL(TRIM(A2)), "&amp;height=40&amp;width=200&amp;hideText=true&amp;symbology=CODE_128")</code></td>
</tr>
</tbody>
</table>
<p>You can easily auto fill the rest of the rows by first selecting the field that you just pasted into. Next shift+click on the last field that you you want the URLs to be generated for. In the ribbon on the <strong>Home</strong> tab select <strong>Fill</strong> and <strong>Fill Down</strong>. </p>
<h2>Make the Macro</h2>
<p>The next step is create the macro that will populate the correct cell with the barcode image. Click on the <strong>View</strong> tab of the ribbon and click on <strong>Macro</strong>. </p>
<p><img src="https://posguys.blob.core.windows.net/content/blog/15/macro_menu.PNG" /> In the <strong>Macro name:</strong> field enter "LoadBarcodeImage" and click <strong>Create</strong> </p>
<p><img src="https://posguys.blob.core.windows.net/content/blog/15/macro_window.PNG" /> </p>
<p>After clicking on create a <strong>Microsoft Visual Basic for Applications</strong> window should open. If it does not select the <strong>LoadBarcodeImage</strong> macro and click edit. </p>
<p><img src="https://posguys.blob.core.windows.net/content/blog/15/macro_editor_window.PNG" /> </p>
<p>Following is the code for the macro, just copy and paste this into the macro editor window. You may need to edit the column references as necessary.     
</p>
<pre><code>Sub LoadBarcodeImage()
     Dim url_column As Range
     Dim image_column As Range

'Set worksheet number to worksheet that data is on.
     'Set Columns() to the letter that contains the URLS
     Set url_column = Worksheets(1).UsedRange.Columns("B")

'Set Columns() to the letter that you want to contain the image
     Set image_column = Worksheets(1).UsedRange.Columns("C")

'Change the "2" in "i = 2" if 2 is not the starting row
     Dim i As Long
     For i = 2 To url_column.Cells.Count
         With image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value)
             .Left = image_column.Cells(i).Left
             .Top = image_column.Cells(i).Top
                 image_column.Cells(i).EntireRow.RowHeight = .Height
         End With
     Next
End Sub 
</code></pre>

<p>When done close the Visual Basic Editor window which will take you back to Excel. Click on <strong>Macros</strong> in the ribbon and select <strong>LoadBarcodeImage</strong> and click on the <strong>Run</strong> button. Depending on how many barcodes you need it to generate this can take some time, just let it run even if Excel becomes unresponsive during this time. You will need to re-run the macro every time the source column changes otherwise the barcodes will not update. After it's complete you should have something that looks like this: </p>
<p><img src="https://posguys.blob.core.windows.net/content/blog/15/barcodes_macro_run.PNG" /> </p>
<h2>Make It Pretty</h2>
<p>For easier use I recommend hiding the column that contains the URL. Right click on the URL column header and select <strong>Hide</strong>. To make the barcodes easier to scan you should increase the sizing of the cells to give some padding between images. To do this select all of the cells and right click on any of the row headers (the row number) and select <strong>Row Height</strong>. I found that setting this to "60" worked well for the height of the example barcodes. All printed out the example looks like this: </p>
<p><img src="https://posguys.blob.core.windows.net/content/blog/15/printed_result.png" /> </p>
<p><br /> </p>
<h2>Finished!</h2>
<p><a href="https://posguys.blob.core.windows.net/content/blog/15/BarcodeExcelExample.xlsm">Click here to download the finished example.</a> If you have any questions of comments please tell us in the comments below!</p>

Share this post:

Easily Generate Barcodes using Microsoft Excel for Free By: Siva Katir - 4/15/2014

Recent Posts By Siva Katir

Easily Generate Barcodes using Microsoft Excel for Free
By: Siva Katir - 4/15/2014