VBSCRIPT List ALL Subfolders and Files From a Path

I've got a script that echos this information, and from there I wanted to output this to an excel file so that it would be somewhat discernable.

I as see it it will output:

subFolder     file    dateLastModified
                      file    dateLastModified
                      file    dateLastModified
subfolder      file    dateLastModified
                      file    dateLastModified etc.

I've entered Excel Output lines everywhere there was an WscriptEcho statement, and I realize that I need a counter statement to increment to the next line in the excel document. I've attempted several combinations but it's eluded me thus far.



' Excel Spreadsheet Output Setup
Set objExcelOutput = CreateObject("Excel.Application")
objExcelOutput.Visible = True

' Setup Column Headings
objExcelOutput.Cells(1, 1).Value = "Computer Name"
objExcelOutput.Cells(1, 2).Value = "File Path"
objExcelOutput.Cells(1, 3).Value = "File Name"
objExcelOutput.Cells(1, 4).Value = "Date Last Modified"

' Setup Column Widths
objExcelOutput.Columns(1).ColumnWidth = 20
objExcelOutput.Columns(2).ColumnWidth = 100
objExcelOutput.Columns(3).ColumnWidth = 50
objExcelOutput.Columns(4).ColumnWidth = 20
' Setup Spreadsheet Range and Top Row
objExcelOutput.Sheets(1).Range("A1:D1").Font.Bold = True
objExcelOutput.Sheets(1).Range("A1:D1").Interior.ColorIndex = 1 'Black
objExcelOutput.Sheets(1).Range("A1:D1").Interior.Pattern = 1 'xlSolid
objExcelOutput.Sheets(1).Range("A1:D1").Font.ColorIndex = 44 'Gold
' Set Range to Freeze Top Row & Center
objExcelOutput.Activewindow.FreezePanes = True

Counter = 2 'init to second cell

Set objFSO = CreateObject("Scripting.FileSystemObject")
objStartFolder = "C:\WINDOWS\Microsoft.NET"

Set objFolder = objFSO.GetFolder(objStartFolder)
' Wscript.Echo objFolder.Path
objExcelOutput.Sheets(1).Cells(counter,2).Value = objFolder.Path
Set colFiles = objFolder.Files
For Each objFile in colFiles
'     Wscript.Echo objFile.Name & objFile.DateLastModified
    objExcelOutput.Sheets(2).Cells(counter,3).Value = objFile.name
    objExcelOutput.Sheets(3).Cells(counter,4).Value = objFile.DateLastModified

ShowSubFolders objFSO.GetFolder(objStartFolder)

Sub ShowSubFolders(Folder)
    For Each Subfolder in Folder.SubFolders
'         Wscript.Echo Subfolder.Path
        objExcelOutput.Sheets(1).Cells(counter,2).Value = objFolder.path
        Set objFolder = objFSO.GetFolder(Subfolder.Path)
        Set colFiles = objFolder.Files
        For Each objFile in colFiles
            ' Wscript.Echo objFile.Name & objFile.DateLastModified
            objExcelOutput.Sheets(2).Cells(counter,3).Value = objFile.name
                objExcelOutput.Sheets(3).Cells(counter,4).Value = objFile.DateLastModified
        ShowSubFolders Subfolder
End Sub
Who is Participating?
Bill PrewConnect With a Mentor Commented:
I typically do this by first creating a CSV file from the script you already have, that goes pretty easy, and then Excel can directly open up the CSV.

It can be done the way you are attempting, just a bit more complicated.

Why are you referencing objExcelOutput.Sheets(2), rather than (1) someplaces?

As far as the counter, you can just add 1 to that inside the two FOR loops, either before or after the adding of the file/directory to the excel row.

JB4375Author Commented:
Yeah.... that's what's known as a typo magnified by cut & paste, which is why it appeared that my placement of counter wasn't working correctly.

Working fine now,

Thanks BP
Bill PrewCommented:

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.