Solved

VBScript List all Subfolders and Files within a Path for a list of Computers

Posted on 2013-01-18
9
2,279 Views
Last Modified: 2013-01-20
I've got a script that does the following:
1. List all subdirectories and files from a specified path.
2. If the path isn't found send a msg stating it.

From there I wanted to do the same thing for a list of computers.

What occurs is a little different:
1. It lists all the files in the first subdiretory the first computer
2. Rather than skipping to the next subfolder the error checking writes that the folder couldn't be found and goes to the next computer.

I've verified that it's never hitting the subfolder sub routine.

Thanks,

JB

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

sServers = "c:\bcalist.txt"  'Server list
objStartFolder = "\C$\WINDOWS\Microsoft.NET"  'Backup Folder

Set oFSO = CreateObject("Scripting.FileSystemObject")
aServers = Split(oFSO.OpenTextFile(sServers).ReadAll, VbCrLf)

      For Each sServer In aServers
          If trim(sServer) <> "" then CheckServer(sServer)
      Next

            Sub CheckServer(sName)
            WScript.Echo "Checking Computer: " & sName
                  Set objFolder = objFSO.GetFolder("\\" & sName & objStartFolder)
                  WScript.Echo objFolder
                        If Err.number <> 0 Then
                              WScript.Echo "File Path Not Found"
                              objExcelOutput.Sheets(1).Cells(counter,1).Value = sName
                              objExcelOutput.Sheets(1).Cells(counter,2).Value = "Cannot Access - " & Err.Description
                              Counter = Counter +1
                        Else
                        End If
                  Wscript.Echo objFolder.Path
                  objExcelOutput.Sheets(1).Cells(counter,1).Value = sName
                  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(1).Cells(counter,3).Value = objFile.name
                      objExcelOutput.Sheets(1).Cells(counter,4).Value = objFile.DateLastModified
                      Counter = Counter + 1
                  Next
                  Wscript.Echo      
                  ShowSubFolders objFSO.GetFolder(objStartFolder)
            End Sub

            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(1).Cells(counter,3).Value = objFile.name
                            objExcelOutput.Sheets(1).Cells(counter,4).Value = objFile.DateLastModified
                            Counter = Counter + 1
                    Next
                    Wscript.Echo
                    ShowSubFolders Subfolder
                Next
            End Sub
0
Comment
Question by:JB4375
  • 4
  • 4
9 Comments
 
LVL 52

Expert Comment

by:Bill Prew
ID: 38795605
Well, you are using error checking, but you haven't done ON ERROR RESUME NEXT, so I suspect it's getting confused.  I think the code can be simplified a bit too.  See if thos works better for you.

strServers = "c:\bcalist.txt"  'Server list
strStartFolder = "\C$\WINDOWS\Microsoft.NET"  'Backup Folder
'strStartFolder = "C:\WINDOWS\Microsoft.NET"

Set objFSO = CreateObject("Scripting.FileSystemObject")
arrServers = Split(objFSO.OpenTextFile(strServers).ReadAll, VbCrLf)

For Each strServer In arrServers
    If Trim(strServer) <> "" Then
        WScript.Echo "Checking Computer: " & strName
        If objFSO.FolderExists("\\" & strName & strStartFolder) Then
            ShowSubFolders objFSO.GetFolder("\\" & strName & strStartFolder)
        End If
    End If
Next

Sub ShowSubFolders(objFolder)
    Wscript.Echo objFolder.Path
    objExcelOutput.Sheets(1).Cells(intCounter, 2).Value = objFolder.Path

    For Each objFile In objFolder.Files
        Wscript.Echo objFile.Name & ", " & objFile.DateLastModified
        objExcelOutput.Sheets(1).Cells(intCounter, 3).Value = objFile.Name
        objExcelOutput.Sheets(1).Cells(intCounter, 4).Value = objFile.DateLastModified
        intCounter = intCounter + 1
    Next

    Wscript.Echo

    For Each objSubfolder In objFolder.SubFolders
        ShowSubFolders objSubfolder
    Next
End Sub

Open in new window

~bp
0
 
LVL 1

Author Comment

by:JB4375
ID: 38795710
BP,

I have On Error Resume Next up top above all the excel code. It's currently turned off because otherwise I wasn't getting anything error related.

I've copied in your code. Not sure what's happening now all I'm getting is the following output from the echo statement:

Checking computer:
Checking computer:
Checking computer:
Checking computer:
Checking computer:
Exit code:0, ooooh
0
 
LVL 52

Expert Comment

by:Bill Prew
ID: 38796413
Sorry, I missed a couple of strName variables that needed to be changed to strServer, try this:

strServers = "c:\bcalist.txt"  'Server list
strStartFolder = "\C$\WINDOWS\Microsoft.NET"  'Backup Folder
'strStartFolder = "C:\WINDOWS\Microsoft.NET"

Set objFSO = CreateObject("Scripting.FileSystemObject")
arrServers = Split(objFSO.OpenTextFile(strServers).ReadAll, VbCrLf)

For Each strServer In arrServers
    If Trim(strServer) <> "" Then
        WScript.Echo "Checking Computer: " & strServer
        If objFSO.FolderExists("\\" & strServer & strStartFolder) Then
            ShowSubFolders objFSO.GetFolder("\\" & strServer & strStartFolder)
        End If
    End If
Next

Sub ShowSubFolders(objFolder)
    Wscript.Echo objFolder.Path
    objExcelOutput.Sheets(1).Cells(intCounter, 2).Value = objFolder.Path

    For Each objFile In objFolder.Files
        Wscript.Echo objFile.Name & ", " & objFile.DateLastModified
        objExcelOutput.Sheets(1).Cells(intCounter, 3).Value = objFile.Name
        objExcelOutput.Sheets(1).Cells(intCounter, 4).Value = objFile.DateLastModified
        intCounter = intCounter + 1
    Next

    Wscript.Echo

    For Each objSubfolder In objFolder.SubFolders
        ShowSubFolders objSubfolder
    Next
End Sub

Open in new window

~bp
0
 
LVL 1

Author Comment

by:JB4375
ID: 38797083
OK now it's showing the echo statement correctly with the computer name following it,  but it's nt incrementing through the folders, and it's not sending anything to the excel file.
0
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.

 
LVL 52

Expert Comment

by:Bill Prew
ID: 38797368
I did a little test here and the basic logic worked fine, with the Wscript.Echo lines displaying the folders and files. I would need to see the rest of the script to troubleshoot the Excel logic.

~bp
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 38797943
Hi, I'm not sure why you mention Excel when the code you're using is VBScript (should be run from a VBS file).  WScript.Echo statement won't work when run within Excel, so it needs to be a VBS.  You also have though, a statement of this
objExcelOutput.Sheets(1).Cells(intCounter, 2).Value = objFolder.Path

which suggests you're missing the
Set objExcelOutput = CreateObject("Excel.Application")

part, and the workbook addition.

I have modified the code to be pure VBS, instantiating an Excel object, and writing the output to it.  Save the code as a VBS file and see how you go.

Regards,

Rob,

strServers = "c:\bcalist.txt"  'Server list
'strStartFolder = "\C$\WINDOWS\Microsoft.NET"  'Backup Folder
strStartFolder = "C:\WINDOWS\Microsoft.NET"

Set objFSO = CreateObject("Scripting.FileSystemObject")
arrServers = Split(objFSO.OpenTextFile(strServers).ReadAll, VbCrLf)
intCounter = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWB = objExcel.Workbooks.Add
Set objSheet = objWB.Sheets(1)

For Each strServer In arrServers
    If Trim(strServer) <> "" Then
        WScript.Echo "Checking Computer: " & Trim(strServer)
        strFolderPath = "\\" & strServer & "\" & Replace(strStartFolder, ":", "$")
        If objFSO.FolderExists(strFolderPath) Then
            ShowSubFolders objFSO.GetFolder(strFolderPath)
        End If
    End If
Next

Sub ShowSubFolders(objFolder)
    Wscript.Echo objFolder.Path
    objSheet.Cells(intCounter, 2).Value = objFolder.Path

    For Each objFile In objFolder.Files
        Wscript.Echo objFile.Name & ", " & objFile.DateLastModified
        objSheet.Cells(intCounter, 3).Value = objFile.Name
        objSheet.Cells(intCounter, 4).Value = objFile.DateLastModified
        intCounter = intCounter + 1
    Next

    Wscript.Echo

    For Each objSubfolder In objFolder.SubFolders
        ShowSubFolders objSubfolder
    Next
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:JB4375
ID: 38798447
Rob,

The short answer is that all theexcel was up top. (Not included)

BP has asked for the entire code, and I would have sworn that I posted it. Must've previewed instead. Anyway that worked perfectly.

Completed below with excel.

Thanks,

JB


strServers = "c:\bcalist.txt"  'Server list
strStartFolder = "C:\WINDOWS\Microsoft.NET"

Set objFSO = CreateObject("Scripting.FileSystemObject")
arrServers = Split(objFSO.OpenTextFile(strServers).ReadAll, VbCrLf)
intCounter = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWB = objExcel.Workbooks.Add
Set objSheet = objWB.Sheets(1)

' Setup Column Headings
objSheet.Cells(1, 1).Value = "Computer Name"
objSheet.Cells(1, 2).Value = "Path / Subfolder Name"
objSheet.Cells(1, 3).Value = "File Name"
objSheet.Cells(1, 4).Value = "Date Last Modified"
 
' Setup Column Widths
objSheet.Columns(1).ColumnWidth = 25
objSheet.Columns(2).ColumnWidth = 50
objSheet.Columns(3).ColumnWidth = 25
objSheet.Columns(4).ColumnWidth = 20
 
' Setup Spreadsheet Range and Top Row
objSheet.Range("A1:D1").Font.Bold = True
objSheet.Range("A1:D1").Interior.ColorIndex = 1 'Black
objSheet.Range("A1:D1").Interior.Pattern = 1 'xlSolid
objSheet.Range("A1:D1").Font.ColorIndex = 44 'Gold
 
objSheet.Range("A2").Select

For Each strServer In arrServers
    If Trim(strServer) <> "" Then
        WScript.Echo "Checking Computer: " & Trim(strServer)
        strFolderPath = "\\" & strServer & "\" & Replace(strStartFolder, ":", "$")
        If objFSO.FolderExists(strFolderPath) Then
              objSheet.Cells(intCounter, 1).Value = strServer
            ShowSubFolders objFSO.GetFolder(strFolderPath)
        End If
    End If
Next

Sub ShowSubFolders(objFolder)
    Wscript.Echo objFolder.Path
    objSheet.Cells(intCounter, 2).Value = objFolder.Path
    'objExcelOutput.Sheets(1).Cells(counter,2).Value = objFolder.Path

    For Each objFile In objFolder.Files
        Wscript.Echo objFile.Name & ", " & objFile.DateLastModified
        objSheet.Cells(intCounter, 3).Value = objFile.Name
        objSheet.Cells(intCounter, 4).Value = objFile.DateLastModified
        intCounter = intCounter + 1
    Next

    Wscript.Echo

    For Each objSubfolder In objFolder.SubFolders
        ShowSubFolders objSubfolder
    Next
End Sub
0
 
LVL 1

Author Closing Comment

by:JB4375
ID: 38798451
Thanks again!!!
0
 
LVL 52

Expert Comment

by:Bill Prew
ID: 38798786
A little surprised on no assist here, I think I did help clean up some of your original code. But glad you got it sorted out at least...

~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

Title # Comments Views Activity
vbscript run from Task Scheduler does not appear to end 7 46
excel:  cumulative data 13 59
VBScript to list Unavailable Mapped Drives 5 44
Enable Intel-VT on a IBM Yoga 7 30
Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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

15 Experts available now in Live!

Get 1:1 Help Now