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:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!