Solved

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

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

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 53

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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 53

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 53

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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