I searched for, and tried, numerous ways to export multiple worksheets to a single PDF file with mixed results. I finally learned that Excel would export several worksheets at a time by using the worksheet’s visible property.
This post explains how to implement the Export to PDF routine which is used in the Total Cost Model. The code will take any number of worksheets from an Excel workbook and export them to a single PDF document. Exporting to separate documents, one for each worksheet is fairly easy by using a looping structure to export all of the selected items individually. In this case the selected worksheets, all from the same open workbook, are obtained from a VBA form (shown in the following image.) The form is opened from the custom ribbon toolbar of the Total Cost Model. The routine starts there by selecting the worksheets to export and clicking the “Export” button.
Three things which should be remembered when working with code that hides worksheets and uses Excel’s ExportAsFixedFormat method:
- At least one worksheet MUST always be visible in an Excel workbook.
- The ExportAsFixedFormat method, as used in the code, will export all of the visible worksheets.
- The ExportAsFixedFormat method exports only the Print Area of the worksheet if IgnorePrintAreas=False
The worksheet selections can be made on the form independently of each other or by checking Select All to mark them all. The Test PDF app button runs a test to see if the computer will open a PDF document. Another post shows that code, which is quite similar in its approach.
The Export button click event procedure follows and proceeds according to this pseudocode :
- The selected item found flag, bolFound, is set to False. A Control object is set to the variable name ctrl.
- A For Each loop moves through all of the controls on the form, frmToPDF by reading the ctrl object members.
- An embedded If statement checks for the control TypeName,”CheckBox”.
- If it finds a checked box, excluding the chkSelectAll checkbox, it sets the flag, bolFound, to True.
- If bolFound is False a message that there was NO selection is displayed,
- Otherwise (else) it calls the ExportToPDF sub procedure…
Private Sub cmdExport_Click() ' find out if there are selected items, and if so go to the export routine Dim bolFound As Boolean Dim ctrl As control On Error Resume Next bolFound = False ' was a checked box found? For Each ctrl In frmToPDF.Controls If TypeName(ctrl) = "CheckBox" Then If ctrl.Name <> "chkSelectAll" Then If ctrl.Value = True Then bolFound = True End If End If Next If bolFound = False Then ' if there is NOT anything selected ' if there is NOT anything selected Call MsgBox("There is nothing selected to export!", vbOKOnly, "Nothing selected...") Else Call ExportToPDF End If End Sub
The following procedure, ExportToPDF, essentially hides the worksheets which are not to be exported to PDF and then uses Excel’s ExportAsFixedFormat method to do the work. The code executes from the first snippet (cmdExport_Click). It is initiated from the line 22, “Call ExportToPDF“. ExportToPDF includes a bit more code than may be necessary. This is because the Total Cost Model workbook has 3 worksheets which may not all be in use (visible) and need to be checked for visibility. At least one System is required and always visible, the other two may be visible or not. The form only enables checkboxes for visible System worksheets, so the routine needs only to return to visibility the ones which were previously visible.
The ExportToPDF sub routine proceeds as explained in this pseudocode:
- Initiate a variable for the System sheet numbers and a loop counter.
- Set the number of visible System worksheets (sysNum) using GetSystemNum(). This a custom function that returns the number of active System worksheets so the routine knows what worksheets to unhide at the end of the export.
- Change the Application properties for DisplayAlerts, EnableEvents and ScreenUpdating to False.
- Set the worksheets visibility according to the form’s selection(s) using a For loop.
- Hide the Concepts and Instructions worksheet (It is never set to print or export.)
- Export the visible worksheets using “ThisWorkbook.ExportAsFixedFormat“.
- The “IgnorePrintAreas:=False” parameter tells the export method to export each worksheet’s Print Area only, if one is set.
- Return the worksheets to their correct visibility, for the Systems worksheets use the var from the first step.
- Unhide the Concepts and Instructions worksheet.
- Return the Application properties for DisplayAlerts, EnableEvents and ScreenUpdating to True.
- The error handling routine, errHandler, will trigger if there is a legitimate error (>0), display a message and resume executing the code at the next statement after the error.
Private Sub ExportToPDF() ' first set the selected sheets to visible and the unselected ones to hidden ' then export to PDF, ' then reset the sheets to their proper visibility Dim sysNum, i As Integer On Error GoTo errHandler sysNum = GetSystemNum() 'get the number of active systems ' The sheets associated with the form checkboxes ' Common Data - chkItems1 ' System 1 - chkItems2 ' System 2 - chkItems3 ' System 3 - chkItems4 ' Cost Analysis - chkItems5 ' Graphical Analysis - chkItems6 With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With ' First set the sheets visibility per the form's selection(s) For i = 1 To 6 Worksheets(i).Visible = Me.Controls("ChkItems" & i).Value Next ' hide the Concepts and Instructions sheet Sheets(7).Visible = False ' Second, export the visible sheets ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _ "Total Cost Model - exported.pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=True ' Return the sheets to their correct visibility, for the systems use the var from the first step For i = 1 To 6 With Worksheets(i) If i = 3 Or i = 4 Then Select Case sysNum Case 1 'do nothing... Case 2 Sheets(3).Visible = True Case 3 Sheets(3).Visible = True Sheets(4).Visible = True End Select Else .Visible = True End If End With Next ' make the Concepts and Instructions sheet visible again Sheets(7).Visible = True With Application .ScreenUpdating = True .EnableEvents = True .DisplayAlerts = True End With errHandler: If Err.Number > 0 Then MsgBox "Error number: " & Err.Number & " Description: " & Err.Description Resume Next End If End Sub
This procedure can be seen in action and the code examined in the workbook, Total Cost Model. You can use this Excel workbook and view all of the code used in it free of charge.