Selecting multiple sheets with SaveAs type PDF

[ABabeNChrist]

Can the SaveAs type PDF select all visible sheets and not just the ActiveSheet

[HansV]

In the Save As dialog, after selecting PDF as file type, ciick Options:

S0741.png

In the Options dialog, select 'Entire workbook':

S0742.png

or select all sheets that you want to save beforehand, and select 'Active sheet(s)':

S0743.png

Click OK, then proceed with saving as usual.

[ABabeNChrist]

Very interesting, can this be accomplished thru VBA or is it a manual thing.

[HansV]

It can be done through VBA. If you have selected multiple sheets (I assume that you know how to do that in VBA), ActiveSheet.ExportAsFixedFormat will save all selected sheets. For example:

Code: Select all

    Dim strFileName As String
    strFileName = "..."
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

[ABabeNChrist]

I like this it saves in a manageable size pdf file even with a large amount of pictures. I’ll do some tweaking with this. Is these feature and code consistent with 2007, 2010 and 2013

[HansV]

It works in Excel 2010, and 2013 is very similar in this respect. I cannot test in 2007.

[ABabeNChrist]

So far it seems identical. I recorded a macro and it was just like the code I received from you and on a trial run it seems to work great. I just need to figure how to place finished pdf on desktop
I know its something similar to this

Code: Select all

    Dim WSHShell As Object
    Dim DesktopPath As String

    Set WSHShell = CreateObject("WScript.Shell")
    DesktopPath = WSHShell.SpecialFolders("Desktop")
    Set WSHShell = Nothing

    ThisWorkbook.SaveAs Filename:=DesktopPath & "\" & _

[HansV]

You can combine it like this:

Code: Select all

    Dim WSHShell As Object
    Dim DesktopPath As String
    Dim strFileName As String

    Set WSHShell = CreateObject("WScript.Shell")
    DesktopPath = WSHShell.SpecialFolders("Desktop")
    Set WSHShell = Nothing

    strFileName = DesktopPath & "\" & "myFile.pdf" ' change as needed
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

[ABabeNChrist]

Thank you Hans; this may be a better approach than trying to compress pictures, I hope so, it seems to have great promise (famous last words).

[HansV]

Keep in mind that the PDF file is merely a 'printout' of the workbook - it doesn't contain any formulas or macros. If that suits your purpose, that is fine of course.

[ABabeNChrist]

In my line of business a PDF file (report) is the only way to go. So far I’m liken this a lot