Offline Barcode Image Reader

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the prompts presented in the video.

Prompts

You are a senior Excel VBA Developer. Explain the best approach for building an offline barcode image reader using Excel VBA. Don't write the code just yet. Instead, focus on how the solution should be designed and what components are needed.

Ok. Explain how I can use zbar from VBA and the concept of the shell layer

Where can i download zbar from?

I have installed zbar. Give me the exact command line code to read a file named "Asset1.png" located in the folder "C:\Youtube\Current\Archive\AI Videos\04 QR Code\Assets" on my computer.

You are a Senior VBA developer. Your task is to build an offline barcode-image reader using Excel VBA. You will use zbar to decode the image file. For your reference, I have already tested the following code in command line and it works. >>>"C:\Program Files (x86)\ZBar\bin\zbarimg.exe" --raw "C:\Youtube\Current\Archive\AI Videos\04 QR Code\Assets\Asset1.png"<<< Write an Excel VBA function that takes the image file as an input, and returns the decoded value as the output. Handle errors if no barcode is found. Name the function appropriately. Give me a commented docstring that clearly describes the function. Build me a Sub to test out this function as well.

Ok. That works. Now, please add the output of the DecodeBarcodeFromImage onto the Excel Sheet1, into the first available row. Column A should contain the file name. Column B should contain the first decoded barcode value. If there is another barcode value, paste it into Column C and so on. Build this new logic into a Sub, which takes the output from DecodeBarcodeFromImage as a parameter.

thanks. however, in case of two barcode values in the same image, both barcode values are being extracted into Column B. I have the first one in Column B and the second one in Column C. for example, i have the output >>>example.com/Asset-003 Asset-003<<< returned from one image and i want these two values in separate columns. please modify the logic accordingly.

Ok. Thanks. It works now. And for the final step. I want to loop through a folder "C:\Youtube\Current\Archive\AI Videos\04 QR Code\Assets" and for every png file in this folder, i want to extract the barcode value and output it into the sheet 1 using the function DecodeBarcodeFromImage and sub WriteBarcodeResultToSheet.

Ok. The loop doesnt seem to be working. Only the values for the first file got pasted into the sheet. I have 2 more files in the folder, which dont seemed to have been processed. I am pasting my entire working code below. Can you please analyse and find out the mistake? --> Sub ProcessAllPngInFolder() Dim folderPath As String Dim fileName As String Dim fullPath As String Dim decoded As String ' Folder containing images folderPath = "C:\Youtube\Current\Archive\AI Videos\04 QR Code\Assets\" ' Ensure folder ends with backslash If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\" ' Loop through all PNG files fileName = Dir(folderPath & "*.png") Do While fileName <> "" fullPath = folderPath & fileName ' Decode barcode decoded = DecodeBarcodeFromImage(fullPath) ' Write result to Sheet1 Call WriteBarcodeResultToSheet(fullPath, decoded) ' Next file fileName = Dir Loop MsgBox "All PNG files processed!", vbInformation End Sub Function DecodeBarcodeFromImage(ByVal ImagePath As String) As String On Error GoTo ErrHandler Dim zbarPath As String Dim cmd As String Dim shell As Object Dim execObj As Object Dim result As String ' Path to zbarimg.exe (update if installed elsewhere) zbarPath = """C:\Program Files (x86)\ZBar\bin\zbarimg.exe""" ' Build command line cmd = zbarPath & " --raw """ & ImagePath & """" ' Create Shell object Set shell = CreateObject("WScript.Shell") ' Execute the command and capture the output Set execObj = shell.Exec(cmd) ' Read output result = execObj.StdOut.ReadAll ' Trim result (remove CR/LF) result = Trim(result) ' If result is empty ? no barcode found DecodeBarcodeFromImage = result Exit Function ErrHandler: DecodeBarcodeFromImage = "" End Function Sub WriteBarcodeResultToSheet(ByVal ImagePath As String, ByVal DecodedOutput As String) Dim ws As Worksheet Dim nextRow As Long Dim fileName As String Dim barcodes() As String Dim cleanOutput As String Dim i As Long On Error GoTo ErrHandler Set ws = ThisWorkbook.Sheets("Sheet1") ' Find next available row nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1 ' Get filename only fileName = Dir(ImagePath) ' --- Normalize all newline variations --- cleanOutput = DecodedOutput cleanOutput = Replace(cleanOutput, vbCrLf, vbLf) ' Convert CRLF ? LF cleanOutput = Replace(cleanOutput, vbCr, vbLf) ' Convert CR ? LF ' Split on vbLf only (most reliable) If Len(cleanOutput) > 0 Then barcodes = Split(cleanOutput, vbLf) Else ReDim barcodes(0) barcodes(0) = "" End If ' Write filename ws.Cells(nextRow, 1).Value = fileName ' Write each barcode into separate columns B, C, D... For i = LBound(barcodes) To UBound(barcodes) ws.Cells(nextRow, i + 2).Value = barcodes(i) Next i Exit Sub ErrHandler: MsgBox "Error writing barcode results to sheet.", vbExclamation End Sub

thanks. that works. now, in the sub ProcessAllPngInFolder_FSO, please add some logic to clear the entire Sheet, and add headers in row 1. Cell A1 will be "File Name" and Cell B1 onwards will be the Barcode Headers. e.g. if total of only one barcode asset has been extracted per asset, Cell b1 will have header Barcode1. If any asset has two barcode values extracted, Cell C1 needs to have a Header value of Barcode2 and so on.

I ran the sub and got one extra header. can you please correct the logic. sample output below --> File Name Barcode1 Barcode2 Barcode3 Asset1.png Asset-001 Asset2.png example.com/Asset-002 Asset3.png example.com/Asset-003 Asset-003