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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
something like this:
Code:
    With Application.FileSearch
        .LookIn = "c:\.............\.............\..."
        .FileType = msoFileTypeAllFiles
        .Execute
         CountOfFiles = .FoundFiles.Count
         End With
?
 
Last edited:

domex

Board Regular
Joined
Feb 25, 2010
Messages
144
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
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Try

MsgBox CreateObject("Scripting.FileSystemObject").GetFolder("C:\").Files.Count
 

domex

Board Regular
Joined
Feb 25, 2010
Messages
144

ADVERTISEMENT

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
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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.
 

domex

Board Regular
Joined
Feb 25, 2010
Messages
144
Hi,

Thank u very much and this is what i expected.

U r great man to help peoples.

Ranjith
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,945
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,424
Messages
5,831,543
Members
430,074
Latest member
Francis101

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
Top