麻辣家族討論版版 - Powered by Discuz! Board

Sub Make_BarCode()
Dim i, j As Integer
With Worksheets("Data")
j = Range("C1").End(xlDown).Row
For i = 2 To j
Range("A" & i).Select
filepath = "https://api.qrserver.com/v1/create-qr-code/?size=95x95&data=" & Range("C1").Value & ":" & Range("C" & i).Value & vbLf & Range("D1").Value & ":" & Range("D" & i).Value & vbLf & Range("E1").Value & ":" & Range("E" & i).Value & vbLf & Range("F1").Value & ":" & Range("F" & i).Value & vbLf & Range("G1").Value & ":" & Range("G" & i).Value & vbLf & Range("H1").Value & ":" & Range("H" & i).Value & vbLf & Range("I1").Value & ":" & Range("I" & i).Value & vbLf & Range("J1").Value & ":" & Range("J" & i).Value & vbLf & Range("K1").Value & ":" & Range("K" & i).Value & vbLf & Range("L1").Value & ":" & Range("L" & i).Value & vbLf & Range("M1").Value & ":" & Range("M" & i).Value
With ActiveSheet.Pictures.Insert(filepath)
H1 = .TopLeftCell.Height
H2 = .Height
.Top = .TopLeftCell.Top + (H1 - H2) / 2
H1 = .TopLeftCell.Width
H2 = .Width
.Left = .TopLeftCell.Left + (H1 - H2) / 2
End With
Range("B" & i) = "*" & Range("C" & i) & "*"
Range("B" & i).Font.Name = "Bar-Code 39"
Range("B" & i).Font.Size = "25"
Next i
End With
End Sub
Sub Remove_QR_Code()
Dim i, j As Integer
With Worksheets("Data")
For Each pic In ActiveSheet.Pictures
pic.Delete
Next pic
j = Range("B1").End(xlDown).Row
For i = 2 To j
Range("B" & i) = ""
Range("B" & i).Font.Name = "Calibri"
Next i
End With
End Sub

複製代碼