Solved

Macro: print each sheet to pdf using ExportAsFixedFormat

Posted on 2016-10-05
11
70 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
0
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
  • 6
  • 4
11 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41830715
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

0
 

Author Comment

by:mafle
ID: 41830750
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,
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41830764
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.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 8

Assisted Solution

by:Koen
Koen earned 100 total points
ID: 41831414
@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...
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41831419
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.
0
 

Author Comment

by:mafle
ID: 41831646
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
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41831685
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

0
 

Author Comment

by:mafle
ID: 41831705
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?
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 400 total points
ID: 41831720
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

0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41831728
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

1
 

Author Closing Comment

by:mafle
ID: 41831743
Good job!
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question