Consulting

Results 1 to 9 of 9

Thread: QR Code in Word 2010

  1. #1

    QR Code in Word 2010

    First of all, Hello World !

    The Scenario:
    - we need to ship mail to people
    - 50 operators with Word 2010 that after they complete the word file with the content, they need to type the address of the person the mail has to arrive to ( in the same file )
    - they write the address ( in a table cell or something EASY for them `cause of noobs ) and a magical button should be there to convert the address into a QR code - The address must be multi-lined
    - the documents arrive at the person responsible with the shipping ( on paper ), he scans the QR Code and prints the sticky label ( on the label will be the human readable address ) that will be put on the package cover

    The Assets:
    - we will buy the hardware required
    - macro enabled files ( checked )
    - it doesn`t matter if the QR code is generated with google link as long as it magically appears in their word file
    - I write code in other languages but NEVER in VBA ( to be honest I`m a Word noob )

    The PROBLEM:
    - the solutions for the QR code generator will imply buying 50 licenses ( for which we just do not have the funds, considering the hardware will not be cheap either )
    - I have tried countless generators found online, that work great but are outside of the Word file, so, outside the scope and need a bit of PC operating skill to work with ( that unfortunatelly we do not have... ) AND some addins for MSWord but keep crashing the instance OR are getting expensive also
    - The designated person to save them is.... me... and I have 4 hours in total of VBA programming, 2 hours yesterday and 2 today... ( I just found out how to make a button OnClick... ), it`s just hell for me, but I learn fast

    The Solution:
    - ANY help is more than welcomed
    - I will add it here for future references

    Thank you in advance !

  2. #2
    QR Codes are not something with which hitherto I have had any involvement, but a quick search produced some example code for Excel at http://www.mrexcel.com/forum/excel-q...generator.html which you may be able to adapt - even if you have to create the code in Excel and paste it to your document.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3

    Talking

    cool, I`ll post the solution for word here IF ( and that is a big IF ) I can manage to implement it as needed Thanks ! ( I found every other link but not that one it seems )

  4. #4
    I had a quick play around with the code from that link, and the following seems to work in Word to put a QR code at the location configured as oRng. Whether the QR code is valid, I have no means of establishing, but it should point you in the right direction. The link at the top of the code gives the parameters.

    Option Explicit
    'other technical specifications about google chart API:
    'https://developers.google.com/chart/infographics/docs/qr_codes
    
    Sub Macro1()
    URL_QRCode_SERIES "ABC123", Selection.Range
    End Sub
    
    Function URL_QRCode_SERIES( _
             ByVal QR_Value As String, _
             oRng As Range, _
             Optional ByVal PictureSize As Long = 150, _
             Optional ByVal Updateable As Boolean = True) As Variant
    
    Dim oPic As InlineShape
    Dim vLeft As Variant, vTop As Variant
    Dim sURL As String
    
    Const sRootURL As String = "https://chart.googleapis.com/chart?"
    Const sSizeParameter As String = "chs="
    Const sTypeChart As String = "cht=qr"
    Const sDataParameter As String = "chl="
    Const sJoinCHR As String = "&"
    
        If Updateable = False Then
            URL_QRCode_SERIES = "outdated"
            GoTo lbl_Exit
        End If
    
        If Len(QR_Value) = 0 Then
            GoTo lbl_Exit
        End If
    
        sURL = sRootURL & _
               sSizeParameter & PictureSize & "x" & PictureSize & sJoinCHR & _
               sTypeChart & sJoinCHR & _
               sDataParameter & UTF8_URL_Encode(VBA.Replace(QR_Value, " ", "+"))
    
        Set oPic = ActiveDocument.InlineShapes.AddPicture(sURL, False, True, oRng)
    lbl_Exit:
        Exit Function
    End Function
    
    Function UTF8_URL_Encode(ByVal sStr As String)
    'http://www.nonhostile.com/howto-convert-byte-array-utf8-string-vb6.asp (link no longer valid)
    Dim i As Long
    Dim a As Long
    Dim res As String
    Dim code As String
    
        res = ""
        For i = 1 To Len(sStr)
            a = AscW(Mid(sStr, i, 1))
            If a < 128 Then
                code = Mid(sStr, i, 1)
            ElseIf ((a > 127) And (a < 2048)) Then
                code = URLEncodeByte(((a \ 64) Or 192))
                code = code & URLEncodeByte(((a And 63) Or 128))
            Else
                code = URLEncodeByte(((a \ 144) Or 234))
                code = code & URLEncodeByte((((a \ 64) And 63) Or 128))
                code = code & URLEncodeByte(((a And 63) Or 128))
            End If
            res = res & code
        Next i
        UTF8_URL_Encode = res
    lbl_Exit:
    Exit Function
    End Function
    
    Private Function URLEncodeByte(val As Integer) As String
    Dim res As String
        res = "%" & Right("0" & Hex(val), 2)
        URLEncodeByte = res
    lbl_Exit:
        Exit Function
    End Function
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    Hello again !
    Below will post the temporary/almost finished solution. It is not polished because I do not have yet the desired template for the production file, BUT here it is!
    The word file has at this moment only a table with one row, and 3 cells:
    - first cell a picture ContentControl as placeholder for the QR Code about to be generated -
    - second cell, button to generate QR
    - third cell, a bookmark ( cell bookmark ) that contains whatever anyone wants to type in ( will have paragraphs - multiple lines, in my case )

    The script generates QR code with multiple lines



    Option Explicit
    'other technical specifications about google chart API:

    Dim BmAdresa As Range
    Dim QR_Value As String
    Dim cc As ContentControl




    Private Sub BtnQR_Click()
    Set BmAdresa = ActiveDocument.Bookmarks("BmAdresa").Range


    Set cc = ActiveDocument.ContentControls(1)
    If cc.Type = wdContentControlPicture Then
    If cc.Range.InlineShapes.Count > 0 Then
    cc.Range.InlineShapes(1).Delete
    End If

    End If
    ' Had to find and replace the paragraphs with proper "end of line" - ^l
    BmAdresa.Find.ClearFormatting
    BmAdresa.Find.Replacement.ClearFormatting
    With BmAdresa.Find
    .Text = "^p"
    .Replacement.Text = "^l"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    BmAdresa.Find.Execute Replace:=wdReplaceAll
    With BmAdresa.Find
    .Text = "^p"
    .Replacement.Text = "^l"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    BmAdresa.Find.Execute Replace:=wdReplaceAll

    URL_QRCode_SERIES BmAdresa.Text, cc.Range
    End Sub






    Function URL_QRCode_SERIES( _
    ByRef QR_Value As String, _
    oRng As Range, _
    Optional ByVal PictureSize As Long = 150, _
    Optional ByVal Updateable As Boolean = True) As Variant

    Dim oPic As InlineShape
    Dim vLeft As Variant, vTop As Variant
    Dim sURL As String



    Const sRootURL As String = "https://chart.googleapis.com/chart?"
    Const sSizeParameter As String = "chs="
    Const sTypeChart As String = "cht=qr"
    Const sDataParameter As String = "chl="
    Const sJoinCHR As String = "&"

    If Updateable = False Then
    URL_QRCode_SERIES = "outdated"
    GoTo lbl_Exit
    End If

    If Len(QR_Value) = 0 Then
    GoTo lbl_Exit
    End If
    sURL = sRootURL & _
    sSizeParameter & PictureSize & "x" & PictureSize & sJoinCHR & _
    sTypeChart & sJoinCHR & _
    sDataParameter & UTF8_URL_Encode(VBA.Replace(QR_Value, "", "%0A"))
    ' Above, our "about to encode text" `s value with multiple rows is "prettyfied" for URL - ye ye, too much coffee
    ' Atleast my browser encoding does not display the sign for "soft enter" or how is it called

    Set oPic = ActiveDocument.InlineShapes.AddPicture(sURL, False, True, oRng)
    lbl_Exit:
    Exit Function
    End Function



    Function UTF8_URL_Encode(ByVal sStr As String)

    Dim i As Long
    Dim a As Long
    Dim res As String
    Dim code As String

    res = ""
    For i = 1 To Len(sStr)
    a = AscW(Mid(sStr, i, 1))
    If a < 128 Then
    code = Mid(sStr, i, 1)
    ElseIf ((a > 127) And (a < 2048)) Then
    code = URLEncodeByte(((a \ 64) Or 192))
    code = code & URLEncodeByte(((a And 63) Or 128))
    Else
    code = URLEncodeByte(((a \ 144) Or 234))
    code = code & URLEncodeByte((((a \ 64) And 63) Or 128))
    code = code & URLEncodeByte(((a And 63) Or 128))
    End If
    res = res & code
    Next i
    UTF8_URL_Encode = res
    lbl_Exit:
    Exit Function
    End Function


    Private Function URLEncodeByte(val As Integer) As String
    Dim res As String
    res = "%" & Right("0" & Hex(val), 2)
    URLEncodeByte = res
    lbl_Exit:
    Exit Function
    End Function

  6. #6
    sDataParameter & UTF8_URL_Encode(VBA.Replace(QR_Value, " ", "%0A")) well, it`s almost like the MALE sign gmayor, you rock, dude next time you visit Bucharest, will rain with beer for you

  7. #7
    While my daughter frequently visits Bucharest on business, I am afraid I will have to miss out Glad you were able to get it working.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  8. #8
    maybe the next time she visits, she`ll take back a special beverage from me that is made in the region I was born in, a bit strong, but damn good It`s called "Tuica".
    do I have to lock the thread or mark it as "solved" or smth?

  9. #9
    You should be able to mark it as solved.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Tags for this Thread

Posting Permissions

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