CODEWITHSUNDEEP
excelvba
JoaMika
JoaMika

Reputation: 1827

Thisworkbook.path - network location

Using this code whilst the spreadsheet is stored on dropbox returns:

Sub output_set()
Dim s As String 
s = ThisWorkbook.Path
outputstr = s & "\output\"
end sub

RESULT 

\\psf\Dropbox\test_folder\output\

I would prefer to return the drive letter assigned to this location instead.

W:\test_folder\output\

How can i modify my macro?

Upvotes: 1

Views: 683

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Sub Tester()

    Debug.Print MappedDrivePath("\\psf\Dropbox\test_folder\output\")

End Sub


Function UncToMappedDrive(uncPath) As String
    Dim rv As String
    Dim objWMI As Object
    Dim disks As Object
    Dim disk As Object

    rv = ""

    Set objWMI = GetObject("winmgmts:" & _
           "{impersonationLevel=impersonate}!\\.\root\cimv2")
    Set disks = objWMI.ExecQuery("Select * from Win32_MappedLogicalDisk")

    For Each disk In disks
        If uncPath Like disk.ProviderName & "*" Then
            rv = Replace(uncPath, disk.ProviderName, disk.Name)
            Exit For
        End If
    Next disk

    UncToMappedDrive = rv
End Function

Upvotes: 2

Related Questions