通过VBA代码将多张工作表导出为.pdf [英] Exporting multiple sheets to .pdf via VBA code

问题描述

我已经看到了这个问题，但是并不能完全回答我的问题-

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

在使用代码创建.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.

我尝试修改链接中的代码，但似乎不适用于多个工作表.

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.

我添加了代码，以选择每张纸的打印范围，然后再选择所有打印纸作为打印纸阵列的一部分，但是该阵列第一张纸的打印范围在所有打印纸上都是重复的.因此，如果工作表1中的范围是B4:P61，工作表2的打印范围是B4:M48，则在选择工作表数组时，工作表2的B4:P61处于选中状态.

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正确，但对其余工作表错误.

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

当我通过选择所有工作表，文件"，导出"来手动执行此操作时，将导出所有工作表的打印范围，因此为什么将其记录并放到例行程序中却被忽略了?

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?

推荐答案

如果您尝试将多个工作表上的多个范围打印到一个pdf中，则可以尝试使用并集函数来组合它们，但是我还没有任何功能使用工会是幸运的，所以我做了更长的时间来做到这一点.本质上，我创建了一个新页面并将其范围复制到该页面上(采用我想要的格式)，因此请确保稍后删除该页面，因为它使再次运行宏变得更加容易.

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.

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

，然后继续执行此操作，直到页面上具有所有范围，并将页面导出为pdf.然后删除工作表打印".

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.

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

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!

这篇关于通过VBA代码将多张工作表导出为.pdf的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！