Excel-VBA - if new folder added to path, add new line in Sheet with new folder name

To achieve your goal of automatically adding a new line to an Excel sheet whenever a new folder is added to a specific path, you can use Excel VBA's event handling capabilities along with the FileSystemWatcher class. Here's how you can do it:

1. Create a New Workbook:
   Open Excel and create a new workbook where you want to track the folder additions.

2. Enable Developer Tab:
   If the Developer tab is not already enabled, go to File > Options > Customize Ribbon and check the Developer option.

3. Access VBA Editor:
   Click on the Developer tab and then click on "Visual Basic" to open the VBA editor.

4. Add the Code:
   In the VBA editor, you'll need to add code to the ThisWorkbook module. Right-click on the VBAProject (YourWorkbookName) in the Project Explorer window and choose Insert > Module. Add the following code:

   vbCopy code
   Dim WithEvents watcher As Object
   
   
   
   Private Sub Workbook_Open()
   
       Set watcher = CreateObject("Scripting.FileSystemObject")
   
       Set watcher = watcher.CreateTextFile("C:\PathToWatch\folderlist.txt", True)
   
       watcher.WriteLine "Folder List"
   
       WatchFolders "C:\PathToWatch"
   
   End Sub
   
   
   
   Private Sub watcher_Created(ByVal Path As String)
   
       Dim ws As Worksheet
   
       Set ws = ThisWorkbook.Sheets(1)
   
       ws.Cells(ws.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Value = Path
   
   End Sub
   
   
   
   Sub WatchFolders(ByVal FolderPath As String)
   
       Set watcher = CreateObject("Scripting.FileSystemObject")
   
       Set watcher = watcher.GetFolder(FolderPath)
   
   End Sub
   
   

   Make sure to replace "C:\PathToWatch" with the actual path you want to monitor.

5. Save and Close VBA Editor:
   Save the workbook and close the VBA editor.

6. Enable Macros:
   When you open the workbook, you may be prompted to enable macros. Allow the macros to run.

7. Testing:
   Whenever a new folder is created in the specified path, a new line will be added to the sheet containing the folder's name.

Remember that the Scripting.FileSystemObject uses VBA events to monitor changes in the file system. This solution should work, but keep in mind that there may be some limitations, and it might not capture all folder additions immediately. Also, you might need to handle potential errors and edge cases depending on your specific use case.