Is it possible to write a piece of code that will return number of files in a folder.
Thanks
April 9, 2024
Click here to learn more 💡
May 10, 2024
Is it possible to write a piece of code that will return number of files in a folder.
Thanks
Reported content has been submitted
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
Reported content has been submitted
4 people found this reply helpful
·Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
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
Reported content has been submitted
3 people found this reply helpful
·Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.