Submitted By: Tom McGeown

Retrieves a list of shared folders from two separate computers and then displays that information side-by-side in an Excel spreadsheet.

Visual Basic
Edit|Remove
===========================================================
'=      Purpose:        Outputs shares from 2 servers                                            
'=                       side by side in xls spreadsheet                                               
'=      Author:         Tom McGeown                                                                   
'=      Date:           10/07/2007                                                                          
'===========================================================
'declare variables, constants and set objects
Option Explicit
Dim objFs, objShare, objFSO, objFile, strserver, objexcel, objworkbook, objworksheet
Dim i, objRange, objRange2, objRange3
Set objFSo = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Const xlAscending = 1
Const xlYes = 1
objExcel.Visible = True
'open spreadsheet - create it first this script(er) is lazy
Set objWorkbook = objExcel.Workbooks.Open("drive:\path to spreadsheet\spreadsheet.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
'set and connect too first server
strServer = "server1"
set objFs = GetObject("WinNT://" & strServer & "/LanmanServer,FileService")
i = 2
objworksheet.cells(1, 1).Value = strServer
'write each share name and description to spreadsheet
For Each objShare In objFs
    objworksheet.cells(i, 1).Value = objShare.name
    objWorksheet.cells(i, 2).value = objShare.description
    i = i + 1
Next
'sort the sheet
Set objRange2 = objWorkSheet.Range("A2:B" & i)
Set objRange3 = objWorksheet.Range("A1")
objRange2.Sort objRange3, xlAscending, , , , , , xlYes
'set and connect too second server
strServer = "server2"
set objFs = GetObject("WinNT://" & strServer & "/LanmanServer,FileService")
i = 2
objworksheet.cells(1, 3).Value = strServer
'write each share name and description to spreadsheet
For Each objShare In objFs
    objworksheet.cells(i, 3).Value = objShare.name
    objWorksheet.cells(i, 4).value = objShare.description
    i = i + 1
Next
'sort the sheet
Set objRange2 = objWorkSheet.Range("C2:B" & i)
Set objRange3 = objWorksheet.Range("C1")
objRange2.Sort objRange3, xlAscending, , , , , , xlYes
'autofit cells for readability
Set objRange = objWorkSheet.UsedRange
objRange.EntireColumn.Autofit()
objWorkbook.Save()