Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VBScript
At first, I apologize if this question is not apropriate here.
But this "QRCodeSample.exe" looks very good!
I'd like to use "ThoughtWorks.QRCode.dll" as a function in excel.
Would someone please show me sample code for VBA? Way of ENCODE is enough for me.
Posted 5-Mar-13 23:45pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
I am not sure whether the binary distribution is complied in a manner to allow it to be used from VBA. Here you can find the great tutorial on how to create C#/VB.NET assembly for use with VBA. Download the ThoughtWork's QRCode library source and follow the instruction given in the above mentioned article and you should be able to use the dll from VBA.
 
regards,
  Permalink  
Comments
Member 9884524 at 6-Mar-13 18:20pm
   
Thaks to comment. I'll try to learn.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

This is a minimalistic example of how to create qr code on excel vb.net.
 
Dim wsh As Worksheet
Set wsh = Excel.ActiveSheet
 
Dim shp As Shape
Set shp = wsh.Shapes.AddOLEObject("STROKESCRIBE.StrokeScribeCtrl.1")
 
shp.LockAspectRatio = msoFalse
shp.Width = Application.CentimetersToPoints(3)
shp.Height = shp.Width
shp.Left = Application.InchesToPoints(1)
shp.Top = Application.InchesToPoints(1)
 
Dim qr_code As StrokeScribe
Set qr_code = shp.OLEFormat.Object.Object
qr_code.Alphabet = QRCODE
qr_code.QrECL = M
qr_code.FontColor = RGB(0, 0, 255)
 
Dim data As String
data = wsh.Range("A1").Text
qr_code.Text = data
 
To encode a range of cells in QR Code, modify the code as following:
 
Dim data As String
rng = wsh.Range("A1:D1")
For Each cell In rng
data = data & cell & ";"
Next
qr_code.Text = data
 
You can put any delimiter between cell values. To insert ASCII TAB, replace ";" with Chr(9).
 
To encode ASCII control characters as a plain text, use tilde sequences:
 
qr_code.ProcessTilde = True
qr_code.Text = "TEXT1~0x0d~0x0aTEXT2" ' ~0x0d~0x0a are hexadecimal codes 0D, 0A of ASCII
 
'The same text is encoded using Chr() function:
qr_code.Text = "TEXT1" & Chr(13) & Chr(10) & "TEXT2"
 
To delete all barcode objects placed on the active Excel worksheet, use this VBA code:
 
Dim sh As Shape
For Each sh In Excel.ActiveSheet.Shapes
If sh.Type = msoOLEControlObject Then
If sh.OLEFormat.progID = "STROKESCRIBE.StrokeScribeCtrl.1" Then
sh.Delete
End If
End If
Next
 
The ActiveX doesn't draw a text label below 2D barcodes - it's not required by ISO standards. If you really need to put a text label near QR Code, use the following VBA code:
 
Dim sh_label As Shape
Set sh_label = wsh.Shapes.AddLabel(msoTextOrientationHorizontal, 0, 0, 0, 0)
 
sh_label.TextFrame.Characters.Text = "text label - text label - text label"
 
sh_label.TextFrame.AutoSize = False
sh_label.TextFrame.HorizontalAlignment = xlHAlignCenter
 
sh_label.Left = shp.Left
sh_label.Top = shp.Top + shp.Height
sh_label.Width = shp.Width
sh_label.Height = Application.InchesToPoints(0.5)
 
sh_label.TextFrame.AutoMargins = False
sh_label.TextFrame.MarginTop = 0
sh_label.TextFrame.MarginBottom = 0
sh_label.TextFrame.MarginLeft = 0
sh_label.TextFrame.MarginRight = 0
 
QR Code with a text label
 
To delete all text labels from a worksheet, use the following code:
 
Dim sh As Shape
For Each sh In Excel.ActiveSheet.Shapes
If sh.Type = msoTextBox Then
sh.Delete
End If
Next
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 390
1 Sergey Alexandrovich Kryukov 329
2 BillWoodruff 210
3 Afzaal Ahmad Zeeshan 204
4 CPallini 185
0 OriginalGriff 5,515
1 DamithSL 4,451
2 Maciej Los 3,902
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,175


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 29 Oct 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100