Excel formula available?

  KJ823 20:44 22 May 03

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 03

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 03

Thank you, that is brilliant!

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Prehistoric Britain is laid out in these Royal Mail stamp illustrations

Best running headphones | Best sport & fitness headphones: 4 brilliant pairs of wireless…