Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

add new sheets based on folders names in directory

0

Hi,

currently I work on project in this step 

I have many folders in this directory  C:\Users\HS\Desktop\data

example folders names OUTPUT , MAIN, REPORT .... 

so I have RUNNING sheet name when try to  run the macro I would  add sheets names after RUNNING  sheet  but should  add  allo of sheets at once (OUTPUT,MAIN,REPORT,.... so on) based on folders names , when try running  the macro  every time, then will delete all of sheets  have already added  before add again to avoid  error in the macro .

this is my try, but I'm still so far for my goal.

Sub test()
Dim fld As Variant
Dim fpath As String
fpath = "C:\Users\HS\Desktop\data"
fld = Dir(fpath & "\")
Do Until fld = ""
fld = Dir
Loop
With Worksheets.Add
.Name = fld
End With
End Sub

I hope some body help.

excel
MS Office Version: 2019
posted by: Hasson (rep: 36) on Tue May 13, 2025 at 4:14 pm
Answer
Discuss

Discussion

Hi again Hassan. 

I'm a bit confused by the wording of your Question, sorry. Do you want to create a workbook with a sheet named RUNNING plus a sheet with the name of each folder at the first level below your target folder (C:\Users\HS\Desktop\data)?
John_Ru (rep: 6722) May 13, '25 at 5:18 pm
Hello again Hasson,

Like John, I too don't understand what you are trying to do.  Firstly, looking at your code it appears that your "With ..... End With" block (3 lines) needs to be inside your "Do Until ..... Loop" block. Are OUTPUT, MAIN, REPORT ..... actually folders or are they Excel files? Your code loops through the files in the "data" folder (C:\Users\HS\Desktop\data). The way you have your code the sheets added will have long names - C:\Users\HS\Desktop\data\workbookfilename. Where is the sheet RUNNING located?  It seems like you want to add sheets after the sheet RUNNING that is in an existing Excel file. What is that file name?

If you can clear up the confusion for us we should be able to help you.

Cheers   :-)
WillieD24 (rep: 687) May 13, '25 at 5:41 pm
Hi John,
no need create workbook with a sheet named RUNNING.
the workbook  a sheet named RUNNING is open , just run the macro to add sheets after RUNNING sheet based on folders names  without add new workbook.
not really sure what you mean at the first level !
Hasson (rep: 36) May 14, '25 at 3:15 am
Hi willie,
It seems like you want to add sheets after the sheet RUNNING that is in an existing Excel file. 
YES.
What is that file name?
the file name is RESULT.
Where is the sheet RUNNING located?
will be sheet3
Hasson (rep: 36) May 14, '25 at 3:20 am
Thanks Hassan, please see the attached file.
John_Ru (rep: 6722) May 14, '25 at 5:58 am
Add to Discussion

Answers

0
Selected Answer

Hassan

Rather than use DIR (and extract the folder names), you could use the VBA File System Object - it's much simpler!

In the attached file, Module 1 contains this code (with comments) and other users must be sure to change the lines in bold (near the top):

Private Sub WorksheetPerFolder()
    Dim Targ As String, objFSO As Object, KeepWs As String, Fldr As Object, Txt As String

    'Fix parameters - target directory and worksheet to keep
    Targ = "C:\Users\HS\Desktop\data"
    KeepWs = "RUNNING"

    ' delete other sheets (without confirmation)
    Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> KeepWs Then ws.Delete
    Next ws
     Application.DisplayAlerts = True

    ' create FSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    ' fix folder (MUST exist)
    Set Fldr = objFSO.GetFolder(Targ)

    ' loop though subfolders...
    For Each SubFldr In Fldr.SubFolders
        '...add as last sheet...
        Set ws = Worksheets.Add(, Worksheets(Worksheets.Count))
        ws.Name = SubFldr.Name
        ' ... and list
        Txt = Txt & SubFldr.Name & vbCrLf
    Next SubFldr

    Worksheets(KeepWs).Activate
    ' tell user
    r = MsgBox(Txt, vbOKOnly, "Created new sheets named...")

End Sub

Hope this fixes things for you.

posted by: John_Ru (rep: 6722) on Wed May 14, 2025 at 5:58 am
Discuss

Discussion

works excellently !
many thanks for your assisatnce.
Hasson (rep: 36) May 14, '25 at 9:21 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login