How to count the number of pages in Pdf files using Excel?

If you have multiple PDF files in a folder and need to list their file names along with the page counts in Excel, you can easily do this with a simple VBA macro. This tutorial will guide you through the steps to quickly extract and display the PDF file names and their corresponding page numbers in an Excel worksheet.

Display PDF file names and page numbers in Excel with VBA

Display PDF file names and page numbers in Excel with VBA

The following VBA code may help you display all PDF file names and their page numbers in a worksheet. Please follow these steps:

1. Open a worksheet where you want to get the Pdf files and page numbers.

2. Hold down the "ALT" + "F11" keys, and it opens the "Microsoft Visual Basic for Applications" window.

3. Click "Insert" > "Module", and paste the following macro in the "Module" Window.

VBA code: List all Pdf file names and page numbers in worksheet:

Sub Test()
    Dim I As Long
    Dim xRg As Range
    Dim xStr As String
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Dim xFileNum As Long
    Dim RegExp As Object
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.pdf", vbDirectory)
        Set xRg = Range("A1")
        Range("A:B").ClearContents
        Range("A1:B1").Font.Bold = True
        xRg = "File Name"
        xRg.Offset(0, 1) = "Pages"
        I = 2
        xStr = ""
        Do While xFileName <> ""
            Cells(I, 1) = xFileName
            Set RegExp = CreateObject("VBscript.RegExp")
            RegExp.Global = True
            RegExp.Pattern = "/Type\s*/Page[^s]"
            xFileNum = FreeFile
            Open (xFdItem & xFileName) For Binary As #xFileNum
                xStr = Space(LOF(xFileNum))
                Get #xFileNum, , xStr
            Close #xFileNum
            Cells(I, 2) = RegExp.Execute(xStr).Count
            I = I + 1
            xFileName = Dir
        Loop
        Columns("A:B").AutoFit
    End If
End Sub

4. After pasting the code, and then press "F5" key to run this code, and a "Browse" window is popped out, please select the folder that contains the Pdf files you want to list and count page numbers, see screenshot:

5. And then, click "OK" button, all Pdf file names and page numbers are listed into the current worksheet, see screenshot: