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 

="" & 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


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


Set Pshp = Nothing



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

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