Windows Scripting Host

Web Lister

This is a modification of Excel File Lister that drills down into a directory and lists all of its files as hypertext links to a web site.  The Excel sheet can function as a datasheet for reviewing the site, or as a reference for checking a backup against the actual site.  If you modify it, be careful, since List() calls itself recursively.  Also, although the script looks short, sending the right column, row, and relative path variables to the to the functions can be a little difficult to configure.

You can download the thing for free.  The weblister is contained in a ZIP file as "weblister.txt" so that you can inspect the code.  Change the extension to ".vbs" so that you can run it.   There is a "readme.txt" in the zip that explains how to enter a web path in the second input box.

weblister.zip

Weblister Dialogs and Excel Sheet

All files and subdirectories except weblister2.vbs itself are listed in columns in an Excel file.

CODE

' Filename: weblister2.vbs   Version 2
'
' Purpose: Automatically generates an Excel 97 listing of all files in a local
' directory website directory backup so that they can be checked against the
' actual website. The Excel file can also be used to generate "datasheets" that 
' you can send to people in your company for reviewing the sections of the website
' for which they are responsible. 
'
' Place weblister.vbs in the directory
' you wish to list and double click it.
'
' Created by: Peter Kosenko, August 28, 2000

Dim website ' The url path to the web site directory you are listing
Dim objXL ' Excel application object
Dim fso ' file system object
Dim scriptfile ' path and filename of the running script
Dim scriptpath ' path of running script
Dim row ' row position in Excel
Dim column ' column position in Excel
Dim webpath
Dim currentpath ' current folder path

row = 3
column = 1

Welcome_MsgBox_Message = "This WSH application lists all files " & _
     "in a web site directory in an Excel 97 workbook."
Welcome_MsgBox_Title = "Web Lister"
Call Welcome()

webpath = website

' Get the full path and filename of the script file

scriptfile = WScript.ScriptFullName 

' Get the path to the script file

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(scriptfile)
scriptpath = f.ParentFolder
Set f = Nothing

' Excel File Creation

Set objXL = WScript.CreateObject("Excel.Application")
objXL.Visible = TRUE

' ".Workbooks." is the short form for Workbooks(1).WorkSheets(1), the 
' default opened object.

objXL.Workbooks.Add
objXL.Cells(1,1).Value = "Folder:"
objXL.Cells(1,2).Value = scriptpath
objXL.Cells(2,1).Value = website

List scriptpath, row, column

' Cleanup

Set objXL = Nothing
Set fso = Nothing


WScript.Quit

' ********************************************************************************
' * Welcome()
' ********************************************************************************

Sub Welcome()
     Dim intDoIt, prompt, boxtype
     prompt = "Enter the web path to the directory " _
          & "you are listing. For example: http://home.netwood.net/kosenko" & Chr(10) _
          & Chr(10) & "If you enter nothing, the application will exit."
     boxtype = vbOKCancel + vbInformation
     intDoIt = MsgBox(Welcome_MsgBox_Message, _
     boxtype, _
     Welcome_MsgBox_Title )

     If intDoIt = vbCancel Then
          WScript.Quit
     Else
          website = Trim(InputBox(prompt, Welcome_MsgBox_Title))
          If website = "" Then
               WScript.Quit
          End If
     End If
End Sub

' ********************************************************************************
' ListFiles(folder, row, column, webpath)
' Variables:
' folder - the full path to the folder in which the files are found
' row - the row of the Excel file in which the listing will begin
' column - the number of columns from the left in which the listing occur
' webpath - relative path to the current file
' ********************************************************************************
Sub ListFiles(folder, row, column, webpath)
     Dim fl, fc
     Set fl = fso.GetFolder(folder)
     Set fc = fl.Files

     For Each f in fc ' f is the file
          If f.name <> "weblister.vbs" Then
               objXL.Cells(row,column).Value = f.name
               With objXL.Workbooks(1).Worksheets(1)
                    .Hyperlinks.Add .Cells(row, column), webpath & "/" & f.name
                    .Cells(row, column + 1).Value = f.DateLastModified
               End With
               row = row + 1
          End If
     Next

     ' Cleanup

     Set fl = Nothing
     Set fc = Nothing

End Sub

' ********************************************************************************
' List(folderpath, row, column)
' Purpose: The main list function
' Variables:
' folderpath - the full path to the folder in which the files are found
' row - the row of the Excel file in which the listing will begin
' column - the number of columns from the left in which the listing occur
' ********************************************************************************
Function List(folderpath, row, column)

     Dim fl, subfolders, currentpath, tempPath
     Set fl = fso.GetFolder(folderpath)
     ListFiles folderpath, row, column, webpath
     Set subfolders = fl.subfolders
     For each subfolder in subfolders
          column = column + 1
          tempPath = webpath
          currentpath = folderpath & "\" & subfolder.name
          webpath = webpath & "/" & subfolder.name
          objXL.Cells(row, column).Value = subfolder.name
          objXL.Cells(row, column).Interior.ColorIndex = 27 ' yellow
          row = row + 1
          List currentpath, row, column
          column = column - 1
          webpath = tempPath
     Next

     Set fc = Nothing
     Set fl = Nothing

End Function


| Editing | Writing | Web Design | Programming | Music |

Home Home Home

E-mail Pete the answers to all his questions.