AutoPrintWithReplace

' 定义一个名为 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

' 定义一个名为 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

AutoPrintWithReplace

发表回复 取消回复

发表回复取消回复