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

prati

New Member
Joined
Jan 25, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
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-

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-
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
1611591353250.png



Second option is to write a vba that will use Pdfinfo
1611591475804.png


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
1611591571009.png


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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,714
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

New Member
Joined
Jan 25, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
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.
 

prati

New Member
Joined
Jan 25, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,714
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.

VBA Code:
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
 
Solution

prati

New Member
Joined
Jan 25, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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)

1611960138720.png


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.

1611959967083.png



1611960872197.png



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.

1611959888801.png



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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,714
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:
VBA Code:
Option Explicit

Const Q As String = """"
The code should work now :)
 

prati

New Member
Joined
Jan 25, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,714
I'm pleased it works for you. No payment is necessary, nor allowed.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,611
Messages
5,625,829
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top