How to count the page numbers of Pdf files in Excel?
If there are multiple Pdf files in a specific folder, now, you want to display all these file names in a worksheet, and get the page numbers of each file. How could you deal with this job in Excel quickly and easily?
May be the following VBA code can help you to display all Pdf file names and their each page numbers in a worksheet, please do as this:
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:
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...