Help With VBA Document Counter

[Baiano42]

I'm trying to create a macro that would auto calculate the number of PDF's in the folder with the active workbook (needing it to be a relative reference as I'll be using this in multiple projects) and add +1 to it, input that number in Cell "H2", and (Ideally) have the macro run once every time that the spreadsheet is opened, rather than having it loop constantly. Could someone please help me adjust the code provided to allow for these features? Thanks in advance for your time and assistance!

Sub sample()
        Dim FolderPath As String, path As String, count As Integer
        FolderPath = ThisWorkbook & "\"
        path = FolderPath & "\*.pdf"
        Filename = Dir(path)
        Do While Filename <> ""
             count = count + 1
             Filename = Dir()
        Loop
        Range("H2").Value = count
        'MsgBox count & " : files found in folder"
End Sub

[Paul_Hossler]

In the ThisWorkbook module

Option Explicit


Private Sub Workbook_Open()
    Dim FolderPath As String, Filename As String, count As Long
    
    FolderPath = ThisWorkbook.path & "\*.pdf"
    
    Filename = Dir(FolderPath)
    
    Do While Filename <> ""
         count = count + 1
         Filename = Dir()
    Loop
    
    ActiveSheet.Range("H2").Value = count
        
End Sub

[Leith Ross]

Hello Baiano42,

Here is another method.

Private Sub Workbook_Open()
    Dim Files As Object

        With CreateObject("Shell.Application")
            Set Files = .Namespace(ThisWorkbook.Path).Items
                Files.Filter 64, "*.pdf"
                Worksheets(1).Range("H2").Value = Files.Count
        End With
End Sub