Counting no. of files.

domex

Board Regular
Joined
Feb 25, 2010
Messages
144
Hi,

I have some files in my shared folder d:\work\... and the no. of files change everyday based on the work done on it.

Simply i need to bring the total count of the files from the specific folder into my spreadsheet.

For e.g. if have 10 files in folder d:\work... it should shows 10 in my spreadsheet.

Any ideas or suggestions.

Ranjith
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
something like this:
Code:
    With Application.FileSearch
        .LookIn = "c:\.............\.............\..."
        .FileType = msoFileTypeAllFiles
        .Execute
         CountOfFiles = .FoundFiles.Count
         End With
?
 
Last edited:
Upvote 0
Hi,
Nice to see u here again.

I do not know how to apply this code.

Can i apply this code in vbmacro module in excel?

Ranjith
 
Upvote 0
Try

MsgBox CreateObject("Scripting.FileSystemObject").GetFolder("C:\").Files.Count
 
Upvote 0
Its Working Great.. but it should be come like msg box for me.. it should be returned as value in cell because i have more than 50 folder to see.

Ranjith
 
Upvote 0
Code:
Function filecounter(folderpath As String) As long
Filecounter=CreateObject("Scripting.FileSystemObject").GetFolder(folderpath).Files.Count  
End Function
or
Code:
Function filecounter42(folderpath As String) As long
   With Application.FileSearch
        .LookIn = folderpath
        .FileType = msoFileTypeAllFiles
        .Execute
         Filecounter42= .FoundFiles.Count
         End With
 End Function
put these codes in a VB module in the file and save
return to the workbook and try them as a function in a cell:
(you should be able to find them in UserDefined Functions in the function wizard)
=filecounter("c:\")
or/and
=filecounter42("c:\")
(i'm pretty sure you'll get different values for folder C:\) :biggrin:
but they both work perfectly.
 
Upvote 0
Hi,

Thank u very much and this is what i expected.

U r great man to help peoples.

Ranjith
 
Upvote 0
Just bear in mind that Application.FileSearch is not supported in Excel 2007 and beyond so any code using this will not work in these releases.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top