I need a file count returned to a cell

O

Oldetowne

My sheet contains an entry for a folder. I need to return the file count from
that directory into another cell in the same row. I am only moderately
familiar with using macros and scripting. Any help will be very much
appreciated
 
M

Mike H

Hi,

Maybe something like this

Sub LoopThroughDirectory()
Application.DisplayAlerts = False

MyPath = Sheets("Sheet1").Range("A1")
ActiveFile = Dir(MyPath & "*.xls")
Do While ActiveFile <> ""
Count = Count + 1
ActiveFile = Dir()
Loop
Application.DisplayAlerts = True
Sheets("Sheet1").Range("B1") = Count
End Sub

Mike
 
M

Mike H

Perhaps better as a function

call with

=filecount(range contaning path,filter)

=filecount(a1,"xls")

or

=filecount(a1,"doc")



Function filecount(MyPath As String, filter As String) As Long
Application.DisplayAlerts = False
MyPath = Sheets("Sheet1").Range("A1")
ActiveFile = dir(MyPath & "*." & filter)
Do While ActiveFile <> ""
Count = Count + 1
ActiveFile = dir()
Loop
Application.DisplayAlerts = True
filecount = Count
End Function

Mike
 
D

Don Guillett

Another way. Probably does NOT work in 2007
Sub countfiles()
With Application.FileSearch
.LookIn = "C:\a"
.FileType = msoFileTypeExcelWorkbooks
MsgBox (.FoundFiles.Count)
End With
End Sub
 
B

Bernard Liengme

In Excel 2010 (Tech Preview) I get error 445 "Object does not support this
action" pointing to first statement in sub. So it seems your are right, Don,
about it not running in XL2007
best wishes
 
R

Rick Rothstein

This function will return the count of the number of files in the directory
path passed into it...
 
R

Rick Rothstein

This function will return the count of the number of files in the directory
path passed into it...

Function FileCount(Path As String) As Long
On Error Resume Next
FileCount = -1
FileCount = CreateObject("Scripting.FileSystemObject"). _
GetFolder(Path).Files.Count
End Function

So, as an example, you would call it like this...

MsgBox FileCount("c:\temp")

If the folder path passed into the function doesn't exist, then the function
returns a count of -1 (minus one).
 
Top