20180429 xlVBA套打单据自适应列宽

Sub TestAutoAdjustColumnWidthBaseOnModel()

    Set ModelSheet = ThisWorkbook.Worksheets("单据模板")

    Set PrintSheet = ThisWorkbook.Worksheets("批量打印")

    AutoAdjustColumnWidthBaseOnModel ModelSheet, PrintSheet

End Sub

Sub AutoAdjustColumnWidthBaseOnModel(ByVal ModelSheet As Worksheet, ByVal PrintSheet As Worksheet, Optional modelCountInOnePage As Variant)

    Dim ModelRng As Range '模板单元格

    Dim modelColumnWidth() As Double '模板列宽数据

    Dim modelColumnCount As Long '模板行数

    Dim sumModelColumnWidth As Double '模板累计列宽

    Dim adjustScale As Double '调整比例

    'Dim modelCountInOnePage As Long '一页打印几个单据模板

    Dim BreakColumn As Long '垂直分页符位置

    Dim FirstPageSumColumnWidth As Double '累计首页列宽

    Dim ColumnsInOnePage  As Long  '每页打印多少行

    Dim i As Long, m As Long '行号

    With ModelSheet

        If Application.WorksheetFunction.Count(.Cells) > 0 Then

            '计数防止计算行号发生错误

            EndRow = .Cells.Find("*", .Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row + 1

            EndCol = .Cells.Find("*", .Cells(1, 1), xlValues, xlWhole, xlByColumns, xlPrevious).Column

            '获取单据模板单元格区域

            Set ModelRng = .Range(.Cells(1, 1), .Cells(EndRow, EndCol))

            Debug.Print ModelRng.Address

            '获取模板单元格行数和累计列宽

            modelColumnCount = ModelRng.Columns.Count

            ReDim modelColumnWidth(1 To modelColumnCount)

            sumModelColumnWidth = 0

            For i = 1 To modelColumnCount

                modelColumnWidth(i) = ModelRng.Columns(i).ColumnWidth

                sumModelColumnWidth = sumModelColumnWidth + ModelRng.Columns(i).ColumnWidth

            Next i

            Debug.Print sumModelColumnWidth

            '记录列宽

        End If

    End With

    '

    With PrintSheet

        Debug.Print "垂直分页符个数："; .VPageBreaks.Count

        '先判断是否有垂直分页符，如果没有则退出

        If .VPageBreaks.Count > 0 Then

            '获取第一页与第二页分页符所在的单元格

            BreakColumn = .VPageBreaks(1).Location.Column

            Debug.Print "首页分页符所在的行号："; BreakColumn

            '累计第一页所有行的高度

            i = 1

            Do While i < BreakColumn

                FirstPageSumColumnWidth = FirstPageSumColumnWidth + .Columns(i).ColumnWidth

                i = i + 1

            Loop

            'Stop

            Debug.Print FirstPageSumColumnWidth

            '获取第一页最后一个成绩单末尾的空白行行号

            If IsMissing(modelCountInOnePage) Then

                ColumnsInOnePage = BreakColumn

                Do While Application.WorksheetFunction.Count(.Columns(ColumnsInOnePage)) > 0

                    ColumnsInOnePage = ColumnsInOnePage - 1

                Loop

                Debug.Print "首页最后一个成绩单截止行号1："; ColumnsInOnePage

                ColumnsInOnePage = Application.WorksheetFunction.Max(BreakColumn, modelColumnCount)

                Debug.Print "首页最后一个成绩单截止行号2："; ColumnsInOnePage

                modelCountInOnePage = ColumnsInOnePage / modelColumnCount

                Debug.Print "每一页放置多少个单据："; modelCountInOnePage

            End If

            '计算调整比例

            adjustScale = FirstPageSumColumnWidth / (sumModelColumnWidth * modelCountInOnePage)

            Debug.Print adjustScale

            '调整

            EndCol = .Cells.Find("*", .Cells(1, 1), xlValues, xlWhole, xlByColumns, xlPrevious).Column

            m = 0

            For i = 1 To EndCol

                m = m + 1

                .Columns(i).ColumnWidth = modelColumnWidth(m) * adjustScale

                If m = modelColumnCount Then m = 0 '逐个单据调整

            Next i

        End If

    End With

End Sub

20180429 xlVBA套打单据自适应列宽的相关教程结束。