Solved

VBSCRIPT List ALL Subfolders and Files From a Path

Posted on 2013-01-17
3
715 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 52

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 52

Expert Comment

by:Bill Prew
ID: 38793698
Welcome.

~bp
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

930 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now