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 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
E-mail Pete the answers to all his questions.
