Get the number of pages and file names of pdf files using third party application and write it to excel

[prati]

Hey
I am new member in this forum, i will do my best efforts to be specifc and clear and respect the time of the members.I am a begginer in VBA and need your help.
in the last month i search for a solution of a VBA code that can retrieve information about pdf files and write that information into excel.
I need a vba code that can count pdf pages and write to excell for each pdf the file name and number of pages.

i have tried several vba codes-

How to count the page numbers of Pdf files in Excel?

www.extendoffice.com

VBA Page Count

Hello Everyone, I have nearly 1500 Pdf files, Each have its own name. So, I have to Open the pdf and List the page numbers in Excel. So, I need a macro code to open the pdf and count the page numbers and post it into excel. Can i Get Any Help from you Guys, Krish.

www.mrexcel.com

None of the codes is perfect. of course sometimes the vba count the pages correctly, but there are alwayes mistakes with files.
Sometimes counting more pages than the real, sometimess less.

After couple of weeks i realized that the best to count pdf pages is a VBA that use third party application- for example the VBA code bellow using adobe acrobat proffesional-

Get page count of pdf files [Archive] - VBA Express Forum

Hi VB'ers :), Is it possible to get the page count of pdf files through vb code? Like the pdfs are saved in one folder and it will list their filenames and page count of each in a simple excel sheet? Please help I really need this one. Any help would be very much appreciated. Thanks a...

www.vbaexpress.com

It works perfect, counting the correct number of pages. No mistakes at all - but it is not free.....you need acrobat for that (not reader)

I need a free solution and not a VBA that use adobe acrobat.

I can think about 3 free main options but have no idea how to write code for them.

1 option to write a VBA thay willl use PDFtk, that will retrieve the information without opening the graphical interface

Second option is to write a vba that will use Pdfinfo

Third and last option is to write a vba code that use pdfsam also without really opening the interface- just insall pdfsam and write a vba that will use it for counting pages

Any other free solution will be perfect as well as long as the vba code can look at c:\tempand then write to excel cells 2 types of information:

1. The file name
2. The number of pages

In another words, a need a VBA that can do the job without "really" opening the the third party interface, and yet get the correct number of pdf pages.
you can read here why counting pdf pages without third party application is very tricky

https://www.reddit.com/r/visualbasic/comments/29n2xa

Thanks in advance for your help

 

[John_w]

1 option to write a VBA thay willl use PDFtk, that will retrieve the information without opening the graphical interface

You could use PDFtk Server which has a command-line interface, which can be run as a DOS command from VBA using the Shell function or Windows Script Host.

 

[prati]

Thanks for your reply. I need a VBA solution. It doesn't have to be with PDFtk...or

Any suggestion for getting the correct numbers of pdf pages would be great, as long as the solution is VBA code, even if the VBA doesn't use third party application for counting.

 

[John_w]

As I said, it's VBA.

 

[prati]

As I said, it's VBA.

Thank you for trying to help me.
Because i am a total begginer i should either hire someone and pay him to write that code, or maybe try another forum and hope that somebody already knows a working code for counting pdf pages.
I thought about learning VBA but investing several months or a year just to learn about counting pdf pages doesn't seem to be efficient.

It is very strange that nobody had written a correct code for counting pdf pages. i already spent a month of searching, and that should be enough. Now the time to give up searching more and more for correct code and search for someone to hire and pay him

 

[John_w]

This macro runs the PDFtk Server dump_data command to count the number of pages in the PDF files (*.pdf) in the specified folder and outputs the file names and page counts to the active sheet. Obviously you must install PDFtk Server.

Public Sub Count_PDF_Pages()

    Dim Wshell As Object 'WshShell
    Dim Wexec As Object 'WshExec
    Dim output As Variant
    Dim PDFfileSpec As String, PDFfolder As String, PDFfileName As String
    Dim pageCount As String
    Dim r As Long
    
    PDFfileSpec = "C:\folder\path\*.pdf"     'CHANGE FOLDER PATH
    
    Set Wshell = CreateObject("WScript.Shell")  'New WshShell
    
    PDFfolder = Left(PDFfileSpec, InStrRev(PDFfileSpec, "\"))
    With ActiveSheet
        .Cells.ClearContents
        .Range("A1:B1").Value = Array("File Name", "Page Count")
        r = 2
        PDFfileName = Dir(PDFfileSpec)
        While PDFfileName <> vbNullString
            Set Wexec = Wshell.Exec("cmd /c PDFtk " & Q & PDFfolder & PDFfileName & Q & " dump_data | FIND ""NumberOfPages""")
            If Not Wexec.StdOut.AtEndOfStream Then
                pageCount = Split(Split(Wexec.StdOut.ReadAll, "NumberOfPages: ")(1), vbCrLf)(0)
                .Cells(r, 1).Resize(, 2).Value = Array(PDFfileName, pageCount)
            Else
                .Cells(r, 1).Resize(, 2).Value = Array(PDFfileName, Replace(Wexec.StdErr.ReadAll, vbCrLf, " "))
            End If
            r = r + 1
            PDFfileName = Dir
            DoEvents
        Wend
    End With
    
End Sub

 

[prati]

Thanks for your kindness. Half on the way to be happy.

I just installed PDFtk server together with PDFtk toolkit that was already installed a few weeks ago. so i just added the PDFtk server by regular installation (like any program in my computer)

Before explaining the problem i would also like to mention that i did not add any Reference aftet insalling PDFtk server - if you think i miss a reference please let me know.

Right after installing PDFtk server i made a copy and paste of the macro you wrote above.

The macro did a great work output the files name pefectly, however it does not output the number of pages.
This is the error i got into excel cell B1

'Error: Unable to find file. Error: Failed to open PDF file: C:\temp\Contract Error: Unable to find file. Error: Failed to open PDF file: Schiffer.pdf Done. Input errors, so no output created.
'Error: Unable to find file. Error: Failed to open PDF file: C:\temp\Contract Error: Unable to find file. Error: Failed to open PDF file: Shmidt.pdf Done. Input errors, so no output created.
'Error: Unable to find file. Error: Failed to open PDF file: C:\temp\Contract Error: Unable to find file. Error: Failed to open PDF file: Veronica.pdf Done. Input errors, so no output created.

Maybe there is a chance to change the code slightly to fix a problem. You gave me hope.
I already gave up i was very upset and thought to hire someone and pay him. I hope you can help again and try to fix the error.

There i another thing to consider, mayber it does not matter, but i have figured interesting something.

Maybe the problem related somehow to the PDFtk server installation?

When i made Uninstall to PDFtk server and then run the macro - it still output the file names and give the same error for the number of pages.
In another words, running the macro just with PDFtk toolkit (without serve) or installing the PDFtk server causing the same error.
This is why i guess maybe just insall PDFtk server (like any other program) is not enough.

 

[John_w]

that i did not add any Reference aftet insalling PDFtk server - if you think i miss a reference please let me know.

No VBA project references are required because the code uses late binding of Windows Script Host Object Model (WshShell).

This is the error i got into excel cell B1

'Error: Unable to find file. Error: Failed to open PDF file: C:\temp\Contract Error: Unable to find file. Error: Failed to open PDF file: Schiffer.pdf Done. Input errors, so no output created.

Sorry, I omitted 2 crucial lines which should be placed at the top of the module:

Option Explicit

Const Q As String = """"

The code should work now

 

[prati]

Your code works perfect.

Your macro counts the number of pdf pages correctly.

your macro always count the correct number of the pdf pages.

You can't imagine how grateful I am right now.

I spent a month copy and paste different kind of macros - all of them are not reliable. Their accuracy range between 20%-70% .


Your macro is the only reliable regarding counting pdf pages.

Although my knowledge regarding VBA is poor, I believe that the assumption i made at the beginning was correct - only a macro that use a third-party application will output the correct number of pdf pages with 100% accuracy.
All the other methods- that doesn't use a third-party application are not reliable.
How can i thank you? i want to pay.

 

[John_w]

I'm pleased it works for you. No payment is necessary, nor allowed.