Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drive Mapping (03)

    Is it possible to use Excel VBA to map a drive? This would be similar to using Windows Explorer and mapping via Tools>Map Network Drive

    Thanks,
    John

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Drive Mapping (03)

    Hi,
    You can do this using the Windows Script Host but the simplest way is probably to run something like:
    <code>shell "cmd.exe /c net use T: ""servernamesharename""", vbMinimizedFocus</code>
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drive Mapping (03)

    Rory,

    Your suggestion worked. One more question. How does one identify the available drive letters that are not mapped?

    Regards,
    John

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Drive Mapping (03)

    I think you would need to use the WSH or some API calls to do that. I will have a look tomorrow if I get time to come up with something, otherwise it is probably worth searching the VBA forum as I think similar topics have come up before.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Drive Mapping (03)

    <P ID="edit" class=small>(Edited by rory on 03-Mar-06 18:27. I have just discovered on further testing that this literally only checks for network mappings. If the drive is local, it will still return False so this may require APIs after all, unless you simply start at Z and work back within a certain degree of comfort!)</P>John,
    The following function will check if a drive letter is mapped: (it can be adapted to return an array of unused letters if required)
    <pre>Function IsMapped(strDrive As String) As Boolean
    Dim objNetwork As Object
    Dim intDrive As Integer, colDrives As Variant
    IsMapped = False
    Set objNetwork = CreateObject("WScript.Network")
    Set colDrives = objNetwork.EnumNetworkDrives()
    strDrive = UCase$(strDrive)
    If Len(strDrive) = 1 Then strDrive = strDrive & ":"
    On Error Resume Next
    For intDrive = 0 To colDrives.Count - 1 Step 2
    If colDrives.Item(intDrive) = strDrive Then IsMapped = True
    Next
    Set colDrives = Nothing
    Set objNetwork = Nothing
    End Function
    </pre>


    You would call it either using <code>IsMapped("T")</code>
    or <code>IsMapped("T:")</code>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drive Mapping (03)

    Rory,

    With a little more surfing and your suggestions I was able to come up with:

    Option Private Module

    Private Declare Function GetDriveType Lib "kernel32" _
    Alias "GetDriveTypeA" (ByVal nDrive As String) As Long

    Function DriveType(DriveLetter As String) As String
    ' Returns a string that describes the type of drive of DriveLetter
    DriveLetter = Left(DriveLetter, 1) & ":"
    Select Case GetDriveType(DriveLetter)
    Case 0: DriveType = "Unknown"
    Case 1: DriveType = "Non-existent"
    Case 2: DriveType = "Removable drive"
    Case 3: DriveType = "Fixed drive"
    Case 4: DriveType = "Network drive"
    Case 5: DriveType = "CD-ROM drive"
    Case 6: DriveType = "RAM disk"
    Case Else: DriveType = "Unknown drive type"
    End Select
    End Function


    Sub ShowAllDrives()
    Dim LetterCode As Long
    Dim Row As Long
    Dim DT As String
    Row = 2
    oSPath = "ServerFolder"

    Range(Cells(Row, 7), Cells(50, 8)).ClearContents
    For LetterCode = 90 To 65 Step -1 ' Z-A
    DT = DriveType(Chr(LetterCode))
    If DT <> "Non-existent" Then
    ' Cells(Row, 7) = Chr(LetterCode) & ":"
    ' Cells(Row, 8) = DT
    ' Row = Row + 1
    Else
    Shell "cmd.exe /c net use " & Chr(LetterCode) & ": """ & oSPath & """", vbMinimizedFocus
    MsgBox "Mapped to Drive: " & Chr(LetterCode)
    End
    End If
    Next LetterCode
    End Sub


    Thanks for your help. You made my day!
    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •