跳到主要內容

如何在Excel中基於單元格值創建二維碼？

您知道如何根據Excel中的特定單元格值創建QR碼嗎？ 本文將向您詳細介紹實現該方法的方法。

使用條形碼控制和VBA代碼基於單元格值創建QR碼
使用驚人的工具根據單元格值輕鬆批量創建多個二維碼

使用條形碼控制和VBA代碼基於單元格值創建QR碼

條形碼控件可以幫助您基於Excel中的單元格值快速創建QR碼。 請執行以下操作。

1.打開工作表，其中包含您要創建QR Code的單元格值。

2。 點擊 開發者 > 插入 > 更多控制。 看截圖：

3。 在裡面 更多控件 窗口，檢查 Microsoft條碼控制16.0 or Microsoft條碼控制15.0.

4.如果在“更多控件”窗口中找不到條形碼控件，或者條形碼控件不是16.0或15.0，則需要通過單擊以下鏈接下載條形碼控件文件： 下載條碼控制。 如果“更多控件”窗口中有“條形碼控件16.0”或“ 15.0”，請跳到下面 步驟10.

下載文件後，將其解壓縮，然後使用在Excel中下載的條形碼控件更新條形碼控件，如下所示。

5.關閉所有Excel工作簿，轉到 開始 部分，找到 Excel 應用並右鍵單擊它，然後選擇 以管理員身份運行 從上下文菜單中。 看截圖：

6.在開幕 用戶帳戶控制 對話框中，單擊 是 按鈕。

7.然後創建一個新的工作簿。 請點擊 開發者 > 插入 > 更多控制。 看截圖：

8。 在裡面 更多控件 窗口中，單擊 註冊自定義 按鈕，找到並選擇下載的QR碼OCX文件之一，然後單擊 已提交 按鈕。 看截圖：

9。 點擊 OK 按鈕，當它返回 更多控件 窗口以完成條形碼控制的更新。 然後重新打開工作簿，其中包含您要創建QR Code的單元格值。

10。 右鍵單擊工作表標籤，然後單擊 查看代碼 從上下文菜單中打開 Microsoft Visual Basic for Applications 窗口。 然後將下面的VAB代碼複製並粘貼到“代碼”窗口中。 最後按 其他 + Q 退出鍵 Microsoft Visual Basic for Applications 窗口。

VBA代碼：在Excel中創建QR代碼

Sub setQR()
'Updated by Extendoffice 2018/8/22
    Dim xSRg As Range
    Dim xRRg As Range
    Dim xObjOLE As OLEObject
    On Error Resume Next
    Set xSRg = Application.InputBox("Please select the cell you will create QR code based on", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    Set xRRg = Application.InputBox("Select a cell to place the QR code", "Kutools for Excel", , , , , , 8)
    If xRRg Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xObjOLE = ActiveSheet.OLEObjects.Add("BARCODE.BarCodeCtrl.1")
    xObjOLE.Object.Style = 11
    xObjOLE.Object.Value = xSRg.Text
    ActiveSheet.Shapes.Item(xObjOLE.Name).Copy
    ActiveSheet.Paste xRRg
    xObjOLE.Delete
    Application.ScreenUpdating = True
End Sub

11。 點擊 開發者 > 插入 > 按鈕（表單控件） 如下圖所示。

12.在當前工作表中繪製一個按鈕。 在彈出 分配宏 對話框中選擇 設置二維碼 在框中，然後單擊 OK 按鈕。

13。 關閉 設計模式 通過點擊 開發者 > 設計模式.

14.單擊開始中的按鈕 Excel的Kutools 對話框中，選擇您要創建QR Code的單元格，然後單擊 OK.

15.在第二 Excel的Kutools 對話框中，選擇一個單元以放置QR碼。 看截圖：

然後，QR碼將立即插入指定的單元格中。 重複步驟14到15，以完成所有創建的QR碼。 看截圖：

使用驚人的工具根據單元格值輕鬆批量創建多個二維碼

老實說，上述方法並不容易處理，因為它有其自身的局限性。 這裡強烈推薦 插入二維碼 的特點 Excel的Kutools. 此功能可幫助您根據 Excel 中指定的單元格值，只需單擊幾下即可快速批量插入二維碼。 請按以下步驟完成。

申請前 Excel的Kutools請 首先下載並安裝.

1. 首先，準備好要基於的值來創建 QR 碼。

2。 點擊 庫工具 > 插入 > 插入二維碼.

提示： 如果您找不到此路徑的功能，請按 其他 + S 同時打開 搜索Kutools函數 框，手動輸入 QR Code 在搜索框中，然後您可以看到 插入二維碼 功能已列出，只需單擊即可激活該功能。 看截圖：

現在，插入 QR 代碼窗格顯示在工作簿的右側。

3。 在裡面 插入二維碼 窗格，您需要配置如下。

3.1）在 數據范圍 框，選擇包含要基於創建二維碼的值的單元格範圍；
3.2）在 插入範圍 框中，選擇一系列空白單元格以輸出二維碼；
3.3）在 選項 部分，配置 尺寸、線條顏色、背景顏色 和 糾錯級別 根據需要獲取二維碼；
注意： 二維碼的大小不能超過 500 像素。
3.4）點擊 產生 按鈕。 看截圖：

4.然後 Excel的Kutools 彈出對話框，單擊 確定。

5. 然後同時創建二維碼。 您需要手動關閉 插入二維碼 如果完成創建所有 QR 碼，則顯示窗格。

點擊即可 了解有關此功能的更多信息...

相關文章：

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (38)
No ratings yet. Be the first to rate!
Ruben
Ruben
This comment was minimized by the moderator on the site
Is it possible to make a payment qr code in excel?
f.e. In cell A1 I've an amout of 10 euro, and in cell B2 I want to have an qr code so anyone can pay me with the qr code?

Thanks.
Reply
0
0
crystal
crystal     Ruben
This comment was minimized by the moderator on the site
Hi Ruben,

Thank you for reaching out with your question. While Kutools for Excel does feature QR code generation, creating a payment QR code directly in Excel for transactional purposes is beyond the scope of this feature. Payment QR codes usually require integration with financial services or payment platforms to ensure secure transactions. I recommend exploring services specifically designed for creating payment QR codes, such as those offered by banks or digital payment systems. These platforms can generate a QR code linked to your payment details securely. Once you have the payment QR code, you could then include it in your Excel document as an image.
Reply
Report
0
0
Siri
Siri
This comment was minimized by the moderator on the site
Hello

The download link provided in step 4 appears to be only Microsoft Barcode Control 14.0. Can anyone suggest a correct link for 16.0 please?

Thank you.
Reply
0
0
H. van Schaijk
H. van Schaijk
This comment was minimized by the moderator on the site
1 Sheets waar in de cellen van de rij random QR codes binnen komen. Waarvan elke QR code automatisch de juiste Sheets openend?
Of de BVA commando codes van de Button om de koppeling maken naar een andere sheets.
Dan ben erg dankbaar voor.
(dit is voor vrijwilligerswerk)

Groetjes,
H. van Schaijk
Reply
0
0
crystal
crystal     H. van Schaijk
This comment was minimized by the moderator on the site
Hi H. van Schaijk,
I can't fix this problem. Sorry about that.
Reply
Report
0
0
Rajendra Narayan Panshikar
Rajendra Narayan Panshikar
This comment was minimized by the moderator on the site
Hi folks,
Below code works fine with me, which is linked to cell range & generates the QR at each row cell 5000 nos, however I would like to stop the QR object generation if the reference cell value is zero / otherway round should generate the QR at adjucent cell if the value at reference cell is more than zero or <>""

Please seek experts assistance!!

thank you in advance

Sub Gen_StockOut_QR()
MsgBox ("Will Generate QR Code for Stock_Out Items at In@Out Sheet")
Sheets("In@Out_Data").Select
ActiveSheet.Unprotect Password:=" "
'Column range set at QR_Code
Range("QR_Code").Select

For Each cell In Selection
'cell.Offset(0, 23).Select ' 1 indicates the position of excel column A1 from the formula/webaddress/site/characters to be used to generate QR code
cell.Offset(0, 0).Select

' My case QRCode is at column 24 hence 24 as column from A1, QR Size 100*100
FilePath = "http://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & WorksheetFunction.EncodeURL(cell.Value) ' here is the cell value

With ActiveSheet.Pictures.Insert(FilePath)
'.ShapeRange.ScaleWidth 0.95, msoFalse, msoScaleFromTopLeft
'.ShapeRange.ScaleHeight 0.95, msoFalse, msoScaleFromTopLeft

End With
Next cell
End Sub
Reply
0
0
mcscouse
mcscouse
This comment was minimized by the moderator on the site
Interesting article - thank you.  How do I remove the custom control from the list please?
Reply
Report
0
0
crystal
crystal     mcscouse
This comment was minimized by the moderator on the site
Hi,Do you mean to remove the Form Control inserted in the VBA code method? If so, you just need to right click it (when there is a context menu listed, click anywhere outside it to hide the context menu), and then press Delete to remove it.
Reply
Report
0
0
Lucas Roberto
Lucas Roberto
This comment was minimized by the moderator on the site
Olá! Para mim, nenhum dos comentários ou esse post funcionou corretamente, mas utilizando o Visual Basic com o código que deixarei abaixo funcionou, pois criou uma função para mim. E para usar a função é só abrir a planilha e digitar: "=QrCode("célula desejada")"




Function QrCode(codetext As String)
Dim URL As String, MyCell As Range
'Para gerar o código, precisa a máquina ter acesso a internet
Set MyCell = Application.Caller
URL = "https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=" & codetext
On Error Resume Next
ActiveSheet.Pictures("QR_" & MyCell.Address(False, False)).Delete
On Error GoTo 0
ActiveSheet.Pictures.Insert(URL).Select
With Selection.ShapeRange(1)
.PictureFormat.CropLeft = 15
.PictureFormat.CropRight = 15
.PictureFormat.CropTop = 15
.PictureFormat.CropBottom = 15
.Name = "QR_" & MyCell.Address(False, False)
.Left = MyCell.Left + 2
.Top = MyCell.Top + 2
End With
QrCode = ""
End Function
Reply
0
0
Jörg
Jörg
This comment was minimized by the moderator on the site
Wie lösche ich die QR Code Grafik wieder ? wenn ich mit den Skript Wie erstelle ich einen QR-Code basierend auf dem Zellenwert in Excel? einen QR Code erzeugt habe ?
Reply
0
0
ChavdarHarchev
ChavdarHarchev
This comment was minimized by the moderator on the site
Hi All,

I modified the VBA code for creation a multi QR codes from selection in excel column

Follow until step 10 then add this code

Sub setQR()

Dim xSRg As Range
Dim xRRg As Range
Dim xObjOLE As OLEObject
On Error Resume Next
Dim srcSelection As Range
Dim srcCell As String
For Each srcSelection In Application.Selection
Dim qrCelltoupdate As String
Dim qrTxt As String
qrCelltoupdate = srcSelection.Offset(0, 1).Address
qrTxt = srcSelection.Text
Worksheets("Sheet1").Range(qrCelltoupdate).Select
Application.ScreenUpdating = False
Set xObjOLE = ActiveSheet.OLEObjects.Add("BARCODE.BarCodeCtrl.1")
xObjOLE.Object.Style = 11
xObjOLE.Object.Value = qrTxt
ActiveSheet.Shapes.Item(xObjOLE.Name).Copy
ActiveSheet.Paste
xObjOLE.Delete
Next srcSelection
'Create Qr code from selection multi cells
End Sub


In step 12
Assign Macro
Chose: Sheet1.setQR

QR codes will be populated in next column

Hope that helps for you all
Reply
Report
0
0
Pierre Galuszka
Pierre Galuszka     ChavdarHarchev
This comment was minimized by the moderator on the site
Hi ChavdarHarchev.
First of all thank You very much for Your nice work.

Is it possible for You to show how the VBA code looks like when it is finished?
Best regards
Pierre Galuszka.
Reply
0
0
Chavdar
Chavdar
This comment was minimized by the moderator on the site
Hi All,
I modified the VBA code for creation a multi QR codes from selection in excel column
Follow until step 10 then add this code
Sub setQR()

Dim xSRg As Range
Dim xRRg As Range
Dim xObjOLE As OLEObject
On Error Resume Next
Dim srcSelection As Range
Dim srcCell As String
For Each srcSelection In Application.Selection
Dim qrCelltoupdate As String
Dim qrTxt As String
qrCelltoupdate = srcSelection.Offset(0, 1).Address
qrTxt = srcSelection.Text
Worksheets("Sheet1").Range(qrCelltoupdate).Select
Application.ScreenUpdating = False
Set xObjOLE = ActiveSheet.OLEObjects.Add("BARCODE.BarCodeCtrl.1")
xObjOLE.Object.Style = 11
xObjOLE.Object.Value = qrTxt
ActiveSheet.Shapes.Item(xObjOLE.Name).Copy
ActiveSheet.Paste
xObjOLE.Delete
Next srcSelection
'Create Qr code from selection multi cells
End Sub


In step 12 Assign Macro Chose: Sheet1.setQR
QR codes will be populated in next column
Hope that helps for you all
Reply
0
0
Mayank Kumar
Mayank Kumar
This comment was minimized by the moderator on the site
I didn't have "Microsoft Barcode Control 16.0" so I down loaded per instruction. And open Excel with Admin mode. Then try to "Register Custom" . But I opened Barcode control folder and select MSBCODE964,OCX. Then there is error message came out. It said "The selected file does not contain self-registrating ActiveX control". What it means? What is the problem. And how can I install that?
Reply
0
0
ChavdarHarchev
ChavdarHarchev     Mayank Kumar
This comment was minimized by the moderator on the site
1)Click File > Options.

2)Click Trust Center > Trust Center Settings > ActiveX Settings.

Click the options below, and then click OK.
* Enable all controls without restrictions and without prompting (not recommended)
3)Click Ok
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations