wsnyder

New Member
Joined
Sep 23, 2018
Messages
29
Hi all,

Trying to get page count for all pdf files.
My current code at the bottom.
Error here :
Dim Ac_Fi As Acrobat.AcroPDDoc
Error message:
Compile error:
User-defined type not defined

I set references to all of these libraries I see available that appear to be Adobe related libraries:
  • Acrobat Access 3.0 Type Library
  • Adobe Acrobat Browser Control Type Library 1.0
  • AcroBrokerLib
I only have Adobe Acrobat Reader DC and Able2Extract from InvestInTech.

Or do you have other suggestions? I saw some threads suggesting to use RexEx to read the file and parse to get the page count, however I don't think that will give good results for my files due to mix of digital and scanned files.

thanks
-w

code:
VBA Code:
Option Explicit
Sub Get_Page_Count()

    'Objects
        Dim wb As Workbook
        Dim wsFolders As Worksheet
        Dim wsPageCount As Worksheet
        Dim FSO As Object
        Dim F_Fol As Object
        Dim F_File As Object
        Dim Ac_Fi As Acrobat.AcroPDDoc
    
    'Variables
        Dim T_Str As String
        Dim i As Long
        Dim r As Long
        Dim j As Long

    'Excel enrionment - speed things up
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With

    'Initalize objects
        Set wb = ThisWorkbook
        With wb
            Set wsFolder = .Worksheets("Folders")
            Set wsPageCount = .Worksheets("PageCount")
        End With
        Set FSO = CreateObject("Scripting.FileSystemObject")
        
    'Initialize variables
        j = 2   'Begin output
        r = GetRows(ws:=ws, _
                    col:=2) 'Max rows list of folders and subfolders


    'Loop folders and subfolders
    'Get file name and page count
        For i = 2 To r
            Set F_Fol = FSO.getfolder(ws.Cells(i, 2).Value)  'Folders are listed in Col 2 on worksheet "Folders"
        
            For Each F_File In F_Fol.Files
                T_Str = UCase(F_File.Path)
                If Right(T_Str, 4) = ".PDF" Then
                    Set Ac_Fi = New Acrobat.AcroPDDoc
                    Ac_Fi.Open T_Str
                    wsPageCount.Cells(j, 1).Value = T_Str
                    wsPageCount.Cells(i, 2).Value = Ac_Fi.GetNumPages
                    j = j + 1
                    Ac_Fi.Close
                    Set Ac_Fi = Nothing
                    Next j
                End If
            Next F_File

'Tidy Up
    'Destroy objects
        Set F_File = Nothing
        Set F_Fol = Nothing
        Set FSO = Nothing
        Set wsFolder = Nothing
        Set wsPageCount = Nothing
        Set wb = Nothing

    'Excel enrionment - restore
        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
            .EnableEvents = True
            .Calculation = xlCalculationAutomatic
        End With

End Sub

Functions:
Code:
Public Function GetRows(ws As Worksheet, _
                        col As Long) As Long
filesys
    'Input          :   ws  : a Worksheet
    '               :   col : Column Number
    'Output         :   A row number of type long
    
    'Get last row of column
        With ws
            GetRows = .Cells(Rows.Count, col).End(xlUp).Row
        End With
        
End Function
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Watch MrExcel Video

Forum statistics

Threads
1,118,788
Messages
5,574,294
Members
412,585
Latest member
devtr
Top