Results 1 to 3 of 3

Count number of pages of pdf files in directory by VBA

Threaded View

  1. #1
    Registered User
    Join Date
    11-29-2017
    Location
    belgium
    MS-Off Ver
    Office 365 ProPlus
    Posts
    32

    Count number of pages of pdf files in directory by VBA

    I need to count the total number of pages in a big directory with thousands of pdf files.
    First I need to be sure that the criteria is: pdf of the year 2020, month does no matter. In my code below you can see that I used *_2020_??.pdf what means that any sign before _2020_ may been used and after _2020_ there is again the possibility for 2 digits.

    Background: naming of the pdf files is e.g. 12345_2020_11.pdf what means: name of the file is 12345 of the year 2020 of the month 11.

    I found a code on the internet but it seems not to work. It stops after adding the heading titles File Name and Pages, and then there is no further progress at all or it stops without showing error message.
    Conclusion after testing is: the script is ONLY looking in the root directory, not in subfolders. So I need some help to integrate code to search in all subfolders.


    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 & "*_2020_??.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
    Last edited by Jesseke; 11-20-2020 at 07:49 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Scan subfolders for PDF files and count pages
    By ISMI in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 10-04-2018, 10:13 AM
  2. Count Rows in all files in a directory
    By dyung in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2016, 05:32 PM
  3. Determine number of files in a dynamic directory
    By desolatori in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-06-2014, 10:23 AM
  4. How can i get the records/rows count of files in a directory
    By anaconte1010 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-01-2013, 12:54 PM
  5. How to count # of .xls files in a directory - I NEED HELP PLEASE
    By anaconte1010 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-10-2013, 12:04 PM
  6. Check Number of Files in Directory
    By dvent in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-12-2007, 10:13 PM
  7. Count files in directory
    By Vlad999 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-21-2006, 07:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1