Download Folder Names into an Excel sheet

[Nick70]

Hi,

What I need to do is quite simple.

I need to check that all clients have a specific sub-folder created for them in a specific folder (called Client Names) found in a directory.

I therefore need to download all the client sub-folders found in that folder (i.e. Client Names) to see if they match with my client list.
Every client sub-folder contains some excel files but I am NOT interested in these I only want the full list of client sub-folder names (each of which corresponds to a client name).

Folder 'Client Names' which contains client sub-folders is found in directory below:

C:\Users\user\OneDrive\Desktop\Client Names

Can you please let me know if there is an excel formula or a macro which can go into the directory look into 'Client Names' folder and return a list in an excel sheet of all sub-folders contained in it (i.e. the client names)?

Thanks,
Nic

 

[Marc L]

Hi,​

​

can be easily done under VBA like in this thread​

bring files from folders and subfolders to multiple columns based on folders names

hi I need macro to brings all files from folder and subfolders to multiple columns so my directory isle "C:\Users\mm\Desktop\FILES" it contains many subfolders FILE1 up to FILE 6 and it's increasable and each subfolder contain files are different...

www.mrexcel.com

 

[Nick70]

Hi,

The original question from the thread you sent says:

'I need a macro to brings all files from folder and subfolders to multiple columns..."

What I need and I mentioned this in my email is not file names but folder names.

Is there a macro to extract folder names as opposed to file names?

Thanks,
Nic

 

[Marc L]

​

Yes like in the link where in posts #2 & 8 each code extracts first only the directories name, so just read & see …​

​

A must see in VBA help : Dir function …​

 

[DanteAmor]

Run this macro, the folder names will stay on the active sheet in cell A2 and down.

Sub subfolders()
  Dim sPath As String, DirFile As String
  Dim i As Long
  
  sPath = "C:\Users\user\OneDrive\Desktop\Client Names\"
  DirFile = Dir(sPath, vbDirectory)
  i = 2
  Do While DirFile <> ""
    If DirFile <> "." And DirFile <> ".." And _
      ((GetAttr(sPath & DirFile) And vbDirectory) = 16) Then
      Range("A" & i).Value = DirFile
      i = i + 1
    End If
    DirFile = Dir
  Loop
End Sub

 

[Nick70]

That works perfectly.

Thanks a lot Dante and Marc.

Nic

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Larrylegend1]

I'm glad to help you. Thanks for the feedback.

Can this be done with formulas as opposed to vba and if so how please.