Question: I will post my solution to this question, but maybe others have found a better way.

I wanted to obtain the number of pages in a pdf document using VBA. 

I reviewed similar [vba] and [acrobat] questions, but I did not find a stand alone solution. After reviewing other posts, Adobe Acrobat's SDK, and the VBA object browser, I learned enough to piece together this solution.

I am running Excel 2013 and Adobe Acrobat 9.0 Pro.

I understand its ok to [answer my own question](https://stackoverflow.com/help/self-answer).

Answer: 
This solution works when Excel 2013 Professional and Adobe Acrobat 9.0 Pro are installed.

You will need to enable the Adobe object model: Tools -> References -> Acrobat checkbox selected.

[Adobe's SDK](http://help.adobe.com/en_US/acrobat/acrobat_dc_sdk/2015/HTMLHelp/index.html#t=Acro12_MasterBook%2FIAC_API_OLE_Objects%2FGetNumPages.htm&rhsearch=getnumpages&rhhlterm=getnumpages&rhsyns=%20) has limited documentation on the GetNumPages method.

```vb
'with Adobe Acrobat 9 Professional installed
'with Tools -> References -> Acrobat checkbox selected

Sub AcrobatGetNumPages()

Dim AcroDoc As Object

Set AcroDoc = New AcroPDDoc

AcroDoc.Open ("C:\Users\Public\Lorem ipsum.pdf") 'update file location

PageNum = AcroDoc.GetNumPages

MsgBox PageNum

AcroDoc.Close

End Sub

```
Inspired from : <https://www.extendoffice.com/documents/excel/5330-excel-vba-pdf-page-count.html>

I created the function below. I do not have Adob accrobat pro installed.

Sub Test()

```
Dim vFolder, vFileName

vFolder = "D:\Test Count Pages In PDF File\"
'vFolder = "D:\Test Count Pages In PDF File\" '--> fine for both forms (with or without PathSeparator)
vFileName = "My File.pdf"
Debug.Print fNumberOfPages_in_PDF_File(vFolder, vFileName)

```

End Sub

Function fNumberOfPages\_in\_PDF\_File(vFolder, vFileName)

```
Dim xStr As String
Dim xFileNum As Long
Dim RegExp As Object

'--- Number of Pages =0 if the file is not a PDF file
If Not vFileName Like "*.pdf" Then
    fNumberOfPages_in_PDF_File = 0
    Exit Function
End If

'--- Add PathSeparator ("\") if it does not exist
If Right(vFolder, 1) <> Application.PathSeparator Then
    vFolder = vFolder & Application.PathSeparator
End If

'--- Count the number of pages in Pdf File
xStr = ""
Set RegExp = CreateObject("VBscript.RegExp")
RegExp.Global = True
RegExp.Pattern = "/Type\s*/Page[^s]"
xFileNum = FreeFile
Open (vFolder & vFileName) For Binary As #xFileNum
    xStr = Space(LOF(xFileNum))
    Get #xFileNum, , xStr
Close #xFileNum

fNumberOfPages_in_PDF_File = RegExp.Execute(xStr).Count

```

End Function