Solved

VBSCRIPT List ALL Subfolders and Files From a Path

Posted on 2013-01-17
3
719 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
  • 2
3 Comments
 
LVL 53

Accepted Solution

by:
Bill Prew earned 500 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 53

Expert Comment

by:Bill Prew
ID: 38793698
Welcome.

~bp
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

828 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