From our last post we learnt how to convert an entire workbook to a single PDF manually. If you want to know how, then please check this post.
Convert an Entire Workbook to a Single PDF File
Today I’m going to teach you how to do the same thing using VBA. So you can use this subroutine inside your VBA applications where necessary. Now let’s start developing the code. First we need to declare a few variables.
We need to loop through all the sheets of the workbook. So we need to declare one variable as worksheet.
| Dim WS As Worksheet |
Then we need an array to assign sheet names.
| Dim SheetNames() As Variant |
PDF file name will be assigned to a string type variable.
| Dim PDF_FileName As String |
In addition to those variables, let’s declare two more variables of type integer. One is to hold the number of sheets. And other variable is to use as a counter inside for next loop.
|
Dim NumberOfSheets As Integer Dim Counter As Integer |
Now we have declared all the required variables. After variable declaration we can calculate the number of sheets inside the workbook as our first step.
| NumberOfSheets = ThisWorkbook.Worksheets.Count |
Now we know the upper bound of our SheetNames array. So we can size that dynamic array using redim statement.
| ReDim SheetNames(1 To NumberOfSheets) |
As our next step we can loop through all the sheets of the workbook and assign name of each worksheet to the SheetNames array. We can do it as follows.
|
Counter = 1 For Each WS In Worksheets SheetNames(Counter) = WS.Name Counter = Counter + 1 Next WS |
Now let’s assign a name to our PDF file. You can give any valid name to the PDF file.
| PDF_FileName = "PDf file name here" |
Next we use SheetNames array to select all the sheets.
| Sheets(SheetNames).Select |
Then we can convert all the sheets to one single PDF file as follows.
|
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "D:\Work\Create PDF\" & PDF_FileName & ".pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False |
You should replace "D:\Work\Create PDF\" with path of your folder where you need to save the PDF file. Or else you can assign the folder path to a variable and then use that inside the code like this.
|
Dim FolderPath as string FolderPath = "D:\Work\Create PDF" ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ FolderPath & "\" & PDF_FileName & ".pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False |
Here is the complete subroutine.
|
Sub ConvertWorkbookToSinglePDF() Dim WS As Worksheet Dim SheetNames() As Variant Dim PDF_FileName As String Dim NumberOfSheets As Integer Dim Counter As Integer NumberOfSheets = ThisWorkbook.Worksheets.Count ReDim SheetNames(1 To NumberOfSheets) Counter = 1 For Each WS In Worksheets SheetNames(Counter) = WS.Name Counter = Counter + 1 Next WS PDF_FileName = "PDf file name here" Sheets(SheetNames).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "D:\Work\New Post 2\" & PDF_FileName & ".pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False End Sub |
