CODEWITHSUNDEEP
vbaexcelvbscript
D_Bester
D_Bester

Reputation: 5931

"Disconnected Network Drive" in My Computer

We use VPN to connect to the work network. I have a mapped network drive that shows "Disconnected Network Drive" in My Computer immediately after I connect. While in this state I run a VBA routine:

Dim dr As String: dr = "F:\T\"
Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(dr) Then
    Call fso.CreateFolder(dr)
End If

Even though the folder actually does exist, fso thinks it doesn't so it tries to create the folder and throws an error: Run-time error 76: Path not found. Of course it can't find it because it is in a disconnected state. Any other operations using that network drive will fail too.

So I open My Computer and manually double-click the network drive. It connects just fine and I no longer get the error when running the code again. But this is problematic. I need a solution in the code to automatically connect the network drive.

Possible solution 1: Just use the UNC path instead of mapped drive letter. The problem with that is that I don't know what the path is. Nor do I want to use UNC; the code needs to be flexible to work with wherever the drive maps to. It's policy from the brass.

Possible solution 2: Re-map the network drive using Net Use or WshNetwork. Again, this isn't really possible as I don't know what the UNC path is or will be.

Possible solution 3: Connect using ShellExecute While this would work, it isn't really palatable as it opens a window to explore the folder (could use SW_HIDE to avoid showing the window, but then how would I close it?). Furthermore it doesn't connect instantly. I would need a delay of uncertain length to make this work. Not a good answer. MSDN ShellExecute Function

So is there a better solution? How do I connect the drive while waiting for it to return and throwing an error if not able to connect?

I need it to work for both VBScript and VBA.

Upvotes: 1

Views: 2527

Answers (1)

D_Bester
D_Bester

Reputation: 5931

Slugster's suggestion to get the mapped UNC value is a good one. Here is my solution as adapted from this answer This works for VBScript and VBA.

Sub testget()
    Debug.Print getUNC("F:\T\")
End Sub

Function getUNC(dir)

    Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
    Dim sDrive: sDrive = fso.GetDriveName(dir)
    Set fso = Nothing

    Dim sMap: sMap = GetMappedDrive(sDrive)
    If sMap <> "" And sDrive <> sMap Then
        getUNC = Replace(dir, sDrive, sMap)
    Else
        getUNC = dir
    End If

End Function

Function GetMappedDrive(sDrive)
    Dim wshNetwork: Set wshNetwork = CreateObject("WScript.Network")
    Dim oDrives: Set oDrives = wshNetwork.EnumNetworkDrives
    Dim i
    For i = 0 To oDrives.Count - 1 Step 2
        If UCase(oDrives.Item(i)) = UCase(sDrive) Then
            GetMappedDrive = oDrives.Item(i + 1)
            Exit For
        End If
    Next
    Set oDrives = Nothing
    Set wshNetwork = Nothing
End Function

Upvotes: 1

Related Questions