Solved

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

Posted on 2013-01-18
9
2,254 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 51

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 51

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 51

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 51

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

21 Experts available now in Live!

Get 1:1 Help Now