通过 VBA 代码将多张工作表导出为 .pdf

Exporting multiple sheets to .pdf via VBA code

提问by Michael May

I have seen this question but it doesn't quite answer my question - excel vba not exporting pagesetup to pdf correctly

我见过这个问题，但它并没有完全回答我的问题 - excel vba 没有正确地将 pagesetup 导出到 pdf

I have the same problem of the specified ranges in each sheet not being exported when utilising code to create the .pdf output. Everything on each sheet is exported so each worksheet is spread across two or more pages. The print ranges for each sheet are set up to print the specified area onto one sheet.

在使用代码创建 .pdf 输出时，我有相同的问题，即每个工作表中的指定范围没有被导出。每个工作表上的所有内容都被导出，因此每个工作表都分布在两页或更多页上。每张纸的打印范围设置为将指定区域打印到一张纸上。

I have tried to adapt the code in the link but it doesn't seem to work with multiple sheets.

我试图调整链接中的代码，但它似乎不适用于多张纸。

The code I am attempting to use in its unadapted form

我试图以未经调整的形式使用的代码

Sub ClientPDFOutput()

If Sheets("File Data").Range("FD_FileName") = "" Then
'   MsgBox ("Save the file before exporting to a .pdf fomrat"), vbInformation, "Save File"

'   Exit Sub
   Else
End If

ActiveSheet.Unprotect Password:=strPassword

Range("UI_Status") = "Creating client PDF output - Please wait"

SelectSheets

Application.ScreenUpdating = False

Sheets(arrSheets).Select

strFilename = "Test"

Selection.ExportAsFixedFormat _
   Type:=xlTypePDF, _
   filename:=ActiveWorkbook.Path & "\" & strFilename & ".pdf", _
   Quality:=xlQualityStandard, _
   IncludeDocProperties:=True, _
   IgnorePrintAreas:=True, _
   OpenAfterPublish:=False

Sheets("User Input").Select

Range("UI_Status") = "Client .pdf output created and saved"

ActiveSheet.Protect Password:=strPassword

Application.ScreenUpdating = True

MsgBox ("The client output in .pdf format has been created and saved"), vbInformation, ".pdf Created"

End Sub

AND

和

Sub SelectSheets()

Dim rngSheets As Range

Set rngSheets = Sheets("File Data").Range("D_OutputSheets")

If rngSheets.Count = 1 Then
   arrSheets = rngSheets.Value2
   Else
   arrSheets = Application.Transpose(rngSheets.Value2)
End If

End Sub

After a bit more experimenting I established that my print ranges on each of the pages was off so corrected these.

经过更多的实验，我确定我在每一页上的打印范围都关闭了，因此更正了这些。

I added code to select the print range of each sheet before all being selected as part of the sheet array, but the print range in the first sheet of the array is being duplicated across all sheets. So if the range in sheet 1 is B4:P61 and sheet 2 print range is B4:M48, sheet 2 is having B4:P61 selected when the array of sheets is selected.

我添加了代码来选择每个工作表的打印范围，然后才将所有工作表都选为工作表数组的一部分，但是数组的第一张工作表中的打印范围会在所有工作表中复制。因此，如果工作表 1 中的范围是 B4:P61，工作表 2 打印范围是 B4:M48，则在选择工作表阵列时，工作表 2 将选择 B4:P61。

This prints out the selected ranges which is correct for sheet 1 but wrong for the rest of the sheets.

这将打印出对工作表 1 正确但对其余工作表错误的选定范围。

When I do this manually by selecting all the sheets, File, Export then all the sheets print ranges are exported so why when this is recorded and put into a routine it is being ignored?

当我通过选择所有工作表、文件、导出手动执行此操作时，所有工作表打印范围都将被导出，那么为什么将其记录并放入例程时会被忽略？

回答by Limak

Please try to change IgnorePrintAreasproperty.

请尝试更改IgnorePrintAreas属性。

Selection.ExportAsFixedFormat _
   Type:=xlTypePDF, _
   filename:=ActiveWorkbook.Path & "\" & strFilename & ".pdf", _
   Quality:=xlQualityStandard, _
   IncludeDocProperties:=True, _
   IgnorePrintAreas:=False, _
   OpenAfterPublish:=False

回答by Teoanadech

I would advise using the following to ensure your page setup fixes it to a single page:

我建议使用以下内容来确保您的页面设置将其修复为单个页面：

With ActiveSheet.PageSetup
 .FitToPagesWide = 1
 .FitToPagesTall = 1
End With

Delete either the first or second line in the with statement to suit your preferences, or keep both.

删除 with 语句中的第一行或第二行以适合您的偏好，或保留两者。

Further I see you have selection.ExportAsFixedFormat. Ensure that your selected area is correct or use fixed ranges. You may also want to dynamically determine your furthest row/column and add that to the variable PrintRange. Getting the actual usedrange. Set IgnorePrintAreas:=False

此外，我看到你有selection.ExportAsFixedFormat。确保您选择的区域正确或使用固定范围。您可能还想动态确定最远的行/列并将其添加到变量中PrintRange。获取实际 usedrange。放IgnorePrintAreas:=False

Dim PrintRange As Range

Set PrintRange = Range("A1:XX100")

PrintRange.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:=Filename, _
                                Quality:=xlQualityStandard, _
                                IncludeDocProperties:=True, _
                                IgnorePrintAreas:=False, _
                                OpenAfterPublish:=False

回答by Teoanadech

Try to add a "For each sheet in activeworkbook" (it worked for me):

尝试添加“对于活动工作簿中的每个工作表”（它对我有用）：

Sub Extract_pdfs()

Dim wb As Workbook
Dim sh As Worksheet

Set wb = ThisWorkbook

For Each sh In wb.Worksheets

    sh.Select

    pdf_name = sh.Name & ".pdf"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ActiveWorkbook.Path & pdf_name, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

Next

End Sub

回答by morg

If you are trying to print multiple ranges across multiple sheets, into one pdf, you can try using a union function to combine them, I however have not had any luck with using unions so I have done a somewhat longer way to do that. Essentially I create a new page and copy the ranges over to it (in the format I want it) I make sure to delete the page afterwards as it makes running the macro again much easier.

如果您尝试将多张纸上的多个范围打印到一个 pdf 中，您可以尝试使用联合函数来组合它们，但是我在使用联合方面没有任何运气，所以我做了一个更长的方法来做到这一点。基本上我创建一个新页面并将范围复制到它（以我想要的格式）我确保之后删除该页面，因为它使再次运行宏变得更加容易。

t= 1
ThisWorkbook.Sheets.Add.Name = "Print"

set rowcount = range(tocopy).row.count

Range(tocopy).SpecialCells(xlCellTypeVisible).Copy
With Sheets("Print").Cells(t, 1)
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValuesAndNumberFormats
End With
t = t + rowcount

'keep doing for all ranges

Sheets("Print").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Name.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

and then I continue to do that until I have all the ranges on the page and I export the page as a pdf. then delete the sheet "Print" afterwards.

然后我继续这样做，直到我拥有页面上的所有范围并将页面导出为 pdf。然后删除工作表“打印”。

However if you are just looking to print each on a separate page you can try

但是，如果您只是想将每个打印在单独的页面上，您可以尝试

dim printing() as string

printing(1) = "Range1"
printing(2) = "Range2"

for each section in printing
Range(section).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Name.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
next section

As I was mentioning above, you could try to set a union function for it (if you have better luck than I do)

正如我上面提到的，你可以尝试为它设置一个联合函数（如果你的运气比我好）

dim printing as range 

printing = union(range1,range2,range3)

printing.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Name.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

Good luck! hope this helps, also note code may not be exactly right as it is untested!

祝你好运！希望这会有所帮助，还请注意代码可能不完全正确，因为它未经测试！