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

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

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

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

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

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

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