Excel formula available?

  KJ823 20:44 22 May 2003

Hi All

Is there a formula you can use in Excel to count the number of files contained within a given folder?

I want to create a spreadsheet with a list of folders showing how many files are in each folder and for this to be updated each time a file is added or deleted.

Hope someone can help.



  VoG™ 16:29 23 May 2003

You need a macro to do this. Create an Excel workbook in the folder that you want to list the files in. ALT+F11 to open the Visual Basic Editor. Insert/Module.

Then copy and paste in the following code:

Sub Auto_Open()

Dim MyFile As String, iRow As Integer

MyFile = Dir(CurDir() & "\" & "*.*")

Sheets.Add after:=Sheets(Sheets.Count)

Do While MyFile <> ""

iRow = iRow + 1

ActiveSheet.Range("A" & iRow).Value = CurDir() & "\" & MyFile

MyFile = Dir()


End Sub

ALT+F11 to exit the VBE, SAVE THE FILE, and close the workbook. Each time you open this workbook a bew sheet will be added containing the current folder listing.

  KJ823 18:19 23 May 2003

Thank you, that is brilliant!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Awful clip art from 1994 is being tweeted every hour by a bot

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017