Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-18
9
Medium Priority
?
2,475 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 57

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 57

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 57

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 2000 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 57

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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…
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

704 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