vba 将多个范围和工作表打印到一个 PDF 文件

Printing multiple ranges and worksheets to one PDF file

提问by Andy

I have a worksheet that often needs to be printed in a specific order of worksheets and ranges. What I'm trying to do is

我有一个工作表，通常需要按特定的工作表和范围顺序打印。我想做的是

1. Print out specific worksheets and ranges in order to a single PDF file.
2. Save the PDF file as a specific date to a certain folder.

1. 打印出特定的工作表和范围以生成单个 PDF 文件。
2. 将 PDF 文件作为特定日期保存到某个文件夹。

The obstacle I'm running into is getting the numerous sheets and ranges printed into one PDF file, as currently each worksheet or range prints out to its own single PDF file.

我遇到的障碍是将大量工作表和范围打印到一个 PDF 文件中，因为目前每个工作表或范围都打印到自己的单个 PDF 文件中。

I'm assuming there's a way to put all the necessary sheets and ranges in something like an array and then do a .PrintOut to that variable, however I haven't gotten this to work.

我假设有一种方法可以将所有必要的工作表和范围放入数组之类的东西中，然后对该变量执行 .PrintOut ，但是我还没有让它起作用。

I'm using Excel 2010 and so I just use the "Adobe PDF" Printer.

我使用的是 Excel 2010，所以我只使用“Adobe PDF”打印机。

How can I print multiple ranges and worksheets to a single .pdf?

如何将多个范围和工作表打印到单个 .pdf？

回答by Dick Kusleika

To print more than one worksheet, you can put the worksheet names in an array like this

要打印多个工作表，您可以将工作表名称放在这样的数组中

Sub PrintArrayOfWorksheets()

    Dim vaWorksheets As Variant

    vaWorksheets = Array("Sheet1", "Sheet2", "Sheet3")

    ThisWorkbook.Worksheets(vaWorksheets).PrintOut

End Sub

Printing to PDF has special problems. If you're using the "official" add-in for creating PDFs it will probably work as above (sorry I can't try it right now). In the past when I've worked with other printer drivers that print to PDF, I found that all (or at least most) of the PageSetup properties had to be identical on the sheets or it would print in multiple jobs. That means no shrink-to-fit and all the margins have to be the same. I can't remember all the properties that caused problems, just that those two definitely did and Orientation definitely didn't.

打印到 PDF 有特殊问题。如果您使用“官方”加载项来创建 PDF，它可能会按上述方式工作（抱歉，我现在无法尝试）。过去，当我使用其他打印为 PDF 的打印机驱动程序时，我发现所有（或至少大部分）PageSetup 属性在工作表上必须相同，否则它会在多个作业中打印。这意味着没有收缩以适应并且所有边距都必须相同。我不记得所有引起问题的属性，只是这两个属性确实有，而 Orientation 绝对没有。

To limit the ranges that print, you need to set the print area on each sheet. You can use the PageSetup.PrintArea property to do it in code. But if doesn't change, it's probably just as well to do it manually one time and it will persist.

要限制打印范围，您需要在每张纸上设置打印区域。您可以使用 PageSetup.PrintArea 属性在代码中执行此操作。但是如果没有改变，可能也可以手动进行一次，并且它会一直存在。

回答by Daniel

See code below. This was used to print multiple sheets in a custom order using a VBA Button. It prints to whatever printer is selected prior to clicking the button.

请参阅下面的代码。这用于使用 VBA 按钮以自定义顺序打印多张纸。单击按钮之前，它会打印到选择的任何打印机。

In essence what it does is selects the sheets that you want and rearranges them at the end of the workbook then prints them. after printing it reorders them to the original order.

从本质上讲，它的作用是选择您想要的工作表并在工作簿的末尾重新排列它们，然后打印它们。打印后将它们重新排序为原始顺序。

Option Explicit
Private Sub Print_Sheets_InOrder_Click()
    Dim ary
    Dim orig() As String
    Dim a As Variant, fp As String, i As Variant
    ary = Array("Sheet1", "Sheet4", "Sheet3")
    fp = ActiveWorkbook.Path
    ReDim orig(1 To Sheets.Count)
    For i = 1 To Sheets.Count
        orig(i) = Sheets(i).Name
    Next i
    For Each a In ary
        Sheets(a).Move after:=Sheets(Sheets.Count)
    Next a
    ThisWorkbook.Worksheets(ary).PrintOut
    For Each a In orig
        Sheets(a).Move after:=Sheets(Sheets.Count)
    Next a
End Sub

回答by AMK

Excel 2010 has a print to pdf feature. If each of your worksheets are set up to print the information that you want, then highlight the various worksheets you want to print to one pdf file, then go to "file" and "save-as" and pick save as type "pdf". It should print the worksheets you have highlighted as you have the print set up for each worksheet.

Excel 2010 具有打印到 pdf 的功能。如果您的每个工作表都设置为打印您想要的信息，然后突出显示您想要打印到一个 pdf 文件的各种工作表，然后转到“文件”和“另存为”并选择另存为“pdf”类型. 它应该打印您突出显示的工作表，因为您为每个工作表设置了打印。

回答by Mike

Here is an approach I have tried ... I print the worksheets to a PS file, then double-click on the PS file to generate a PDF. I know it is a workaround, but printing to PS is lightning quick. It requires you have Adobe Acrobat on your PC.

这是我尝试过的一种方法......我将工作表打印到 PS 文件，然后双击 PS 文件以生成 PDF。我知道这是一种解决方法，但打印到 PS 的速度非常快。它要求您的 PC 上装有 Adob​​e Acrobat。

Dim GetPath, SavePath, ReportSuffix
GetPath = ActiveWorkbook.Path & "\"
ReportSuffix = Range("ReportName").Text & ".ps"
SavePath = GetPath & ReportSuffix

Dim PrintSheets

PrintSheets = Array("Exec1", "Intro1", "Intro2", "Intro3", "Intro4", "Intro5")

Sheets(PrintSheets).PrintOut Copies:=1, PrintTofile:=True, PrToFileName:=SavePath