|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
' 定义一个名为 AutoPrintWithReplacementAndQRCode 的子过程 Sub AutoPrintWithReplacementAndQRCode() '定义变量用于存储数据页的最后一行行数、单元格引用、索引、打印次数、循环计数器等 Dim LastRowNumber As Long Dim CellReference As Range Dim dataRowIndex As Long Dim PrintCount As Long Dim LoopCounter As Long Dim oleObject As Object Dim shp As Shape '设置数据页和展示页的工作表对象 Dim DataSheet As Worksheet Dim ShowSheet As Worksheet Set DataSheet = Sheets("数据页") Set ShowSheet = Sheets("展示页") '设置展示页的页面设置，包括边距等 With ShowSheet.PageSetup .LeftMargin = 0 .RightMargin = 0 .TopMargin = 0 .BottomMargin = 0 .HeaderMargin = 0 End With '通过循环找到数据页第一列非隐藏且非空的最后一行 ' 100 进行遍历 For dataRowIndex = 1 To 100 Set CellReference = DataSheet.Cells(dataRowIndex, 1) '如果当前行未隐藏且单元格不为空，则更新最后一行的行数 If CellReference.EntireRow.Hidden = False And Not IsEmpty(CellReference) Then LastRowNumber = CellReference.Row End If Next dataRowIndex '获取展示页中 AJ2 单元格中的打印次数，并转换为数值类型 PrintCount = Val(ShowSheet.Range("AJ2").Value) '下是二维码相关的处理逻辑 '定义变量判断是否安装了特定的条形码控件 'Dim barcodeControlInstalled As Boolean 'Dim oleObjectCount As Long 'Dim oleObjectLoopCounter As Long 'oleObjectCount = ActiveSheet.OLEObjects.Count 'For oleObjectLoopCounter = 1 To oleObjectCount ' Set oleObject = ActiveSheet.OLEObjects(oleObjectLoopCounter) ' If InStr(1, oleObject.Name, "BarCode", vbTextCompare) > 0 Then ' barcodeControlInstalled = True ' Debug.Print "检测到包含'BarCode'的控件" ' Exit For ' Else ' Debug.Print "未检测到包含'BarCode'的控件" ' End If 'Next oleObjectLoopCounter '开始主要的循环操作 For LoopCounter = 1 To LastRowNumber '如果数据页的当前行未隐藏且第一列单元格不为空 If DataSheet.Rows(LoopCounter).Hidden = False And Not IsEmpty(DataSheet.Cells(LoopCounter, 1)) Then '将数据页的数据复制到展示页的指定单元格 ShowSheet.Range("Y20").Value = DataSheet.Cells(LoopCounter, 1).Value ShowSheet.Range("P21").Value = DataSheet.Cells(LoopCounter, 2).Value ShowSheet.Range("D28").Value = DataSheet.Cells(LoopCounter, 3).Value '如果未安装特定的条形码控件 'If Not barcodeControlInstalled Then With ActiveSheet '遍历当前工作表中的所有形状，删除指定位置的图片形状 For Each shp In .Shapes If shp.Type = msoPicture And shp.Left = Range("B14").Left And shp.Top = Range("B14").Top Then shp.Delete End If Next shp '根据 D28 单元格的值生成新的二维码图片并添加到指定位置 Dim url As String Dim cellContent As String cellContent = Range("D28").Value url = "https://api.snote.cn/QRCode.php?size=300&text=" & Replace(cellContent, " ", "%20") .Shapes.AddPicture url, False, True, Range("B14").Left, Range("B14").Top, 157, 157 Debug.Print "二维码删除和生成操作完成。" End With 'Else ' Debug.Print "检测到支持的条形码控件，不执行二维码删除和生成操作。" 'End If '内部循环，根据打印次数进行打印操作 Dim InnerLoopCounter As Long For InnerLoopCounter = 1 To PrintCount ShowSheet.Range("A1:AC29").PrintOut Next InnerLoopCounter End If Next LoopCounter '在展示页的 D28 单元格设置特定的值 ShowSheet.Cells(28, "D").Value = "新乡片区 新乡和平营业部 张子屾" End Sub |
发表回复