Produce QR Codes in Excel Using Google API

[matt1_7]

Evening forum.

I am trying to make a ring-binder label with a QR code on it using data from excel and Google's Infographics API:

http://code.google.com/apis/chart/infographics/docs/overview.html

My excel sheet uses a Concatenate formula...
=CONCATENATE("https://chart.googleapis.com/chart?chs=250x250&cht=qr&chl=",C2," ,",C4,", ",C6,", ",C8,", ",C10)
...to make a hyperlink for Google's API...
https://chart.googleapis.com/chart?...mpany ,Accounts, Payrol Summary, 40848, 40909
...which creates a QR code image file when you put the hyperlink in a browser.

I have found the following excel VBA macro that will insert the QR code from the web into my spreadsheet:

Sub TestInsertPicture()
InsertPicture "https://chart.googleapis.com/chart?chs=250x250&cht=qr&chl=Test Company ,Accounts, Payrol Summary, 40848, 40909", _
Range("D10"), True, True
End Sub

Sub InsertPicture(PictureFileName As String, TargetCell As Range, _
CenterH As Boolean, CenterV As Boolean)
' inserts a picture at the top left position of TargetCell
' the picture can be centered horizontally and/or vertically
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
' determine positions
With TargetCell
t = .Top
l = .Left
If CenterH Then
w = .Offset(0, 1).Left - .Left
l = l + w / 2 - p.Width / 2
If l < 1 Then l = 1
End If
If CenterV Then
h = .Offset(1, 0).Top - .Top
t = t + h / 2 - p.Height / 2
If t < 1 Then t = 1
End If
End With
' position picture
With p
.Top = t
.Left = l
End With
Set p = Nothing

My problem is I do not know how to write the VBA script to look up the hyperlink produced by the concatenate formula from a cell in the workbook (in my case B16).

Any help would be welcome.

Many thanks - Matt.

 

[GlennUK]

Have you tried:

Sub TestInsertPicture()
InsertPicture Range("B16").Value, _
Range("D10"), True, True
End Sub

?

 

[matt1_7]

Thanks Glenn! That works a treat.
Regards, Matt

 

[GlennUK]

My pleasure!

 

[mimimimi]

Have you tried:

Sub TestInsertPicture()
InsertPicture Range("B16").Value, _
Range("D10"), True, True
End Sub

?

great codes there. thanks man. and i am trying to do this excel vbscript to generate qr code. do you think is it possible? any suggestion?