Excel formula available?

  KJ823 20:44 22 May 03
Locked

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.

TIA

Karen

  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()

Loop

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!
Karen

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

What is ransomware and how do I protect my PC from WannaCry?

What I learned from my mentor, Oscar-winning VFX supervisor Phil Tippett

Siri vs Google Assistant