VBA in Excel to count number of pages in PDFs in a folder

in a folder, there are several PDF files. I need to calculate number of pages in each files and write the file name and number of pages in front of the file name in Excel. I would like to see a good VBA coding to do this. Thanks!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


pls try
'with Adobe Acrobat 9 Professional installed
'with Tools -> References -> Acrobat checkbox selected
Sub ListPDFFiles()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    Dim AcroDoc As Object
    Set AcroDoc = New AcroPDDoc
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = ActiveSheet
    Dim folderpath
    folderpath = InputBox("Enter Folder Path", "Folder Path")
     'Get the folder object associated with the directory
    Set objFolder = objFSO.GetFolder(folderpath)
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"
     'Loop through the Files collection
    For Each objFile In objFolder.Files
        If UCase$(Right$(objFile.Name, 4)) = ".PDF" Then
            ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
            AcroDoc.Open (objFolder.Name & "\" & objFile.Name)
            PageNum = AcroDoc.GetNumPages
            ws.Cells(ws.UsedRange.Rows.Count + 1, 2).Value = PageNum
        End If
     'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
    Set AcroDoc = Nothing
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lapatiyaAuthor Commented:
would this work without acrobat pro? what if I have only adobe reader ?
Joe Winograd, Fellow&MVEDeveloperCommented:
I wrote a program called CountPagesPDF that does this, but it is not in VBA. It is a stand-alone program, written in the AutoHotkey language and distributed as a standard Windows installer (a Setup.exe file). This EE thread discusses it:

The Quick Start Guide posted at that question is for Version 1.2, dated 4-Nov-2017. The current release is Version 1.7, dated 31-Jan-2018. I've attached the latest QSG to this post. The report in the QSG (shown below) is from a live run on a user's production computer:

CountPagesPDF(tm) Report 2018-01-31_11.25.50
Version 1.7 Build 20180131.1052
Run type: Graphical User Interface (GUI)
Source folder: D:\PDF files\
What to process: All files in a source folder and its subfolders
Report format: TXT
Report type: Summary and details
Beginning date and time of processing: 2018-01-31_11.27.14
Number of folders processed: 6
Number of PDF files processed: 154
Number of non-PDF files skipped: 173
Number of pages in all PDF files: 7,847
Ending date and time: 2018-01-31_11.27.21
Elapsed processing time (minutes:seconds): 0:07

If you select the report type of Summary and details, entries such as these are made during processing:

Page count for D:\PDF files\2016 Final.pdf: 219

That is, the report has a line for each PDF file with its full file name, including path, and number of pages. Also, I see in your question that you want "the file name and number of pages in front of the file name in Excel". CountPagesPDF does this if you select the report format of CSV. In that case, the fully qualified file name is in column A and the number of pages in column B.

Adobe Acrobat does not have to be installed to run CountPagesPDF. In fact, no Adobe software is required, as CountPagesPDF is a stand-alone, self-contained program. As noted at the other thread, I'm not ready yet for distribution to the entire Internet, but if CountPagesPDF interests you, please PM me. Regards, Joe
Joe Winograd, Fellow&MVEDeveloperCommented:
One solution is VBA code, although it requires Acrobat, because of the calls to Acrobat's Component Object Model (COM) routines. The other solution does not require Acrobat, but it is not VBA code. So neither solution is perfect, but without feedback from the asker, I think that both are acceptable, and will be good to have in the PAQ. There is no Best Answer, so I'm marking the first one as the Accepted Solution and the second one as the Assisted solution, and splitting the points evenly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.