在Excel中插入分页符以拆分数据以打印.pdf [英] Insert page breaks in Excel to split data for printing .pdf

问题描述

我正在使用一个可以包含动态行的Excel文件.我将其导出到.pdf，并且效果很好.问题在于所有内容都位于.pdf文件中的单个页面上.

I am working with an Excel file that can contains dynamic rows. I export it to .pdf and it works well. The matter is that all the content is on a single page in the .pdf file.

反正在我的VBA中为我的.pdf文件插入分页符吗?

Is there anyway to insert page breaks in my VBA for my .pdf file?

编辑:这是我的分页代码.似乎无法上班

Here is my code for the page break. Won't seems to get working

If count > 30 Then ' count is the number of row. Break at every 15 rows
        Set ws = Range("A1", "K" & count) 'The range of the document
        Dim ii As Integer
        ii = 21 ' first page break
        While count > 0
            If count > 15 Then ' no page break if there is less than 15 rows
             ws.Rows(ii).EntireRow.PageBreak = xlPageBreakManual
            End If               
            ii = ii + 15
            count = count - 15
        Wend
End If

我没有找到创建pdf文件中的分页符的任何方法.但是，我仍然可以解决我的问题.我只是找到了一个用于vba的模块，以运行外部进程并等待它们.它是 ShellAndWait

EDIT 2: I did not find any way to create a page break into the pdf file. However, I still resolve my problem. I just find a module for vba to run external process and wait for them. it is ShellAndWait

我将pdf文件的每一页另存为临时文档，并将其路径/名称发送到我创建的C#控制台应用程序中.我正在使用 PDFsharp 库来处理pdf文档，然后将它们合并为1个文件.

I save every page of my pdf file as a temporary document and send it's path/name to a C# console application that I've created. I am using the library PDFsharp to process the pdf documents and then merge them to 1 file.

希望它可以帮助某人.

推荐答案

您可以这样设置分页符:

You can setup the page breaks like this :

Worksheets("Sheet1").HPageBreaks.Add Before:=Worksheets("Sheet1").Rows(25)
Worksheets("Sheet1").VPageBreaks.Add Before:=Worksheets("Sheet1").Columns("J")

Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakManual
Worksheets("Sheet1").Columns("J").PageBreak = xlPageBreakManual

仅供参考，我没有设法使Range.PageBreak正常工作

FYI, I didn't managed to make the Range.PageBreak to work

或者对于范围，像这样:

Or for a range, something like this :

With Range(blabla)
    .Rows(.Rows.Count).EntireRow.PageBreak = xlPageBreakManual
    '.Rows(Int(.Rows.Count/2)).EntireRow.PageBreak = xlPageBreakManual
    .Columns(.Columns.Count).EntireColumn.PageBreak = xlPageBreakManual
End With

---

这是您的更正代码:

Sub test_Wanceslas()
Dim Ws As Worksheet, _
    Rg As Range, _
    LastRow As Long, _
    Count As Long, _
    ii As Long

ii = 21 ' first page break
Set Ws = ActiveSheet

With Ws
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    Count = LastRow
    Set Rg = .Range("A1", "K" & Count) 'The range of the document

    If LastRow > 30 Then ' count is the number of row. Break at every 15 rows
        .ResetAllPageBreaks
        .PageSetup.PrintArea = Rg.Address

        While Count > 0 And ii < LastRow
            If Count > 15 Then ' no page break if there is less than 15 rows left
                '.Rows(ii).PageBreak = xlPageBreakManual
                .HPageBreaks.Add Before:=.Rows(ii)
            End If
            ii = ii + 15
            Count = Count - 15
        Wend
    End If
End With

End Sub

这篇关于在Excel中插入分页符以拆分数据以打印.pdf的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！