Note: The other languages of the website are Google-translated. Back to English

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?

Count the page numbers of Pdf files from a folder in worksheet with VBA code


Count the page numbers of Pdf files from a folder in worksheet with VBA code

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:

doc count pdf pages 1

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

doc count pdf pages 2


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (56)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Works great! Many thanks!
This comment was minimized by the moderator on the site
Thank you very much for posting such informative message
This comment was minimized by the moderator on the site
Thanku very much , Excellent code very helpful to me
This comment was minimized by the moderator on the site
Not working properly, for some pdfs, for some pdfs it shows 0 and for some incorrect page numbers
This comment was minimized by the moderator on the site
Hi, Fawaz,
The code works well in my Excel, which Excel version do you use?
Or you can send your detailed problem or pdf files to my Email: skyyang@extendoffice.com.
This comment was minimized by the moderator on the site
Hi skyyang,

I've the same problem as Fawaz. I use MS Office Professional Plus 2013.

Thanks for your help!

Best regards
This comment was minimized by the moderator on the site
Saludos


Hay algún problema con el programa, yo estoy usando la versión 2019 de Office, y las páginas parece que las va contando de mal las primeras 9 páginas acumuladas me sale cero, en la novena página acumulada me sale 10.

¿Por favor me puedes ayudar con ese inconveniente?

De antemano muchas gracias.

Atte.

Pedro
This comment was minimized by the moderator on the site
HOLY! This is awesome! Thank you so much! I'm a printer and have been doing printit.txt and filling in by hand! This is going to make quoting and checking jobs SO MUCH EASIER! Thanks again!!!
This comment was minimized by the moderator on the site
Regards

There is a problem with the program, I am using version 2019 of Office, and the pages seem to be counting badly the first 9 accumulated pages I get zero, in the ninth accumulated page I get 10.

Can you please help me with that inconvenience?

Beforehand thank you very much.

Atte.

Pedro
This comment was minimized by the moderator on the site
The code is good structure for how to do this kind of thing but that regexp will give unreliable results for many pdfs. The regexp being searched for (/Type\s*/Page[^s]), will not work in SECURED pdfs (count will be zero). Also pdfs tools and versions vary in how they mark pages. It could be accurate if you know that all your pdfs are created using the same structure (version and tools).
This comment was minimized by the moderator on the site
Thank you very much for your answer, I solved the problem by saving the files as: "Optimized PDF"
This comment was minimized by the moderator on the site
100% agree with Pedro, I was having the same problem as Rob where some PDF page counts were wrong. But if you make sure that all files are saved as "Optimized PDF" in the folder it will get all the pages correct. This worked for me on over 100 separate PDF files. You can bulk optimize as well with Acrobat Pro. Overall great code, worked right out of the box if you will.
This comment was minimized by the moderator on the site
What if I want to run through subfolders too?
This comment was minimized by the moderator on the site
Hello, Prashant,
To get the number of all the PDF files from folder and subfolders, please apply the below code:

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
Set xRg = Range("A1")
Range("A:B").ClearContents
Range("A1:B1").Font.Bold = True
xRg = "File Name"
xRg.Offset(0, 1) = "Pages"
I = 2
Call SunTest(xFdItem, I)
End If
End Sub

Sub SunTest(xFdItem As Variant, I As Long)
Dim xRg As Range
Dim xStr As String
Dim xFd As FileDialog
Dim xFileName As String
Dim xFileNum As Long
Dim RegExp As Object
Dim xF As Object
Dim xSF As Object
Dim xFso As Object
xFileName = Dir(xFdItem & "*.pdf", vbDirectory)
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
Set xFso = CreateObject("Scripting.FileSystemObject")
Set xF = xFso.GetFolder(xFdItem)
For Each xSF In xF.SubFolders
Call SunTest(xSF.Path & "\", I)
Next
End Sub

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Your subfolder code works fine! thanks
This comment was minimized by the moderator on the site
This is wonderful, thank you. I would like to run through subfolders too. Where/how in the above code do I add these additional commands? what would the whole thing look like?
This comment was minimized by the moderator on the site
Can you help me to also get the creator and dimensions of the file?
This comment was minimized by the moderator on the site
Its really great.But sub folder names are not coming into separate column with PDF file names & Page count.  Can you help in this?
This comment was minimized by the moderator on the site
Fantastic!!!
This comment was minimized by the moderator on the site
Thank you very much.
This comment was minimized by the moderator on the site
Hello,
You are welcome. Glad it helps. Any questions, please feel free to contact us. Have a great day.
Sincerely,
Mandy
This comment was minimized by the moderator on the site
Hi Mandy,
I get a Run-time error '5':  Invalid procedure call or argument
Debug goes to this line: xStr = Space(LOF(xFileNum))
This comment was minimized by the moderator on the site
I'm running but I get an error and debug shows xStr = Space(LOF(xFileNum)) as the issue.
This comment was minimized by the moderator on the site
Thanks you very much.
Likewise, can you count and categorize A3 and A4 pages?
This comment was minimized by the moderator on the site
Here is the code I found somewhere on the net, it's not as optimal as your method:
Option Explicit
Public PDFDoc As AcroPDDoc, PDFPage As Object, A3&, A4&

Sub Main()
Dim fso As FileSystemObject, fld As Folder, fil As File, s$, i&, Arr()
Set fso = New FileSystemObject
Set PDFDoc = New AcroPDDoc
Set fld = fso.GetFolder(ThisWorkbook.Path)
ReDim Arr(1 To 1000, 1 To 3)
For Each fil In fld.Files
s = fil.Name
If Right(s, 4) = ".pdf" Then
CountPagesPDF (ThisWorkbook.Path & "\" & s)
i = i + 1
Arr(i, 1) = s
Arr(i, 2) = A3
Arr(i, 3) = A4
End If
Next
Range("A2:C" & Cells.Rows.Count).Clear
Range("A2:C" & (i + 1)) = Arr
Set PDFPage = Nothing
Set PDFDoc = Nothing
Set fso = Nothing
End Sub

Sub CountPagesPDF(FullFileName$)
Dim i&, n&, x, y
A3 = 0
A4 = 0
PDFDoc.Open (FullFileName)
n = PDFDoc.GetNumPages
For i = 0 To n - 1
Set PDFPage = PDFDoc.AcquirePage(i)
x = PDFPage.GetSize().x
y = PDFPage.GetSize().y
If x + y > 1500 Then A3 = A3 + 1 Else A4 = A4 + 1
Next
PDFDoc.Close
End Sub
This comment was minimized by the moderator on the site
Wov! so many thanks for sharing, this VBA code is a killer!! It works flawlessly with Excel O365
This comment was minimized by the moderator on the site
wow. subfolders works great. can you share how to add "file path" and "file size" too?
This comment was minimized by the moderator on the site
Hello, Daphne,
For solving your problem, please apply the below code, please try, hope it can help you!

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
Set xRg = Range("A1")
Range("A:B").ClearContents
Range("A1:B1").Font.Bold = True
xRg = "File Name"
xRg.Offset(0, 1) = "Pages"
xRg.Offset(0, 2) = "Path"
xRg.Offset(0, 3) = "Size(b)"
I = 2
Call SunTest(xFdItem, I)
End If
End Sub

Sub SunTest(xFdItem As Variant, I As Long)
Dim xRg As Range
Dim xStr As String
Dim xFd As FileDialog
Dim xFileName As String
Dim xFileNum As Long
Dim RegExp As Object
Dim xF As Object
Dim xSF As Object
Dim xFso As Object
xFileName = Dir(xFdItem & "*.pdf", vbDirectory)
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
Cells(I, 3) = xFdItem & xFileName
Cells(I, 4) = FileLen(xFdItem & xFileName)
I = I + 1
xFileName = Dir
Loop
Columns("A:B").AutoFit
Set xFso = CreateObject("Scripting.FileSystemObject")
Set xF = xFso.GetFolder(xFdItem)
For Each xSF In xF.SubFolders
Call SunTest(xSF.Path & "\", I)
Next
End Sub
This comment was minimized by the moderator on the site
This is so great. Thanks!
This comment was minimized by the moderator on the site
oh i see, this is the whole code. I tried to add to the original and was getting an error. Thank you!
This comment was minimized by the moderator on the site
Hello.

Is there a way to also add the page number of the documents and also I get an error and this is the message:
xStr = Space(LOF(xFileNum))


Thank you very much.
This comment was minimized by the moderator on the site
Awesome code! I cant get it to work in subfolders. Can anyone help me pleas?
This comment was minimized by the moderator on the site
Thank you so much
This comment was minimized by the moderator on the site
Hello, works great, thank you for sharing this. One question, is it possible to add that also counts microsoft word .doc and .docx files?
This comment was minimized by the moderator on the site
Hi, sroczeto,
To count the page number of .doc and .docx as well as the PDF files, please apply the following code:
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
Dim xWdApp
Dim xWd
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
If xFd.Show = -1 Then
Application.ScreenUpdating = False
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
xFileName = Dir(xFdItem & "*.docx", vbDirectory)
Set xWdApp = CreateObject("Word.Application")
Do While xFileName <> ""
Cells(I, 1) = xFileName
xFileNum = FreeFile
Set xWd = GetObject(xFdItem & xFileName)
Cells(I, 2) = xWd.ActiveWindow.Panes(1).Pages.Count
xWd.Close False
I = I + 1
xFileName = Dir
Loop
Columns("A:B").AutoFit
End If
Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Thanks mate! It works on pdf and docx, but not on doc files. And one question more, can yo uadd that this will count in subfolders too?
This comment was minimized by the moderator on the site
I have opened a pdf file who's path and name is mention in excel cell column "C9". I just want to get last page number in excel vba please help me
This comment was minimized by the moderator on the site
Hello, this really works well, thank you. Is it possible to get the page size of the first page in a new column? example 8.5 x 11, 11 x 17 etc.
This comment was minimized by the moderator on the site
Hello, this works really well thanks!, is it possible to get the page size for the first page of the PDF document?
This comment was minimized by the moderator on the site
Hello,
Is het possible to also get the dimensions of the pages and the creator of the pdf in this macro?
can someone help me with this?
This comment was minimized by the moderator on the site
is there a way to include .doc I noticed that it works for .docx but not .doc
This comment was minimized by the moderator on the site
Hi, John, To count the pages of .doc and .docx as well as the PDF files, please apply the following code:<div data-tag="code">Sub StatisticsPage()
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
Dim xWdApp
Dim xWd
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
If xFd.Show = -1 Then
Application.ScreenUpdating = False
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
xFileName = Dir(xFdItem & "*.docx", vbDirectory)
Set xWdApp = CreateObject("Word.Application")
Do While xFileName <> ""
Cells(I, 1) = xFileName
xFileNum = FreeFile
Set xWd = GetObject(xFdItem & xFileName)
Cells(I, 2) = xWd.ActiveWindow.Panes(1).Pages.Count
xWd.Close False
I = I + 1
xFileName = Dir
Loop
xFileName = Dir(xFdItem & "*.doc", vbDirectory)
Set xWdApp = CreateObject("Word.Application")
Do While xFileName <> ""
Cells(I, 1) = xFileName
xFileNum = FreeFile
Set xWd = GetObject(xFdItem & xFileName)
Cells(I, 2) = xWd.ActiveWindow.Panes(1).Pages.Count
xWd.Close False
I = I + 1
xFileName = Dir
Loop
Columns("A:B").AutoFit
End If
Application.ScreenUpdating = True
End SubPlease try, hope it can help you!
This comment was minimized by the moderator on the site
Thanks this helps a lots.
This comment was minimized by the moderator on the site
Hi, i have a folder with multiple subfolders  How can i specify the parent folder Path without manually selecting it. Then also the output the name of the child folder. Thanks in advance 
This comment was minimized by the moderator on the site
HI sorted i have edited the code removed XFD and set filpath as xfditem
This comment was minimized by the moderator on the site
Hi Skyyang,First I'd like to thank you for that incredible work you do, and the time you take...I'm searching for a while for a VBA code :I Have an Excelsheet with in column "J" a list of pdf, xlsx and elm files located in a data room directory (with subdirectory's)File name are complete with type X:\Data_Room\Sub_directory_1\file.pdfThe code should fill the column "I" with the number of pages of each .pdf and .xls files (no need for other, cels should stay blank)Could you please help me?
This comment was minimized by the moderator on the site
Any chance this could be expanded to pull a Bates number from the first page of each pdf?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0  Characters
Suggested Locations