PDA

View Full Version : [SOLVED] Help With VBA Document Counter



Baiano42
07-11-2019, 12:31 PM
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
07-12-2019, 06:59 AM
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
07-13-2019, 11:53 AM
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