How to write a script listing sub-dir & files then put the list into Excel ?

Question

Using MS 365 Apps v 16.0

Need to build an office script which lists all sub-directories and files in a given directory, and writes down the list into an Excel spreadsheet.

Are there any special settings to process ?

An example of scritp would be much welcome, thanks in advance...

Answer 1

Yes, you can write a macro to list all sub-directories and files in a given directory and write the list into an Excel spreadsheet. Here's an example of a VBA macro that you can use:

=============

Sub ListFiles()

Dim objFSO As Object

Dim objFolder As Object

Dim objFile As Object

Dim i As Integer

Dim ws As Worksheet

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder("C:\Your\Folder\Path")

Set ws = ThisWorkbook.Sheets("Sheet1")

i = 1

For Each objFile In objFolder.Files

    ws.Cells(i, 1).Value = objFile.Name

    ws.Cells(i, 2).Value = objFile.Path

    i = i + 1

Next objFile

For Each objSubFolder In objFolder.SubFolders

    For Each objFile In objSubFolder.Files

        ws.Cells(i, 1).Value = objFile.Name

        ws.Cells(i, 2).Value = objFile.Path

        i = i + 1

    Next objFile

Next objSubFolder

Set objFile = Nothing

Set objFolder = Nothing

Set objFSO = Nothing

End Sub

=======================

To use this macro, you need to replace "C:\Your\Folder\Path" with the path of the directory you want to list the files and sub-directories for. You also need to change "Sheet1" to the name of the worksheet where you want to output the list.

To run the macro, press Alt + F8 to open the Macro dialog box, select the macro name, and click Run. The macro will list all files and sub-directories in the specified directory and output the list to the specified worksheet.

Answer 2

That's perfect. Thanks a lot !