I need a file count returned to a cell
 

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

I need a file count returned to a cell
 

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

"Oldetowne" wrote:

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

I need a file count returned to a cell
 

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

"Mike H" wrote:

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

"Oldetowne" wrote:

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

I need a file count returned to a cell
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Oldetowne" wrote in message
...
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

I need a file count returned to a cell
 

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Don Guillett" wrote in message
...

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Oldetowne" wrote in message
...
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

I need a file count returned to a cell
 

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


--
Rick (MVP - Excel)


"Oldetowne" wrote in message
...
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

I need a file count returned to a cell
 

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).

--
Rick (MVP - Excel)


"Oldetowne" wrote in message
...
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