Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Save multiple sheets to .pdf

I have a reporting spreadsheet that grabs some data from a database and forms three sheets of summary report information. I want to run the spreadsheet automatically from the command line, and have it automatically save all three reporting sheets as a PDF file(s).

At first I thought I could have a VBA macro on the sheet do this by a series of "print as PDF", but that requires an intermediary interactive dialog box to specify the output file name. Then I find that I can just save as pdf, and the macro can set the output file name. However this creates three separate files, and I have to then later put them together externally to merge them.

(It is odd that save as pdf only saves one sheet, where other save modes seem to save the entire workbook.)

Yes, there are tools for merging the three files later, but I want to know if there is some easy way to get Excel to save multiple sheets together as one pdf file.

I print now by a series of things like:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ... 

Could I do instead a single statement something like (pseudo-code):

ThisWorkbook.Sheets(1,2,3,4).ExportAsFixedFormat Type:=xlTypePDF, ... 
like image 623
guthrie Avatar asked Jan 18 '13 17:01

guthrie


People also ask

Can I create a PDF with multiple tabs?

1. Hold the Command key and select the sheets that I want to save as PDF format. 2. Click File>Save as>Choose the location and file format>Click Sheet on the bottom of the page>Save to print multiple tabs.

How do I save every sheet of a workbook as a PDF?

Right click one sheet tab. And then choose the option “Select All Sheets” in the menu. Thus, you have created a sheet group. Repeat the step 2-7 to save the workbook as a PDF file.


2 Answers

Start by selecting the sheets you want to combine:

ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Select  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _     "C:\tempo.pdf", Quality:= xlQualityStandard, IncludeDocProperties:=True, _      IgnorePrintAreas:=False, OpenAfterPublish:=True 
like image 189
Tim Williams Avatar answered Oct 07 '22 10:10

Tim Williams


Similar to Tim's answer - but with a check for 2007 (where the PDF export is not installed by default):

 Public Sub subCreatePDF()      If Not IsPDFLibraryInstalled Then         'Better show this as a userform with a proper link:         MsgBox "Please install the Addin to export to PDF. You can find it at http://www.microsoft.com/downloads/details.aspx?familyid=4d951911-3e7e-4ae6-b059-a2e79ed87041".          Exit Sub     End If      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _         Filename:=ActiveWorkbook.Path & Application.PathSeparator & _         ActiveSheet.Name & " für " & Range("SelectedName").Value & ".pdf", _         Quality:=xlQualityStandard, IncludeDocProperties:=True, _         IgnorePrintAreas:=False, OpenAfterPublish:=True End Sub  Private Function IsPDFLibraryInstalled() As Boolean 'Credits go to Ron DeBruin (http://www.rondebruin.nl/pdf.htm)     IsPDFLibraryInstalled = _         (Dir(Environ("commonprogramfiles") & _         "\Microsoft Shared\OFFICE" & _         Format(Val(Application.Version), "00") & _         "\EXP_PDF.DLL") <> "") End Function 
like image 21
Peter Albert Avatar answered Oct 07 '22 11:10

Peter Albert