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
ليست هناك تعليقات:
إرسال تعليق