Getting a Workbooks path on a network (VBA)

[ChrisOswald]

Running this code snippet with a workbook saved on a network drive:

StrThisSavePath = ActiveWorkbook.Fullname
debug.print StrThisSavePath

Returns something like

x:\Dist Doc Stg\Saved reports\...\MyDoc.xls
or
E:\Dist Doc Stg\Saved reports\...\MyDoc.xls

depending on how the network drive is mapped.

How would I get it the domain type of drive name thing?

i.e.

\\corpshare.mycompany.com\GRPS\Institutional\Dist Doc Stg\Saved reports\...\MyDoc.xls

Thanks,
Chris

ps. what is the correct way to say "domain type of drive name thing" ? I'd probably have had better luck with an internet search if I knew.

 

[shg]

Uniform Naming Convention

Function Path2UNC(sFullName As String) As String
    ' Converts the mapped drive path in sFullName to a UNC path if one exists.
    ' If not, returns a null string
 
    Dim sDrive      As String
    Dim i           As Long
 
    sDrive = UCase(Left(sFullName, 2))
 
    With CreateObject("WScript.Network").EnumNetworkDrives
        For i = 0 To .Count - 1 Step 2
            If .Item(i) = sDrive Then
                Path2UNC = .Item(i + 1) & Mid(sFullName, 3)
                Exit For
            End If
        Next
    End With
End Function

 

[ChrisOswald]

Works a treat. Thanks.