[Excel VBA] Generate QR Code (1)

 Excel   VBA    QR Code  

▌Introduction

---

我在此篇文章將利用Excel的VBA環境，將儲存格內容傳送到 google charts api 以產生QR Code圖片，並從http response將此圖片透過本機站存檔的方式再加入到Excel內容中。

▋相關文章

[Excel VBA] Generate QR Code (1)

[Excel VBA] Generate QR Code (2)

▌Google Charts Api

---

請至 developers.google.com 查看更多API的參數和使用方式。

                 

▌Implement

---

▋加入按鈕和資料

請從【開發人員】→【插入】，加入一個ActiveX控制項按鈕。

然後在第一個儲存格放入要轉成QR Code的資料。

▋撰寫按鈕Click事件

Private Sub QRCodeGen_Click()     Dim qrcodeImgPath As String     Dim qrcodeValue As String     qrcodeImgPath = ActiveWorkbook.Path & "\QRCode.png"  'QR Code暫存圖片名稱     qrcodeValue = Cells(1, 1).value     'Create QR Code image     Call getQRCodeImg(qrcodeImgPath, qrcodeValue)     'Append QR Code image to sheet     Call appendQRCode(qrcodeImgPath) End Sub

PS. ActiveWorkbook.Path 會取得目前的Excel所在的資料夾路徑。

▋送出http Request給Google Charts API，並儲存產生的QR Code至本機

Private Sub getQRCodeImg(imgPath As String, value As String)     Dim fileNum As Long     Dim apiUri As String     Dim fileData() As Byte     Dim tmpImgPath As String     Dim winHttpReq As Object     Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")     apiUri = "https://chart.googleapis.com/chart?cht=qr&chs=130x130&chl=" + value     winHttpReq.Open "GET", apiUri, False     winHttpReq.Send     fileData = winHttpReq.ResponseBody     Open imgPath For Binary Access Write As #1     Put #1, 1, fileData     Close #1 End Sub

▋將QR Code圖片放到Excel儲存格

Private Sub appendQRCode(qrcodeImgPath As String)     Dim img As Picture     Set img = ActiveSheet.Pictures.Insert(qrcodeImgPath)     With img         .ShapeRange.LockAspectRatio = msoFalse         .Top = ActiveSheet.Cells(2, 1).Top         .Left = ActiveSheet.Cells(2, 1).Left     End With End Sub

▌Demo

---

▌Reference

---

▋Stackoverflow : Get picture as http GET response and insert to spreadsheet without saving the picture

▋Stackoverflow : How to get current working directory using vba?