Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
COURSE of the MONTH
13 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Macro: print each sheet to pdf using ExportAsFixedFormat
Posted on 2016-10-05
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.ExportAsFixedF
fileSaveName _
, Quality:=xlQualityStandard
:=False, OpenAfterPublish:=False
MsgBox "File Saved " & " " & fileSaveName
End Sub
Regards,
Maxime
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.ExportAsFixedF
fileSaveName _
, Quality:=xlQualityStandard
:=False, OpenAfterPublish:=False
MsgBox "File Saved " & " " & fileSaveName
End Sub
Regards,
Maxime
[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
Active 1 day ago
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
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,
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,
Active 1 day ago
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
Active today
@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...
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...
Active 1 day ago
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
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
:=False, OpenAfterPublish:=False
I uploaded the file in case you want to test it.
Regards
test.xls
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
:=False, OpenAfterPublish:=False
I uploaded the file in case you want to test it.
Regards
test.xls
Active 1 day ago
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
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?
Active 1 day ago
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
Active 1 day ago
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
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Suggested Courses
Course of the Month13 days, 5 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.