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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
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,343
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,626
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,771
Messages
5,513,321
Members
408,945
Latest member
gravy834

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top