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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,119,094
Messages
5,576,078
Members
412,697
Latest member
ahem27
Top