• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

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!
  • 2
2 Solutions

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

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now