Solved

Macro: print each sheet to pdf using ExportAsFixedFormat

Posted on 2016-10-05

2 solutions

84 Views

Last Modified: 2016-10-06

Hello,

I got this macro which save the active sheet of a workbook as pdf. I want to add the following function : do it for every sheets of the workbook and at the beginning of the macro i want to add a value with a dialog box which will be added at the beginning of the filename. Exemple : sheet name : 036, 037. Filename : Dialogboxinput-036, Dialogboxinput-037.

Sub PrintSheetsToPDF2()
pdfName = ActiveSheet.Name
ChDir ActiveWorkbook.Path & "\"
fileSaveName = ActiveSheet.Name

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

MsgBox "File Saved " & " " & fileSaveName
End Sub

Regards,
Maxime

Comment

Question by:mafle

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

11 Comments

LVL 27

Expert Comment

by:MacroShadow

ID: 418307152016-10-05

Please try this:

Sub PrintSheetsToPDF2()
    Dim sht As Worksheet
    Dim fileSaveName As String, strDialogPrefix As String

    For Each sht In ActiveWorkbook.Sheets
        strDialogPrefix = InputBox("Enter prefix for sheet: " & sht.Name, "Export")
        fileSaveName = strDialogPrefix & "-" & sht.Name
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                        fileSaveName _
                                        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                                                                                                  :=False, OpenAfterPublish:=False
        MsgBox "File Saved " & " " & fileSaveName
    Next
End Sub

Open in new window

Author Comment

by:mafle

ID: 418307502016-10-05

Hi MacroShadow,

there is 2 problems, first I want the same dialog prefix for every sheets (only 1 input). Also, the macro always save the first sheet for every sheets. The names are correct.
Regards,

LVL 27

Expert Comment

by:MacroShadow

ID: 418307642016-10-05

For your first issue use this:

Sub PrintSheetsToPDF2()
    Dim sht As Worksheet
    Dim fileSaveName As String, strDialogPrefix As String

    strDialogPrefix = InputBox("Enter prefix", "Export")

    For Each sht In ActiveWorkbook.Sheets
        fileSaveName = strDialogPrefix & "-" & sht.Name
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                        fileSaveName _
                                        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                                                                                                  :=False, OpenAfterPublish:=False
        MsgBox "File Saved " & " " & fileSaveName
    Next
End Sub

Open in new window

I didn't understand the second problem.

LVL 8

Assisted Solution

by:Koen

Koen earned 100 total points

ID: 418314142016-10-06

@macroshadow:

1. he wants to enter a number at the beginning, then the loop must auto increment (if I understood correctly)

2. you save the activesheet... that is always the same sheet...since you have no select statement. so either you select each sheet and save it or you dont use activesheet, but name the sheet...

LVL 27

Expert Comment

by:MacroShadow

ID: 418314192016-10-06

Here you go:

Sub PrintSheetsToPDF2()
    Dim sht As Worksheet
    Dim fileSaveName As String, strDialogPrefix As String

    strDialogPrefix = InputBox("Enter prefix", "Export")

    For Each sht In ActiveWorkbook.Sheets
        fileSaveName = strDialogPrefix & "-" & sht.Name
        sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                        fileSaveName _
                                        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                                                                                                  :=False, OpenAfterPublish:=False
        MsgBox "File Saved " & " " & fileSaveName
    Next
End Sub

Open in new window

Thanks Koen, missed that one.

Author Comment

by:mafle

ID: 418316462016-10-06

Thanks both of you.
I tested the macro on a new workbook and it worked great. But in my working workbook, the macro stop with error code 5 saying there is a bad argument. I tested several time with the same result.

The problematic code :
sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
fileSaveName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

I uploaded the file in case you want to test it.
Regards
test.xls

LVL 27

Expert Comment

by:MacroShadow

ID: 418316852016-10-06

It's because you have a hidden sheet. To skip hidden sheets use this:

Sub PrintSheetsToPDFFinal()
    Dim sht As Worksheet
    Dim fileSaveName As String, strDialogPrefix As String

    strDialogPrefix = InputBox("Enter prefix", "Export")

    For Each sht In ActiveWorkbook.Sheets
        If sht.Visible = True Then
            fileSaveName = strDialogPrefix & "-" & sht.Name
            sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                    fileSaveName _
                                    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                                                                                              :=False, OpenAfterPublish:=False
            MsgBox "File Saved " & " " & fileSaveName
        End If
    Next
End Sub

Open in new window

Author Comment

by:mafle

ID: 418317052016-10-06

Good. Thanks. Missed that one. Is there a way to browse the directory to use to save the pdf? Or save the pdf where the .xls file is saved?

LVL 27

Accepted Solution

by:MacroShadow

MacroShadow earned 400 total points

ID: 418317202016-10-06

To save in the same folder as the excel file:

Sub PrintSheetsToPDFFinal()
    Dim sht As Worksheet
    Dim fileSaveName As String, strDialogPrefix As String

    strDialogPrefix = InputBox("Enter prefix", "Export")

    For Each sht In ActiveWorkbook.Sheets
        If sht.Visible = True Then
            fileSaveName = ActiveWorkbook.Path & "\" & strDialogPrefix & "-" & sht.Name
            sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                    fileSaveName _
                                    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                                                                                              :=False, OpenAfterPublish:=False
            MsgBox "File Saved " & " " & fileSaveName
        End If
    Next
End Sub

Open in new window

LVL 27

Expert Comment

by:MacroShadow

ID: 418317282016-10-06

To browse for a folder:

Sub PrintSheetsToPDFFinal()
    Dim sht As Worksheet
    Dim fileSaveName As String, strDialogPrefix As String

    strDialogPrefix = InputBox("Enter prefix", "Export")

    For Each sht In ActiveWorkbook.Sheets
        If sht.Visible = True Then
            fileSaveName = BrowseForFolder & "\" & strDialogPrefix & "-" & sht.Name
            sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                    fileSaveName _
                                    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                                                                                              :=False, OpenAfterPublish:=False
            MsgBox "File Saved " & " " & fileSaveName
        End If
    Next
End Sub


Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose:  To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE:  If invalid, it will open at the Desktop level
'Source: http://www.vbaexpress.com/kb/getarticle.php?kb_id=284

    Dim ShellApp As Object

    'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
                   BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

    'Set the folder to that selected.  (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0

    'Destroy the Shell Application
    Set ShellApp = Nothing

    'Check for invalid or non-entries and send to the Invalid error
    'handler if found
    'Valid selections can begin L: (where L is a letter) or
    '\\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1)
        Case Is = ":"
            If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
        Case Is = "\"
            If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
        Case Else
            GoTo Invalid
    End Select

    Exit Function

Invalid:
    'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False

End Function

Open in new window

Author Closing Comment

by:mafle

ID: 418317432016-10-06

Good job!

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Guide to Writing Understandable and Maintainable VBA Code

Article by: Martin

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.

Freeze Panes feature

Article by: Rob

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.

Microsoft Excel

Building Probability Models in Excel Part 5: Modeling an Investment Using Discrete Random Variables

Video by: Toby

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

Creating an Interactive Chart for Marketers in Excel

Video by: AnnieCushing

This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.