Excel VBA Problem 1

[dangash]

Can someone help me, basically im trying to write a macro that will determine the drive information (particularly the amount of storage space left on hard disks) on each computer in our LAN.
This is the code I have written so far:

Sub Storage_Properties()

Dim FileSys, Drv
Dim Row As Integer

Set FileSys = CreateObject("Scripting.FileSystemObject&quot

Row = 1
On Error Resume Next

For Each Drv In FileSys.Drives

Row = Row + 1
Cells(Row, 2) = Drv.Driveletter
Cells(Row, 3) = Drv.IsReady

Select Case Drv.DriveType

Case 0: Cells(Row, 4) = "Unknown"
Case 1: Cells(Row, 4) = "Removable"
Case 2: Cells(Row, 4) = "Fixed"
Case 3: Cells(Row, 4) = "Network"
Case 4: Cells(Row, 4) = "CD-ROM"
Case 5: Cells(Row, 4) = "RAM Disk"

End Select

Cells(Row, 5) = Drv.VolumeName
Cells(Row, 6) = Drv.TotalSize
Cells(Row, 7) = Drv.AvailableSpace

Next Drv 'Moves on to next drive

End Sub

This obtains the information i want, but only for the current computer, does anyone know how i can move to a new network path and scan another computer on the LAN (if that is what i need to do)?

 

[mwolf00]

I'm giving you a star for getting this far. I didn't realize that you could do this. I ran you code and I got info on all drives that I have mapped... Perhaps you need to map every drive on the LAN (argh!!!!), but it does seem like there has to be a better way.

 

[hessrk]

Here is a link to some free tools, called PSTools. You can execute one of these tools to get the information you need for any machine on your network.

http://www.sysinternals.com/ntw2k/freeware/pstools.shtml

 

[mscallisto]

I agree with Mwolf00, this could be very useful if it can be made to see all domain drives. problem is I can't think of a way other than drive mapping which isn't going to work past 26 mappings.

Dangash

I noticed you reference Drv.VolumeName,Drv.TotalSize etc.
What does one find a list of all the Drv. attributes?