Попробуйте следующее:
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim ws As Worksheet
Dim lngFileCount As Long
Dim Z as Long
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ws = ActiveSheet
Dim folderpath As String
folderpath = InputBox("N:\Files", "Folder Path")
Set objFolder = objFSO.GetFolder(folderpath)
ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"
For Each objFile In objFolder.Files
If UCase$(Right$(objFile.Name, 4)) = ".PDF" Then
ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = Replace$(UCase$(objFile.Name), ".PDF", "")
Z = Z + 1
End If
Next
If Z = 0 Then
MsgBox "No PDF Files found"
Else
MsgBox "The total number of files in folder: " & Z
End If
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
Я переместил MsgBox за пределы цикла, поэтому вы видите его только один раз с помощью total.
Также свойство objFolder.Count.Z
не существует , вы просто хотите, чтобы переменная Z
хранила количество ваших PDF-файлов.