Consulting

Results 1 to 9 of 9

Thread: QR Codes for Excel 2003 XP

  1. #1
    VBAX Mentor Sir Babydum GBE's Avatar
    Joined
    Mar 2005
    Location
    Cardiff, UK
    Posts
    499
    Location

    QR Codes for Excel 2003 XP

    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
    Have a profound problem? Need a ridiculous solution? Post a question in Babydum's forum

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    [VBA]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[/VBA]

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    @KH

    Thank you for sharing !! )

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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?

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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.
    [vba]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[/vba]

    Use the Post method if you need to submit more than a 2k URL. https://google-developers.appspot.co.../docs/overview
    Last edited by Kenneth Hobs; 08-03-2012 at 09:03 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •