count files in a folder automatically??

Hi all,

I have a spreadsheet which counts files in a specific windows folder using =CountFiles("C:\My Files","BMP"). I've just made up the directory on that. The number of files in the folder grows all day.

I also had to add a module to the sheet I found the code online:-

Private Function CountFiles(strDirectory As String, Optional strExt As String = "*.*") As Double
'Author : Ken Puls (www.excelguru.ca)
'Function purpose: To count files in a directory. If a file extension is provided,
' then count only files of that type, otherwise return a count of all files.

Dim objFso As Object
Dim objFiles As Object
Dim objFile As Object

'Set Error Handling
On Error GoTo EarlyExit

'Create objects to get a count of files in the directory
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFiles = objFso.GetFolder(strDirectory).Files

'Count files (that match the extension if provided)
If strExt = "*.*" Then
CountFiles = objFiles.Count
Else
For Each objFile In objFiles
If UCase(Right(objFile.Path, (Len(objFile.Path) - InStrRev(objFile.Path, ".")))) = UCase(strExt) Then
CountFiles = CountFiles + 1
End If
Next objFile
End If

EarlyExit:
'Clean up
On Error Resume Next
Set objFile = Nothing
Set objFiles = Nothing
Set objFso = Nothing
On Error GoTo 0

End Function

My problem is that I have to press enter on the cell formula for it to update. any ideas on how to get it to calculate when the sheet/workbook is opened.

Try making the function volatile by adding this to the start of the function:

Please Login or Register  to view this content.

Remember to use code tags when posting code per the forum rules.

Dom

Just pop it at the beginning:

Please Login or Register  to view this content.

Dom

Making the udf volatile will work but the workbook has to calculate for the formula to refresh.

You could add this to the worksheet code page:

Please Login or Register  to view this content.

It will then calculate each time you activate the worksheet.

Dom

Event codes like Workbook_Open and Worksheet_Activate need to go on the relevant code pages whereas the main function code should go in a regular code module.

This article should explain it: http://www.contextures.com/xlvba01.html#Worksheet

Dom

But you haven't made the function volatile.

Dom

Just where I said before: http://www.excelforum.com/2480749-post4.html

Dom