How to find and count # of files in a directory

[Guest]

Hi I am trying to count the number of files in directory + subs, when I try I
get all files in the directory but not in the subs or I can get all thats in
a sub but not sub and directory. Can anybody help please
Charles

 

[Guest]

Show the code you are using.

 

[Guest]

The code I am using is from "VBA Developer's Handbook which only have code
for Directory or subs there is not much in the help files I can get it to
look into the directory and subs?
Regards
Charles

 

[Guest]

I think you can use something like this to get files from a folder and all
it's subfolders:

Sub getFiles(strFolder As String)

Set fso = CreateObject _
("Scripting.FileSystemObject")

If Not fso.FolderExists(strFolder) Then
Debug.Print "The folder doesn't exist"
Exit Sub
End If

Set folder = fso.GetFolder(strFolder)

For Each file In folder.Files
Debug.Print file.Path
Next

For Each subFolder In folder.Subfolders
Call getFiles(subFolder.Path)
Next

End Sub


If you need only the count of files you can ad a counter variable.

 

[Guest]

Option Explicit
Dim DirectoriesAndFiles() As String
Public count As Integer
Public counter As Integer

Sub Start()
Dim int1 As Integer
Dim DirToSearch As String
Dim x As String
Dim i As Integer
Dim sh As Worksheet
'*************************For Testing the speed of Code
Dim Start As Single
Dim Finish As Single
Dim TotalTime As Single
Start = Timer
'***************************************
count = 0
DirToSearch = "C:\Data1" '"F:\USER\ARNOLGD\EXCEL" 'C:\Main"
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("List of Directories").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set sh = Worksheets.Add(After:=Worksheets( _
Worksheets.count))
sh.Name = "List of Directories"
GetFilesInDirectory (DirToSearch)
LookForDirectories (DirToSearch)

x = "B1:B" & UBound(DirectoriesAndFiles)
Worksheets("List of Directories").Range(x) = _
Application.WorksheetFunction.Transpose(DirectoriesAndFiles)

Application.StatusBar = False

'****************************For Testing the speed of Code
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
MsgBox "Number of Files Found " & UBound(DirectoriesAndFiles) & Chr(13) _
& "Time to Run " & TotalTime & " seconds"
'***************************************

End Sub


'======================================================================
Sub LookForDirectories(ByVal DirToSearch As String)
Dim i As Integer
Dim Directories() As String
Dim Contents As String

counter = 0
DirToSearch = DirToSearch & "\"
Contents = Dir(DirToSearch, vbDirectory)
Do While Contents <> ""
If Contents <> "." And Contents <> ".." Then
If (GetAttr(DirToSearch & Contents) And _
vbDirectory) = vbDirectory Then
counter% = counter% + 1
ReDim Preserve Directories(1 To counter)
Directories(counter) = DirToSearch & Contents
End If
End If
Contents = Dir()
Loop
If counter = 0 Then Exit Sub
For i = 1 To counter
GetFilesInDirectory Directories(i)
LookForDirectories Directories(i)
Next i
End Sub

'=====================================================================
Sub GetFilesInDirectory(ByVal DirToSearch As String)
Dim NextFile As String
NextFile = Dir(DirToSearch & "\" & "*.xls", vbHidden)
Application.StatusBar = "Files Found " & count _
& " " & "Searching " & DirToSearch & "\"
Do Until NextFile = ""
count = count + 1
ReDim Preserve DirectoriesAndFiles(1 To count)
DirectoriesAndFiles(count) = DirToSearch & "\" & NextFile
NextFile = Dir
Loop
End Sub