Excel VBA 產生 QRCode 與自動調整大小

這兩天在 ptt 上面找有什麼接案的工作,一般會上 ptt 找人接的案子,都是預算比較低的、簡單些的。剛好有看到一個要把 Excel 欄位產生 QRCODE 的案子,想說這案子是該用程式的,怎麼會是找人手動轉的呢?

手動轉的話,案主是寫說時間大概要六小時!真是 OMG… 由於白天都在上班,就先簡單的寫個信給他,問有沒有考慮用程式的,有的話可聯絡我。不過真的就從此無聲無息了…下班後,憑著一股對程式的熱情,快快的把程式寫出來。

一開始覺得很簡單,後來又覺得不簡單,最後又覺得簡單了。其實是不簡單的部份是在於對 Excel 表格操作的部份,抓欄位、產生 QRCODE、下載圖檔,這些都不是問題。Excel VBA 才是不熟悉的部份,好在網路上都有資料找的到。

雖然最後寫出來了,可是案主已經找到人。我還是把我的程式錄影寄給他看了一下,雖然沒回應,不過能夠寫出一些真實需求和有收獲的程式,還是很開心。

文末會附上完整的程式碼、xlsm檔、範例影片。怕 .xlsm 檔有毒的,就把程式碼手貼進自己的 .xlsm 檔吧。

注意:本文及其附件僅適合於32位元的系統!

 

 

EXCEL VBA QRCODE 產生巨集

EXCEL 的程式化操作一直是靠 VBA 來完成的,也是本文主要寫的東西。而產生 QRCODE 的部份,基本上是透過 HTTP GET,藉由網路上的服務,來產生 QRCODE 圖檔。雖然這個服務免費、速度又快,但風險就是可能使用太密集,有被阻擋的風險。

所以另外有再研究如何自己產生 QRCODE ,就分另一個 Linux 程式文章再介紹 (這裡)。

程式流程

簡單描述一下程式流程

  1. 需針對ColumnA的每一儲存格,都產生一個 QRCODE,然後將 QRCODE 放在對應ColumnE的位置。
  2. 產生 QRCODE  的方式,就是把儲存格內的文字代入 “https://api.qrserver.com/v1/create-qr-code/?size=150×150&data=文字內容“內,然後抓取這個網址的檔案。這就是一個 QRCODE 的 png 檔了,把它抓下存起來。
  3. 將圖片插入ColumnE相應的位置,並設定圖檔隨著儲存格改變大小和位置。
  4. 完成。
關鍵技術

中間比較關鍵的技術的地方有幾個

  1. 如何使用 VBA 插入圖片,並跟該儲存格大小、位置連動
  2. 如何使用 VBA 下載圖片
  3. 如何把用戶資料轉成 URL 編碼,因為資料裡可能有空白、特殊符號、中文,這些都需經過轉換才能用於 HTTP GET 的欄位。
  4. 找到網路上產生 QRCODE  的免費服務

第(4)點算不上什麼關鍵,但有找到這個服務,的確還蠻重要的。很乾脆的直接給你圖檔,沒夾一堆廣告之類的。

 

 

程式碼說明

插入圖片(QRCODE圖檔),並與儲存格大小、位置連動:這部份不難,只是一開始找到的都是舊資料。據說 Office 2007 和之後的版本方法不同。我是 2010,所以還挖了一陣子,當然自己沒那麼熟 VBA 也是原因。

 

從網址下載檔案:這部份是網路抓的,主要是呼叫 DLL API。沒仔細研究,直接使用。

 

轉換URL編碼:也是從網路上抓的,直接用。確定中文跟空白沒問題。

產生取得 QRCODE,並放置指定位置:這個函式會把src儲存格內的文字,產生QRCODE,放在dst儲存格的位置。src內容會先被編成URL可接受的格式,然後使用網路上的文字轉QRCODE服務,產生圖檔。Download 後放置在指定位置,並設定成隨儲存格放大縮小。

 

主巨集《更新 QRCODE》:我們採取了一點技巧來增加使用的便利性。假設在A1有個文字要轉成 QRCODE,我們會將產生的QRCODE放在A5的位置(其實是在之上,A5還是可以有內容),然後將A1的內容複製到A5去。當下載次要再產生一次時,會檢查A5是否等於A1(是否有變動)。有的話,則會重新產生,否則不需要。這樣對整列更新時,可有效加快速度,也省去人工決定是否要更新的檢查。

 

主巨集《移除 QRCODE》:有產生,就有移除的功能。記得剛插入圖檔時有命名嗎?這個函式會找出該名稱的所有圖檔,並把它刪除 。另外在Column5的資料,也會被全部清空(假設最多1000筆)。

 

 

展示影片

展示的影片位在 https://www.youtube.com/watch?v=-rlkrQqnd2o。

 

獨立程式版

後來有再寫一篇利用獨立程式產生 qrcode,而無需仰賴網路服務的,有需求的可參考這篇

檔案下載

  1. EXCEL 自動產生QRCODE純程式碼 (Text檔)
  2. EXCEL 自動產生QRCODE,完整檔案 (.xlsm) 檔案 –> 會有巨集停用警告,請將它啟用。或者自己再開一個 .xlsm 檔,將上一個文字的檔的內容自己貼上。
  3. *** 測試時發現在沒有 D 槽的機器上會有問題,請把巨集的內容做取代。把 “d:\kkk.png” 取代成 Environ(“TEMP”) + “\kkk.png”

Latest Comments

  1. 胖橘 2018-10-01
    • 蔡可彼 2018-10-02
  2. 汪淑慧 2019-01-29
    • 蔡可彼 2019-01-29
  3. newbie 2019-04-26
  4. Dian Hudayah R 2019-10-09
    • 蔡可彼 2019-10-19

Dian Hudayah R 發表迴響 取消回覆