[nquadr]

I have the following code that I would like to count files in a folder and return their name.

Function CountFilesInFolder(strFolder As String) As Long
    Dim noOfFiles As Long
    Dim FileName As String
    FileName = Dir(strFolder)
    noOfFiles = 0
    Do While FileName <> ""
    FileName = Dir
    'ActiveSheet.Cells(noOfFiles, 1).Value = FileName
    noOfFiles = noOfFiles + 1
    Loop
    CountFilesInFolder = noOfFiles
    Exit Function
    End Function

However when I uncomment

ActiveSheet.Cells(noOfFiles, 1).Value = FileName  

(Line 8) I get an error, not sure what I am doing wrong.

Also, is there a way to code this without using a function, ideally I would like to run it as a procedure, but I could not get it to work. I am new to VBA, so any help is greatly appreciated.

This post has been edited by nquadr: 20 June 2012 - 08:16 AM

[BobRodes]

Abandon your code entirely and learn how to use the FileSystemObject class. This class has a Folders collection, and each Folder in the collection has a Files collection. Your problem is as easily solved by accessing the Files.Count property of the Files collection in the Folder that you want, assuming you don't want the file count of all the subdirectories as well. To do that, you need to work with a recursive procedure call, which is easy enough if you have learned how to use the FileSystemObject class to access the file count in an individual folder. So, do that first.

[nquadr]

BobRodes, on 20 June 2012 - 01:49 PM, said:

Abandon your code entirely and learn how to use the FileSystemObject class. This class has a Folders collection, and each Folder in the collection has a Files collection. Your problem is as easily solved by accessing the Files.Count property of the Files collection in the Folder that you want, assuming you don't want the file count of all the subdirectories as well. To do that, you need to work with a recursive procedure call, which is easy enough if you have learned how to use the FileSystemObject class to access the file count in an individual folder. So, do that first.

I am using Excel 2007, I do not see the intellisense referencing the FileSystemObject. Can you elaborate on using this class?

[maj3091]

Select Tools - References and add a reference to Microsoft Scripting Runtime. You should then be able to reference it.

[BobRodes]

You'll need to do what maj says first. If you have further problems, post back with specifics.

[nquadr]

maj3091, on 21 June 2012 - 07:59 AM, said:

Select Tools - References and add a reference to Microsoft Scripting Runtime. You should then be able to reference it.

Hi Maj,

Is there something I can also enable in this reference to give me intellisense recommendations when I create an object or a workbook of my own. For example if I have Dim variable as Workbook, and I type variable.[populated list].[more populated list]. I hope my question is clear.

[BobRodes]

No, the intellisense recommendations are either there or not there. I seem to remember that they are there in shdocvw.dll (scripting runtime).