Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

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.

Thanks,

JB

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

' 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.Range("A2").Select
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
Next
Wscript.Echo

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
        Next
        Wscript.Echo
        ShowSubFolders Subfolder
    Next
End Sub
0
JB4375
Asked:
JB4375
  • 2
1 Solution
 
Bill PrewCommented:
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.

~bp
0
 
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
0
 
Bill PrewCommented:
Welcome.

~bp
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now