How to get a UNC path from Application.Path?

Question

I want to get the path of the active workbook in vba code. ActiveWorkbook.Path does this

BUT

I need it to retrieve something like this:

\\MachineName\ShareFolder\ETC\ETC2

NOT:

S:\ETC\ETC2

Where S: maps to \\MachineName\ShareFolder\. How would I do that?

Answer 1

    Dim Drive As String
    Drive = Left(ActiveWorkbook.Path, 2)

    ActiveWorkbookPath = Replace(ActiveWorkbook.Path, Drive, GetNetworkPath(Drive))


Function GetNetworkPath(ByVal DriveName As String) As String

    Dim objNtWork  As Object
    Dim objDrives  As Object
    Dim lngLoop    As Long


    Set objNtWork = CreateObject("WScript.Network")
    Set objDrives = objNtWork.enumnetworkdrives

    For lngLoop = 0 To objDrives.Count - 1 Step 2
        If UCase(objDrives.Item(lngLoop)) = UCase(DriveName) Then
            GetNetworkPath = objDrives.Item(lngLoop + 1)
            Exit For
        End If
    Next

End Function

Answer 2

Before trying to convert the filepath or any junk like that, try out a couple of the other properties that the Workbook object offers. I personally use ActiveWorkbook.FullName with all of my projects (which are hosted on network drives) and I have never had an issue.

That said, if this approach doesn't work then there are certainly ways of converting the filepath. While I prefer going through the properties of objects first (they tend to be more reliable), I am not resistant to using a function to solve the problem. That's where checking out this article: https://pagecommunication.co.uk/2014/07/15/vba-to-convert-a-mapped-drive-letter-to-unc-path/ and checking this answer Convert Shared folder path to UNC path might help. The only difference between both is that the former requires a reference to Microsoft.Scripting.Runtime, the other does not.