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.