?
Solved

VBSCRIPT List ALL Subfolders and Files From a Path

Posted on 2013-01-17
3
Medium Priority
?
723 Views
Last Modified: 2013-01-18
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
Comment
Question by:JB4375
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 56

Accepted Solution

by:
Bill Prew earned 2000 total points
ID: 38789818
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
 
LVL 1

Author Comment

by:JB4375
ID: 38793591
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
 
LVL 56

Expert Comment

by:Bill Prew
ID: 38793698
Welcome.

~bp
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question