How to gen QR Code image in Ms Excel

If someone want to use Ms Excel to gen qrcode for product SN number or ID to scan and test for anys reson 

Wonder how to do so, in order to Gen the qr code images on Ms Excel to work with a pesonal purposes or a small home business and so on.

Just hit on the target by open up ms Excel 

in column A type the column head name as Product Sn 

Colum B as Product Name

Column C as Qr code URL

Column D as image


steps two just fill up some information for learning process


1234567891         Notebook       

1234567892        Pen    


GO to colum C enter url as follow 

="https://chart.googleapis.com/chart?chl=" & A2 & " &chs=150x150&cht=qr"

on column D

Pree Alt+F11

copy the VBA code and insert 

"Sub URLPictureInsert()

Dim Pshp As Shape

Dim xRg As Range

Dim xCol As Long

On Error Resume Next

Application.ScreenUpdating = False

Set Rng = ActiveSheet.Range("C2:C5")

For Each cell In Rng

filenam = cell

ActiveSheet.Pictures.Insert(filenam).Select

Set Pshp = Selection.ShapeRange.Item(1)

If Pshp Is Nothing Then GoTo lab

xCol = cell.Column + 1

Set xRg = Cells(cell.Row, xCol)

With Pshp

.LockAspectRatio = msoFalse

If .Width > xRg.Width Then .Width = xRg.Width * 2 / 3

If .Height > xRg.Height Then .Height = xRg.Height * 2 / 3

.Top = xRg.Top + (xRg.Height - .Height) / 2

.Left = xRg.Left + (xRg.Width - .Width) / 2

End With

lab:

Set Pshp = Nothing

Range("C2").Select

Next

Application.ScreenUpdating = True

End Sub

"

Save the code

Press F5 to run the code 

if notthing go wrong you would see the image as below 


NOTE : Set Rng = ActiveSheet.Range("C2:C5") 

up to you for the Range URL to link the qrcode image to gen in my cace is column C2 :C5 




Post a Comment

ใหม่กว่า เก่ากว่า