36

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, ...
pnuts
  • 58,317
  • 11
  • 87
  • 139
guthrie
  • 4,529
  • 4
  • 26
  • 31

3 Answers3

92

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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • @Tim `+1` this a useful piece :) – bonCodigo Jan 19 '13 at 21:03
  • The ability to select an array of sheets worked out wonderfully. Thank you. – CBC_NS Jan 22 '14 at 14:54
  • 4
    If you hide workbook with `book.Windows(1).Visible = False` `Sheets(..).Select()` fails. (python3.4, mso2013) – Winand Oct 14 '15 at 14:37
  • Is it possible to do this across multiple workbooks? – Austin Wismer Aug 05 '16 at 01:22
  • Save sheets from different workbooks into the same PDF? I don't think so. – Tim Williams Aug 05 '16 at 02:53
  • 9
    If you're worried about users renaming a sheet, you can use the Name method of the sheet instead, like `Thisworkbook.Sheets(Array(Sheet1.Name, Sheet2.Name)).Select` so that no matter what a user renames a sheet to the macro will still function. To break that, you have to edit the sheet properties in VBA. – Pork Aug 22 '16 at 17:19
  • @AustinWismer only workaround I know of is to use [the `Worksheet.Copy` method](https://msdn.microsoft.com/en-us/library/office/ff837784.aspx) to put them all in a temporary workbook. The upside is then you don't have to use `ThisWorkbook.Sheets(Array()).Select` – AnotherParker Dec 01 '16 at 16:45
  • 1
    I'm finding that the function just tries (and fails) to export the whole workbook (guessing from the file size of the temporary file) irrespective of the sheet selection. New version of Excel/ VBA perhaps? – CJB Sep 03 '19 at 12:02
  • Well what version are you using? – Tim Williams Sep 04 '19 at 00:09
  • Question: Does this require a `namespace`, or just allows you to do it without using the proper `namespace`?(I can't find a `namespace` for this) – Momoro Dec 14 '19 at 03:14
  • I know this isn't in C#, but this doesn't really make sense to me :D(But that's just me...) – Momoro Dec 14 '19 at 03:15
  • 1
    IMPORTANT: Be sure to use ActiveSheet.ExportAsFixedFormat ... NOT ThisWorkbook.ExportAsFixedFormat ... or all sheets will be exported, not jsut the selected sheets. It's a bit counterintuitive (or was for me). – John Joseph Apr 28 '21 at 20:31
10

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
Peter Albert
  • 16,917
  • 5
  • 64
  • 88
  • Will this work with Excel 2003, too? I mean, will IsPDFLibraryInstalled return false? – Anthony Sep 05 '13 at 21:57
  • The code here for `IsPDFLibraryInstalled()` has a bug in it. There's supposed to be an <> sign before the final `"")` i.e. the line before `End Function` should be `"\EXP_PDF.DLL") <> "")` – AnotherParker Oct 04 '16 at 04:42
  • 1
    I attempted to edit the code to fix the <> bug and also the missing "If" in "End If" but my edit was rejected. – AnotherParker Oct 04 '16 at 04:58
  • @AnotherParker thanks for pointing it out. It actually was there in the source but got lost in the conversion to HTML. Fixed now- thanks – Peter Albert Oct 04 '16 at 05:28
6

I recommend adding the following line after the export to PDF:

ThisWorkbook.Sheets("Sheet1").Select

(where eg. Sheet1 is the single sheet you want to be active afterwards)

Leaving multiple sheets in a selected state may cause problems executing some code. (eg. unprotect doesn't function properly when multiple sheets are actively selected.)

AnotherParker
  • 789
  • 5
  • 16
Armand
  • 69
  • 1
  • 1