VBA Code WORD that creates the QR Code directly without installing any programs.

Hello everyone, I'm new to the forum. I need your help to solve my need. I created a WORD editable form (which the public at the following link https://1drv.ms/w/s!Ali6qqOH3dOAhGAqVS721f9F6yij) whose VBA code contained in it, after compilazioe Data generates me Qcode. The Qcode is generated thanks to the installation of the program (DEMO version and maturity) STROKESCRIBE and it works great for my purpose. What I ask you is: 1) because I have to distribute the files editable Word 1020 users who can not install the program Qcode, there is a chance to download a FREE program (instead of STROKESCRIBE) I Qcode genres and you can call in VBA code in Word? 2) Or, alternatively (this would be the best solution for me) as possible in order to submit within the Word VBA code that allows all users to be able to create the Qcode without installing any program on your PC? I thank those who help me in this, you only experts can do.

P.S. excuse me for my English is not perfect.

If my sentences and my terms are not correct please change them to promote understanding of the text to other forum users.

Hi Nicola.

***Post moved by the moderator to the appropriate forum category.***

 

Question Info


Last updated April 9, 2020 Views 8,679 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Word 2013 and later versions have a BARCODE field that can display a QR code. For example, see my answer at https://answers.microsoft.com/en-us/msoffice/forum/msoffice_word-mso_windows8/inserting-a-qr-code-in-a-word-document. This field could be inserted in a document by VBA. Unfortunately, it won't work in Word 2010 or earlier versions.
_____________________________
https://jay-freedman.info

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Good morning Jay Freedman, first of all nice to meet you, thanks for your kind and professional intervention.

Excuse my ignorance, how can I adapt this code vba with what you show me ?. Please do me a practical example to understand how to proceed alone? Thanks for your help.

P.S. I'm sorry for my English incorrect, I help with the translator of google online.

Private Sub CommandButton3_Click()
Dim oFFld As FormFields
Dim oBkMrk As Bookmarks
Selection.EndKey Unit:=wdStory 
'oFFld("txtFirma").Result = oFFld("txtCognome").Result & " " & oFFld("txtNome").Result
Set oFFld = ActiveDocument.FormFields
Set oBkMrk = ActiveDocument.Bookmarks
Dim sh As InlineShape
Dim ss As StrokeScribe

  Set sh = Selection.InlineShapes.AddOLEControl("STROKESCRIBE.StrokeScribeCtrl.1")
  Set ss = sh.OLEFormat.Object
  sh.Height = InchesToPoints(1)
  sh.Width = CentimetersToPoints(2)
  ss.Alphabet = QRCODE
  ss.Text = oFFld("txtCognome").Result & " " & oFFld("txtNome").Result & " " & oFFld("txtIl").Result

End Sub

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

The equivalent VBA code to insert a DISPLAYBARCODE field for the same text string should look like this:


Private Sub CommandButton3_Click()
    Dim oFFld As FormFields
    Dim oNewFld As Field
    Dim oRg As Range
    Dim strCode As String
    Dim iHeight As Integer ' measured in twips (1 point = 20 twips)
    Dim iCorrection As Integer  ' valid values are 0 to 3
    Dim strSwitches As String

    With ActiveDocument
        ' cannot insert anything in a protected form, so unprotect
        If .ProtectionType <> wdNoProtection Then
            .Unprotect  ' include Password:="secret password" if needed
        End If

        Set oFFld = .FormFields
        Set oRg = .Range
        oRg.Collapse wdCollapseEnd  ' location to insert field

        iHeight = InchesToPoints(1) * 20
        iCorrection = 3  ' best correction level
        strSwitches = " \h " & CStr(iHeight) & " \q " & CStr(iCorrection)

        ' build the field code
        strCode = "DISPLAYBARCODE " & Chr(34) & oFFld("txtCognome").Result & _
            " " & oFFld("txtNome").Result & " " & oFFld("txtIl").Result & _
            Chr(34) & " QR" & strSwitches

        ' insert the field and update it
        Set oNewFld = .Fields.Add(Range:=oRg, Text:=strCode, PreserveFormatting:=False)
        oNewFld.Update

        ' reprotect the document
        .Protect Type:=wdAllowOnlyFormFields, noreset:=True ' Password:="secret password"
    End With
End Sub

There are a few things to note about the difference:

  • The QR code is always square, so the code does not set the height and width separately. Only the height is given.
  • Your original code did not use the bookmarks collection, so I omitted that.
  • If some other part of your userform turns off the form protection, then you should remove the parts from the beginning and end of my code that turn it off and turn it on again. If the form is protected when the .Fields.Add command executes, the command will fail with a runtime error.
_____________________________
https://jay-freedman.info

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Something else worth noting about DISPLAYBARCODE is that it does not work with accented characters (I think it is constrained to 0-9,a-z,A-z,some of the ASCII punctuation characters). i.e. it is not currently an option for anyone needing additional characters.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Word 2013-16 will generate a QR barcode. Word 2010 will not. Period. You can convert a barcode created in Word 2013 to an image file and place that image in a Word 2010 (or 2003) document.

Here is an Add-In for Word 2013 that inserts the appropriate DISPLAYBARCODE field.

Bar Code Add-In

Again, this will not work in earlier versions of Word. The bar code field generated will not work in earlier versions unless converted to an image.

Volunteering to pay forward and help the Microsoft user community.]
Screenshots from SnagIt

Charles Kenyon
Sun Prairie, Wisconsin
wordfaq[at]addbalance[dot]com

Legal website: http://addbalance.com

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Start with thank you all for your invaluable advice and your professionalism, I could not answer for the first night working commitments. So that does not accept accented characters, the DISPLAYBARCODE is limited. Explain this please, I is not yet clear. We admit that prepares the Word file to be delivered to all users, adapting to my purpose the Jay Freedman code, and installing the Bar Code Add-In (as I suggest Charles Kenyon, whom I greet), I have to do, the files will work on any computer with Word 2013 installed without the need for another?

Help me understand this please.

Hi Nicola.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

<<
that does not accept accented characters, the DISPLAYBARCODE is limited. Explain this please, I is not yet clear

>>


What I mean is that the QR code standards should let you encode a string containing accented characters such as "MÁLAGA" i.e. so that the bar code reader reads "
MÁLAGA"

However, DISPLAYBARCODE does not encode the Á properly. In this case it will encode  "MALAGA" and that is what the bar code reader will "see"

If you do not need to encode accented characters, no problem.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks Peter Jamieson, you were very clear and understandable. My real problem will manage the names and surnames with accented characters, type Cirinnà, Abbascià etc. That to me would be a big problem, especially for how much regards the preparation of lists for open competitions.

What else can I do to fix this?

Hi Nicola.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Well, I have never done this, but since you can use VBA you may be able to use a web service to convert the text to a URL. I have provided some sample code that shows how to access one such service (This is just the first service I came across on Google - I do not know whether it is suitable for your needs).

Sub testGetQRCode()

' This is just an example of how you could use a
' web service to create a QR code.
' The steps are:
'   convert the text to a "URLencoded" format
'   build a request URL tht specifies the text and the size of the QR code
'   send the request
'   save the result to a file
'   insert the file at the end of the document

' to use any such service for real, you obviously have to find out
' what their terms and conditions are, and you will probably need
' your users to understand that they are sending data to a 3rd-party

' This sample actually uses 3 Windows COM objects to do this, and also
' uses the Windows JScript interprter.
' You will need to make references to all these objects via
' VBA Editor->Tools->References
' But the code will only work on Windows systems that have those objects
' The references you need are as follows:
' To issue the request and retrieve the result:-
' Microsoft WinHTTP Services, version 5.1 (in winhttpcom.dll)
' To save the binary file easily:-
' Microsoft ActiveX Data Objects Library
' (I happen to be using version 6.1, which is in msado15.dll)
' To encode the string:
' Microsoft Script control (msscript.ocx)

' None of the above are available on Mac.
' There are other ways you can save to disk without using ActiveX Data Objects
' so yoou should probably avoid that if possible
' There are also other ways to do the URL Encoding and the .ocx probably
' will not work on 64-bit versions of Word
' so you should probably avoid that if possible
'
' However, I do not think the HTTP control
' or something like it is probably unavoidable.

' Finally, in the scenario you describe, you might need
' to avoid saving the image to disk. It may be possible to avoid
' that by encapsulating the returned .png in an XML format Word document
' and using Word's insertXML method to insert the image, but
' I am not sure about that
'
' To distribute code that may use different versions of referenced
' objects you normally have to change declarations such as
' "Dim request as WinHttpRequest"
' to
' "Dim request as Object"
' and you may need to replace constant names by their values

' My test!

Const QRImageFile As String = "c:\a\qrimage.png"
Const TextToEncode As String = "Cirinnà, Abbascià"
Call getQRCode(TextToEncode:=TextToEncode, FullName:=QRImageFile)
' Insert the image at the end of the document
Set r = ActiveDocument.Content
r.Collapse Direction:=Word.WdCollapseDirection.wdCollapseEnd
r.InlineShapes.AddPicture _
  FileName:=QRImageFile, _
  LinkToFile:=False, _
  SaveWithDocument:=True
Set r = Nothing
End Sub

Function getQRCode(TextToEncode As String, FullName As String)
' This is the URL of an example text-to-QR code web service
Const QRServerUrlStem As String = "https://api.qrserver.com/v1/create-qr-code/"

Dim request As WinHttpRequest

Set request = CreateObject("WinHttp.WinHttpRequest.5.1")

With request
  .SetTimeouts _
    ResolveTimeout:=60000, _
    ConnectTimeout:=60000, _
    SendTimeout:=60000, _
    ReceiveTimeout:=60000
  .Open _
    Method:="GET", _
    URL:=QRServerUrlStem & "?data=" & encodeURL(TextToEncode) & "&size=100x100", _
    Async:=False
  ' set a few options
  .Option(WinHttpRequestOption_UserAgentString) = "Test QR code retrieval"
  .Option(WinHttpRequestOption_EnableRedirects) = True
  .Option(WinHttpRequestOption_EnableHttpsToHttpRedirects) = True
  ' issue the request
  .Send
  ' in this example we want to save the response,
  ' which is basically a .png image, directly to a file.
  ' FileSystemObject is not a good choice for binary data
  ' We might be able to use the ResponseStream property of the request
  ' which is an Istream but I think that is a little bit hard
  ' to work with in VBA
  ' But we also get a bytearray back so we can use ADO
  Call SaveBinaryData(FullName:=FullName, ByteArray:=.ResponseBody)
  
End With

Set request = Nothing
End Function

Sub testEncodeURL()
Debug.Print encodeURL("Cirinnà, Abbascià")
End Sub

Function encodeURL(str As String) As String
' This came from http://stackoverflow/questions/218181
' which also shows some other ways to do the encoding
Dim ScriptEngine As ScriptControl
Set ScriptEngine = New ScriptControl
ScriptEngine.Language = "JScript"

ScriptEngine.AddCode "function encode(str) {return encodeURIComponent(str);}"
Dim encoded As String
encoded = ScriptEngine.Run("encode", str)
encodeURL = encoded
End Function

Function SaveBinaryData(FullName As String, ByteArray)

Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2

Dim BinaryStream As ADODB.Stream

Set BinaryStream = CreateObject("ADODB.Stream")
BinaryStream.Type = adTypeBinary
BinaryStream.Open

' Write the ata to the stream
BinaryStream.Write ByteArray

' Save the stream to disk
BinaryStream.SaveToFile FullName, adSaveCreateOverWrite

End Function

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks Peter Jamieson for the very good explanation, I will try to put into practice what you've recommended and posted to me, if I still need your help do not hesitate to ask you. Many thanks to all who have helped me in this matter, without excluding anyone. You are fantastic and very professional.

See you soon, hello Nicola.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.