Excel QR Generation Issues - & +

Question

So I have been using the following VBA Module code to generate QR codes in a desired cell. The code works as expected for all desired ASCII values expect '&' and '+'. If any of these characters is mid string example - Jack & Jill - only 'Jack' would be displayed, ie anything after, and including, the '&' would be cut off. Similar story with the '+'.

Thanks in advance

  Function Insert_QR(codetext As String)
    Dim URL As String, MyCell As Range

    Set MyCell = Application.Caller
    URL = "https://chart.googleapis.com/chart?chs=125x125&cht=qr&chl=" & codetext
    On Error Resume Next
      ActiveSheet.Pictures("My_QR_" & MyCell.Address(False, False)).Delete 'delete if there is prevoius one
    On Error GoTo 0
    ActiveSheet.Pictures.Insert(URL).Select
    With Selection.ShapeRange(1)
     .PictureFormat.CropLeft = 10
     .PictureFormat.CropRight = 10
     .PictureFormat.CropTop = 10
     .PictureFormat.CropBottom = 10
     .Name = "My_QR_" & MyCell.Address(False, False)
     .Left = MyCell.Left + 25
     .Top = MyCell.Top + 5
    End With
    Insert_QR = "" ' or some text to be displayed behind code
End Function

This is an issue with `https://chart.googleapis.com/chart?` which is deprecated. — chris neilsen, Feb 23 '20 at 08:40
@chrisneilsen thanks for the reply, do you know of a similar URL which i copuld just switch out for — Jack, Feb 23 '20 at 09:07
I don't know one specifically , but I'd follow the links Google provide — chris neilsen, Feb 23 '20 at 09:38

score 0 · Answer 1 · answered Feb 24 '20 at 19:20

First thing first Google Image Charts is deprecated and can be shutdown anytime soon. There are alternatives like Image-Charts.

Regarding your issue codetext needs to be URLEncoded. If you are using Excel 2013+ you can use

URL = "https://image-charts.com/chart?chs=125x125&cht=qr&chl=" & WorksheetFunction.EncodeUrl(codetext)

Otherwise you can follow this answer that provide an URLEncode function that will also encode query params in order to generate the static QR code image.

score 0 · Answer 2 · answered Mar 10 '20 at 07:25

0

The issue lay within the fact that the QR codes were being generated by URL. the above characters are what are known as URL restricted characters therefore cannot be repreneted in URL. therefore to represent '&' in URL == '%26'

answered Mar 10 '20 at 07:25

Jack

21
5

Excel QR Generation Issues - & +

2 Answers2