Hello,

I want to create a macro that will go to a folder, count the excel
documents in it (There will only be excel documents in it if that
helps), go back to my original document and enter the number of files
in a cell.

Is this possible?

Thanks to Dave Petersen for helping me with a similar situation a month
or two ago. This should work:

Private Sub CountFiles()
Dim i As Long
Dim FS As FileSearch

Set FS = Application.FileSearch

With FS
.LookIn = "C:\" 'change this to your folder
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) 0 Then
Range("A1") = FS.FoundFiles.Count
End If
End With
End Sub



*** Sent via Developersdex http://www.developersdex.com ***

The folder name will either have to be passed to the macro by another macro

Sub gmunroMacro(sFolderName As String)
Dim i As Integer

Dim sResult As String

i = 0

sResult = Dir(sFolderName & "\*.*")

Do While sResult < ""
i = i + 1
sResult = Dir()
Loop

Range("A1").Value = i
End Sub

or be hard coded into the macro

Sub gmunroMacro()
Dim i As Integer

Dim sResult As String

i = 0

sResult = Dir("C:\gmunroFolder\*.*")

Do While sResult < ""
i = i + 1
sResult = Dir()
Loop

Range("A1").Value = i
End Sub


gmunro wrote in message
.com...
Hello,

I want to create a macro that will go to a folder, count the excel
documents in it (There will only be excel documents in it if that
helps), go back to my original document and enter the number of files
in a cell.

Is this possible?

One more way:

Option Explicit
Sub testme()
Dim FSO As Object
Dim myFolder As String

myFolder = "C:\my documents\excel"

Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FolderExists(myFolder) Then
activesheet.range("a1").value = FSO.GetFolder(myFolder).Files.Count
End If

End Sub

gmunro wrote:

Hello,

I want to create a macro that will go to a folder, count the excel
documents in it (There will only be excel documents in it if that
helps), go back to my original document and enter the number of files
in a cell.

Is this possible?

--

Dave Peterson