QR Code generator, text from multiple cells

[ph1l]

Hi all,

I saw some great threads here, but none with answers to my questions.

I have one sheet, where I print labels, based on text from !$B$10:$B$16
I want to insert a QR code into $C$10, based on data / text from !$B$10:$B$16
It would be awesome, if the QR code gets updated, when new data / text has been inserted to any cell, at !$B$10:$B$16
The QR code could be generated from the Google API, or whatever you can recommend.

Can anyone crack this hard nut?

 

[severynm]

H!

Interesting problem. Not sure which barcode generator you need to use or prefer to use, but I've found one called zint that I like. It's free, open source, doesn't require an online connection, includes both a decent GUI and command line solutions, and has pretty good documentation. I had never tried it from the command line before (which we need to do if running it from a VBA script), but I was able to piece things together nicely from its documentation.

I played around a little with it myself and ended up with the following, called from the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$10" Then
        'Delete existing QR code in worksheet
        Dim pic As Shape
        For Each pic In Sheet3.Shapes
            If pic.Name = "QR Code" Then pic.Delete
        Next pic
        
        'Create new QR code
        Dim data As String
        Dim qr As Picture
        Dim qrImagePath As String
        data = Sheet3.Range("B10").value
        qrImagePath = "C:\Users\%username%\Downloads\zint_win32_snapshot_2021-07-02\2021-07-02\qr.png"
    
        Shell "C:\Users\%username%\Downloads\zint_win32_snapshot_2021-07-02\2021-07-02\zint.exe zint -b 104 -d """"" & data & """"" -o " & qrImagePath & " --scale 5"
        Set qr = Sheet3.Pictures.Insert("C:\Users\%username%\Downloads\zint_win32_snapshot_2021-07-02\2021-07-02\qr.png")
        qr.Left = Sheet3.Range("C10").Left
        qr.Top = Sheet3.Range("C10").Top
        qr.PrintObject = True
        qr.Name = "QR Code"
    End If
End Sub

You'll need to change: the worksheet you want to use, the path pointing to zint.exe, the location where you want to save the QR image, and the location in the worksheet you want the image to be. You also might want to play around with the cmd line a little to customize the output exactly how you want it. -b 104 is telling it to export as HIBC QR Code type, -d specifies the data for the QR code, -o specifies the output path. Those are the basics, but you can add other parameters according to the documentation to customize the format or size.

 

[ph1l]

Thanks for the effort
However, no QR codes are beeing generated at the moment.

I start to believe, that this task is close to impossible to make.

 

[severynm]

However, no QR codes are beeing generated at the moment.

I start to believe, that this task is close to impossible to make.

Well, it's not impossible, because it definitely works for me. If you'd still like help, you'll have to be more specific about what is not working or what errors are occurring. Otherwise, I cannot do much to help further.

 

[Jlmiller17]

H!

Interesting problem. Not sure which barcode generator you need to use or prefer to use, but I've found one called zint that I like. It's free, open source, doesn't require an online connection, includes both a decent GUI and command line solutions, and has pretty good documentation. I had never tried it from the command line before (which we need to do if running it from a VBA script), but I was able to piece things together nicely from its documentation.

I played around a little with it myself and ended up with the following, called from the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$10" Then
        'Delete existing QR code in worksheet
        Dim pic As Shape
        For Each pic In Sheet3.Shapes
            If pic.Name = "QR Code" Then pic.Delete
        Next pic
       
        'Create new QR code
        Dim data As String
        Dim qr As Picture
        Dim qrImagePath As String
        data = Sheet3.Range("B10").value
        qrImagePath = "C:\Users\%username%\Downloads\zint_win32_snapshot_2021-07-02\2021-07-02\qr.png"
   
        Shell "C:\Users\%username%\Downloads\zint_win32_snapshot_2021-07-02\2021-07-02\zint.exe zint -b 104 -d """"" & data & """"" -o " & qrImagePath & " --scale 5"
        Set qr = Sheet3.Pictures.Insert("C:\Users\%username%\Downloads\zint_win32_snapshot_2021-07-02\2021-07-02\qr.png")
        qr.Left = Sheet3.Range("C10").Left
        qr.Top = Sheet3.Range("C10").Top
        qr.PrintObject = True
        qr.Name = "QR Code"
    End If
End Sub

You'll need to change: the worksheet you want to use, the path pointing to zint.exe, the location where you want to save the QR image, and the location in the worksheet you want the image to be. You also might want to play around with the cmd line a little to customize the output exactly how you want it. -b 104 is telling it to export as HIBC QR Code type, -d specifies the data for the QR code, -o specifies the output path. Those are the basics, but you can add other parameters according to the documentation to customize the format or size.

I love the above solution and am attempting to use it to loop through a range and create qr codes in zint and save an image to a folder of each one. With my limited vba knowledge I have made progress but I was hoping for a bit of direction so I can finish this project.
1.) what do I need to have after "Shell"? My filepath to the zint application?
2.) any direction on "set qr" path as well would be appreciated!

 

[severynm]

I love the above solution and am attempting to use it to loop through a range and create qr codes in zint and save an image to a folder of each one. With my limited vba knowledge I have made progress but I was hoping for a bit of direction so I can finish this project.
1.) what do I need to have after "Shell"? My filepath to the zint application?
2.) any direction on "set qr" path as well would be appreciated!

1. Yep! Point it to wherever you have the zint exe saved on your computer. All the items after that path are command line parameters we send to zint to tell it how to create the QR. The Shell command is the same thing as opening command prompt and typing the same items - it just allows us to do it directly from excel.
2a. The qrImagePath details where you want the qr file to be saved, and its filename. You can customize this how you like.
2b. The step Set qr = Sheet3.Pictures.Insert("...") inserts a picture (QR) at the location you specify into the worksheet, and the lines directly after it adjust the position. If you do not need the QR in the worksheet, you can skip these steps. If you want this, the path here should point to the new QR picture just created with the previous step.

 

[Jlmiller17]

Dim iLR As Integer

With Sheet2
'find last row of data
iLR = .Cells.FIND("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

For i = 2 To iLR

'Create new QR code
Dim data As String
Dim qr As Picture
Dim qrImagePath As String
Dim qrName As String

qrName = .Cells(i, 1).Value 'will contain text i want to use as image name
data = .Cells(i, 2).Value 'qr code sting
qrImagePath = "\\dc1\admindoc\1 SQL\1 Scheduler\QR Codes for MB\" & qrName & ".png" 'save qr image here

Shell "C:\Users\jmiller\Program Files (X86)\zint.exe zint -b 104 -d """"" & data & """"" -o " & qrImagePath & " --scale 5"

'create qr code on sheet4
Set qr = Sheet4.Pictures.Insert("\\dc1\admindoc\1 SQL\1 Scheduler\QR Codes for MB\" & qrName & ".png")
qr.Left = Sheet4.Range("C10").Left
qr.Top = Sheet4.Range("C10").Top
qr.PrintObject = True
qr.Name = "QR Code"
Next i

 

[Jlmiller17]

Set qr makes sense. I am still struggling to get the Shell filepath correct. This seems to me to be the right filepath but I think i am missing something obvious.

 

[Jlmiller17]

Ok, I moved the program to a new location and now have got it to open Zint with my Shell command. Only remaining problem is that my data is not being entered into Zint so a valid image is not being produced. I stepped through the code and my data variable is being set properly and Zint opens up as planned. This is where I am at now. I have no idea how to post the code properly which is driving me nuts

 

[Jlmiller17]

For i = 2 To iLR
        
        'Create new QR code
        Dim data As String
        Dim qr As Picture
        Dim qrImagePath As String
        Dim qrName As String
        
        qrName = .Cells(i, 1).Value 'will contain text i want to use as image name
        data = .Cells(i, 2).Value   'qr code string
        qrImagePath = "\\dc1\admindoc\1 SQL\1 Scheduler\QR Codes for MB\" & qrName & ".png"  'save qr image here
        
        Shell "\\dc1\admindoc\1 SQL\1 Scheduler\QR Codes for MB\qtZint.exe qtZint -b 104 -d """"" & data & """"" -o " & qrImagePath & " --scale 5"
        
        
        'create qr code on sheet4
        Set qr = Sheet4.Pictures.Insert("\\dc1\admindoc\1 SQL\1 Scheduler\QR Codes for MB\" & qrName & ".png")
        qr.Left = Sheet4.Range("C10").Left
        qr.Top = Sheet4.Range("C10").Top
        qr.PrintObject = True
        qr.Name = "QR Code"
Next i