This post describes the Test PDF app feature of the Export to PDF form from the Total Cost Model. It provides a simple way to check for valid export targets for Excel workbooks and may be adapted to work with other file types. You can download the Total Cost Model workbook and see it in action and inspect the code yourself. The workbook is free and without any password protection.
The exporting code is explained in another post, so we will just concentrate on this simple code to create and open a worksheet as an exported PDF document. The purpose of this feature is to ensure that there is the capability to export and open a PDF document on your PC.
Clicking the Test PDF app button triggers the cmdTest click event and runs the following code:
Private Sub cmdTest_Click()
' check for the availability of a PDF reader
On Error Resume Next
If MsgBox("This procedure will try to open a sample document in the default PDF reader on your system. Do you want to continue?", _
vbYesNo, "Test for a PDF reader...") = vbYes Then
Call Check_Reader
End If
End Sub
This simple code is just a gateway to ask if you want to continue with the process. The command Call Check_Reader does the work. It is listed below…
Remember… all these samples scroll to the right to reveal the complete code→
The Check_Reader code is explained in the following pseudocode:
- Initiate a variable for the System sheet number, a loop counter and a worksheet object
- Change the Application properties for DisplayAlerts, EnableEvents and ScreenUpdating to False. The workbook Calculation property is set to manual and the Cursor is set to the wait style.
- Get the number of visible System worksheets 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 test.
- Create the PDF test worksheet (“Test_for_PDF”). This is done first because there must always be one open worksheet in an Excel workbook. Trying to hide all of the worksheets will throw an error!
- Next the other worksheets visibility are set to False using a simple For loop.
- Export the only visible worksheet (“Test_for_PDF”) using Excel’s ExportAsFixedFormat method.
- Return the worksheets to their correct visibility, for the systems worksheets the var from the first step (GetSystemNum()) is used.
- Delete the PDF test worksheet (“Test_for_PDF”).
- Return the Application properties for DisplayAlerts, EnableEvents and ScreenUpdating to True. Reset the Calculation property to automatic and the Cursor to the Excel default.
- 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 Check_Reader()
' Check to see if a PDF reader is available...create a document and open it...at least try to...
Dim sysNum, i As Integer
Dim ws As Worksheet
On Error GoTo errHandler
With Application
.DisplayAlerts = False
.EnableEvents = False ' turn OFF event tracking
.Calculation = xlCalculationManual ' turn OFF the auto calculations, for speed...
.ScreenUpdating = False
.Cursor = xlWait
End With
sysNum = GetSystemNum() 'get the number of active systems
' First CREATE the PDF test sheet and THEN set the other sheets visibility to false
With ThisWorkbook
Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
ws.Name = "Test_for_PDF"
End With
With ws
With .Range("A3")
.Value = "Success!"
.Font.Size = 16
.Font.Color = vbRed
End With
With .Range("A5")
.Value = "If you are reading this in a PDF reader you are set to export your documents…"
.Font.Size = 12
.Font.Color = vbBlack
End With
With .Range("A6")
.Value = "There is nothing else here, you can close this reader app and go back to work."
.Font.Size = 12
.Font.Color = vbBlack
End With
End With
For i = 1 To 7
Worksheets(i).Visible = False
Next
' Second, export the visible sheet
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 7
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
Worksheets(i).Visible = True
End If
Next
' DELETE the PDF test sheet
With Worksheets(Sheets.Count)
If .Name = "Test_for_PDF" Then
Sheets(Sheets.Count).Delete
End If
End With
Set ws = Nothing
With Application
.Calculation = xlCalculationAutomatic ' turn ON the auto calculations
.DisplayAlerts = True
.EnableEvents = True ' turn ON event tracking
.ScreenUpdating = True
.Cursor = xlDefault
End With
errHandler: ' Error-handling routine
If Err.Number >; 0 Then
MsgBox ("Error: " & Err.Description & ". Error number: " & Err.Number)
Resume Next
End If
End Sub
So simple…and maybe not necessary considering most every modern Windows PC has an application which will open and display a PDF file. This has been adapted and used for my exporting to Microsoft Word© tools in other workbooks.