Test PDF Document Exporting with Excel VBA

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.