QR Codes for Excel 2003 XP

[Sir Babydum GBE]

Hi

Anyone know of any software (free or commercial) that can allow Excel to systematically convert text to QR Code? THere are web based solutions but I need for Excel to convert hundreds of items in one go.

Many thanks

Damian

[Kenneth Hobs]

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Function DownloadFile(url As String, LocalFileName As String) As Boolean
If URLDownloadToFile(0, url, LocalFileName, 0, 0) = 0 Then
DownloadFile = True
End If
End Function

Sub DownloadQRcode(url As String, pngName As String, size As Integer)
' Pixel Size S, M, L: 160, 260, 360
DownloadFile "http://chart.apis.google.com/chart?cht=qr&chs=" & size & "x" & size & "&chl=" & url, pngName
End Sub

Sub Test_DownloadQRcode()
DownloadQRcode "http://www.vbaexpress.com/forum/showthread.php?t=43015", "c:\temp\VBAExpress QR Codes 43105.png", 160
Shell "cmd /c ""c:\temp\VBAExpress QR Codes 43105.png"""
End Sub

[snb]

@KH

Thank you for sharing !! :))

[Bob Phillips]

Kenneth,

I have never had a need for many QR codes so one of the generators around work fine for me, but I tried this and couldn't figure out how to get true multi-lines. Any idea what to inject into the text so as to get a business card to spread over multi-lines?

[Kenneth Hobs]

I would just treat it as a graphic and insert it where needed. One can compute the size in terms other than pixels if a set size is needed to span multiple lines. Are you doing the card in an MSWord file or Excel?

[Bob Phillips]

Not sure that we are on the dame page. I was referring to creating a QR from a text string of my business details. I just adapted your code in Excel and fed the details to it. It worked fine insofar as a QR is created and when I read that QR all the information is there, except that it came out as lone continuous line, not multiple lines for each data element.

It is not important, as I have a QR image of my details from elsewhere, just tried it out of interest.

[Kenneth Hobs]

I don't have a smart phone so I can not test that. I have always used the QR as a link to a web site. For that, I usually use is.gd's QR code since I typically need to shorten the link for other needs. I then clip the QR code image.

Without seeing your coded string, I would guess that it might mean that you used vbLF rather than vbCRLF to build the string for it?

[Bob Phillips]

I tried them all Kenneth, vbLf, vbCrLf, and vbNewline - none achieved the breaks. As none of those worked, I even tried embedding %0A and %0D in case the API accepted those, but no joy.

[Kenneth Hobs]

Since vbCR = decimal character 13 = hex D or &HD
and
vbLF = decimal character 10 = hex A or &HA
vbCRLF = decimal characters 13 and then 10.

xld was close. We just need to escape those characters for html.
"%0D%0A"

e.g.
Sub Test2_DownloadQRcode()
DownloadQRcode "Hello World!" & "%0D%0A" & "by Kenneth Hobson", "c:\temp\HelloWorld.png", 360
Shell "cmd /c ""c:\temp\HelloWorld.png"""
End Sub

Use the Post method if you need to submit more than a 2k URL. https://google-developers.appspot.com/chart/infographics/docs/overview