Solved

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

Posted on 2013-01-18
9
2,421 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
[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
  • 4
  • 4
9 Comments
 
LVL 56

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 56

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
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.

 
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
 
LVL 56

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 56

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

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…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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