How to find number of files in a folder via VBA code

Question

Is it possible to write a piece of code that will return number of files in a folder.

Thanks

Answer 1

Following code counts the number of files in a folder and also iterates through the files and lists their filenames in the ActiveSheet.

Ensure that you do not allow the code to overwrite anything on the activesheet in your workbook. Ensure the active sheet is blank.

Sub CountFiles()

    Dim strDir As String

    Dim fso As Object

    Dim objFiles As Object

    Dim obj As Object

    Dim lngFileCount As Long

    strDir = "C:\Users\OssieMac\Documents\Excel\Test Macros"

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set objFiles = fso.GetFolder(strDir).Files

    lngFileCount = objFiles.Count

    MsgBox lngFileCount     'Total number of files

    '***************************************************

    'NOTE: Ensure that the following code does not overwrite _

     anything in your workbook.

    'Active worksheet should be a blank worksheet

    For Each obj In objFiles

      ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = obj.Name

    Next obj

    Set objFiles = Nothing

    Set fso = Nothing

    Set obj = Nothing

End Sub

Answer 2

Thanks; works as required. Can you please point out a reference (or explain) to help understand what is going on in the code.

I am not sure how well I can explain this for you but I will try. If anyone else reads this and either disagrees with any of my explanation or can explain better then feel free to post.

Firstly, in the code example below, I have modified the declaration of the variables. Instead of using the generic Object, it is better to use their proper description. The reason is that the VBA intellisense kicks in which helps with the programming. (More on intellisense below.)

Next paragraph added with edit after initial post.

To use the proper declarations you will need to open the VBA editor and select Tools -> References and scroll down until you find Microsoft Scripting Runtime and check the box and click OK. (Ensure you check the box; not just select the line.) This is individual to the particular workbook; does not remain for all workbooks.

strDir = "C:\Users\OssieMac\Documents\Excel\Test Macros"...... Should be self explanatory.

Set fso = CreateObject("Scripting.FileSystemObject")........ Creates an object of the file system of the computer and assigns to the variable fso and the variable is then used to address the file system on the computer. (Note the line begins with 'Set' when assigning an object to a variable.)

Set objFiles = fso.GetFolder(strDir).Files......... Assigns the files in the specified directory to the variable objFiles and the variable is then used in lieu of the full description. This variable contains the full list of files in the directory.

The following 5 lines of code iterate through the files in objFiles and outputs them to the active sheet. Note that I have enhanced this to my previous code to exclude temporary excel files that are prefixed with the tild and dollar signs (~$).

    For Each objF In objFiles

      If Not objF.Name Like "~$*" Then

        ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = objF.Name

      End If

    Next objF

If Not objF.Name Like "~$*" Then..... Excludes temporary file created when excel workbook is opened. These temporary files are prefixed with the tild and dollar sign (~$). These will normally only occur in ThisWorkbook.Path. However, it is possible for them to be left over from files when Excel crashes and the workbook does not close properly.

ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)........ Finds the next blank cell in column A. It is like when on the worksheet, selecting the last cell in column A and then holding the Ctrl key and pressing the up arrow. The cursor will go up to the first cell it finds with data which is actually the last cell in column A containing data. Offset(1, 0) then moves it down one cell which will be a blank cell.

The following 3 lines is just good housekeeping in that it cleans up the object variables when they are no longer required. Set them to nothing in the reverse order to that which is used to assign objects to them.

    Set objF = Nothing

    Set objFiles = Nothing

    Set fso = Nothing

Now Intellisense. It is when you are programming and place a dot (.) ready to continue with the next sector of code, you get a drop down with possible options. The variables need to be declared correctly instead of declaring as the generic Object. Open a new workbook and save the workbook  in a folder where there are other files. (Must save workbook otherwise ThisWorkbook.Path will error. If using xl2007 or later then save as Macro enabled workbook.). Copy the entire code from below into the VBA editor and try the following examples: Don't forget to set the References as per my edit above.

In this line Set objFiles = fso.GetFolder(strDir).Files......... Delete the .Files from the end and then type the dot and see the dropdown of options.

In this line lngFileCount = objFiles.Count....... Delete the .Count and then re-type the dot.

In this line ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = objF.Name

Delete the .Name from the end and re-type the dot.

Sub CountFiles()

    Dim strDir As String

    Dim fso As FileSystemObject

    Dim objFiles As Files

    Dim objF As File

    Dim lngFileCount As Long

    'strDir = "C:\Users\OssieMac\Documents\Excel\Test Macros"

    'Next line is alternative to previous line using the path of This workbook

    strDir = ThisWorkbook.Path    'Path of this saved workbook

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set objFiles = fso.GetFolder(strDir).Files

    lngFileCount = objFiles.Count

    MsgBox lngFileCount     'Total number of files

    For Each objF In objFiles

      If Not objF.Name Like "~$*" Then

        ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = objF.Name

      End If

    Next objF

    Set objF = Nothing

    Set objFiles = Nothing

    Set fso = Nothing

End Sub

Answer 3

See http://msdn.microsoft.com/en-us/library/t0aew7h6(v=VS.84).aspx

Scripting refers to Windows Script which is accessed using VBScript.

For Object related to FileSystemObject see http://msdn.microsoft.com/en-us/library/95dtkhsz(v=VS.84).aspx...

Some of the objects are;

Drive

Drives

File

Files

Folder

Folders

TextStream

Answer 4

Thanks for taking out time to explain. Most of the things have logically fallen in place. A couple of questions remain.

In the following what does Scripting stand for (I mean what is its function in invoking the file system on the computer)

      Set fso = CreateObject("Scripting.FileSystemObject")........

What other objects can be defined in a similar way?

Thanks again

Answer 5

Thanks; works as required. Can you please point out a reference (or explain) to help understand what is going on in the code.