Determine Network Drives 1

[ktwclark]

Hi All

Since I get a quicker response through here than I do trawling though MSDN, here goes...

I'm looking for a way to determine the drive letters of mapped network drives through Excel VBA. I'm look to automatically save Excel produced reports straight to the correct folders on netwrok drives, but everyone in the office has the network drives mapped to different letters.

Pain!!!

Thanks for the help (again!)

 

[xlbo]

This snippet should get you on your way

Sub LookForAndMapADrive()
Dim fso As New FileSystemObject, wshN As New IWshRuntimeLibrary.iwshnetwork_class
If fso.DriveExists("z&quot = False Then
wshN.mapnetworkdrive "z:", "\\Yorkshire\Shared", True
End If
End Sub

obviously, you'll need to loop thru a list of letters - could use
Dim fso As New FileSystemObject, wshN As New
IWshRuntimeLibrary.iwshnetwork_class

Dim Alphab(25) as variant
Alphab = array("a","b","c","d","e"...etc etc
For i = 0 to 25
If fso.DriveExists(Alphab(i)) = False Then
----Do stuff here
Else
----Do different stuff
End If
Next i
HTH
~Geoff~

 

[ktwclark]

Thanks for the response...It pointed me in the right direction however, I was looking for Excel VBA code, you supplied VB code. Also you didn't mention the Microsoft Scripting Runtime Library is needed in order to use the fso object.

I reworked the code and produced this:

Private Sub MapADrive()
Dim fso As New FileSystemObject
Dim CurrentDrive As Drive
Dim DriveList As String
Dim Counter As Integer
Dim PossibleDrives As Variant

DriveList = ""
PossibleDrives = Array ("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z&quot
For Counter = 0 To 25
If fso.DriveExists(PossibleDrives(Counter)) = True Then
Set CurrentDrive = fso.GetDrive(fso.GetDriveName(PossibleDrives(Counter) & ":&quot)
DriveList = DriveList & CurrentDrive.DriveLetter & ": - "
If CurrentDrive.IsReady = True Then
DriveList = DriveList & CurrentDrive.VolumeName & vbCrLf
Else
If CurrentDrive.DriveType = 1 Then
DriveList = DriveList & "Floppy Drive" & vbCrLf
ElseIf CurrentDrive.DriveType = 4 Then
DriveList = DriveList & "CD ROM Drive" & vbCrLf
End If
End If
End If
Next Counter
MsgBox DriveList
End Sub

 

[xlbo]

The code works fine in VBA as long as the reference is set - apologies for not mentioning - I set the reference ages ago and to be honest, forgot I needed it to run the code
Seems like you got what you wanted tho'
;-) HTH
~Geoff~