I have a spreadsheet that I would like to indicate how
many files are present in a folder on my hard drive.
Is it possible to write some sort of logic formula or VBA
to count the files in folder x?

Thanks,
Jefe

Jefe,

Here are 2 versions, one a simple (slow) Fiilesearch, the second a faster
FileSystemObject version.

The reason I included both is that as expected they return different results
on my machine. Who knows which is correct, not me.

'----------------------------------------------------------------------
' FileSearch
'----------------------------------------------------------------------
Function FilesFound(Drive As String)
Dim fs

Set fs = Application.FileSearch
With fs
.LookIn = Drive
.SearchSubFolders = True
.FileName = "*.*"
If .Execute() 0 Then
FilesFound = .FoundFiles.Count
Else
FilesFound = -1
End If
End With


End Function

'----------------------------------------------------------------------
' FileSystemObject
'----------------------------------------------------------------------
Dim FSO As Object

Function FilesFoundFSO(Drive As String) As Long
Dim i As Long
Dim cfiles As Double
Dim ODrive As Object
Dim oFolder As Object

Set FSO = CreateObject("Scripting.FileSystemObject")

cfiles = 0
' Set ODrive = FSO.GETDRIVE(FSO.getdrivename(Drive))
' For Each oFolder In ODrive.folders
SelectFiles Drive, cfiles
' Next oFolder

FilesFoundFSO = cfiles

End Function

'-----------------------------------------------------------------------
Sub SelectFiles(ByVal sFolder, ByRef FileCount As Double)
'-----------------------------------------------------------------------
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set Folder = FSO.GetFolder(sFolder)

On Error GoTo exit_sub
FileCount = FileCount + Folder.Files.Count
For Each fldr In Folder.Subfolders
SelectFiles fldr.Path, FileCount
Next
exit_sub:
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jefe" wrote in message
...
I have a spreadsheet that I would like to indicate how
many files are present in a folder on my hard drive.
Is it possible to write some sort of logic formula or VBA
to count the files in folder x?

Thanks,
Jefe

Jefe,

mis-read your question, read it as a drive not folder. Both sill work on a
single folder, just use
MsgBox FoundFiles("C:\myTest")
or
MsgBox FoundFilesFSO("C:\myTest")

By the way, they also count all subfolder files, not just the top level.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Jefe,

Here are 2 versions, one a simple (slow) Fiilesearch, the second a faster
FileSystemObject version.

The reason I included both is that as expected they return different
results
on my machine. Who knows which is correct, not me.

'----------------------------------------------------------------------
' FileSearch
'----------------------------------------------------------------------
Function FilesFound(Drive As String)
Dim fs

Set fs = Application.FileSearch
With fs
.LookIn = Drive
.SearchSubFolders = True
.FileName = "*.*"
If .Execute() 0 Then
FilesFound = .FoundFiles.Count
Else
FilesFound = -1
End If
End With


End Function

'----------------------------------------------------------------------
' FileSystemObject
'----------------------------------------------------------------------
Dim FSO As Object

Function FilesFoundFSO(Drive As String) As Long
Dim i As Long
Dim cfiles As Double
Dim ODrive As Object
Dim oFolder As Object

Set FSO = CreateObject("Scripting.FileSystemObject")

cfiles = 0
' Set ODrive = FSO.GETDRIVE(FSO.getdrivename(Drive))
' For Each oFolder In ODrive.folders
SelectFiles Drive, cfiles
' Next oFolder

FilesFoundFSO = cfiles

End Function

'-----------------------------------------------------------------------
Sub SelectFiles(ByVal sFolder, ByRef FileCount As Double)
'-----------------------------------------------------------------------
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set Folder = FSO.GetFolder(sFolder)

On Error GoTo exit_sub
FileCount = FileCount + Folder.Files.Count
For Each fldr In Folder.Subfolders
SelectFiles fldr.Path, FileCount
Next
exit_sub:
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jefe" wrote in message
...
I have a spreadsheet that I would like to indicate how
many files are present in a folder on my hard drive.
Is it possible to write some sort of logic formula or VBA
to count the files in folder x?

Thanks,
Jefe