Finding UNC path of Excel file

[SunGodly]

I can't seem to find a way to display the UNC path of the active workbook. Is there a UNC companion to activeworkbook.path or fullname?

Thanks!

 

[acron]

Try the following use of the windows API.

Public Declare Function WNetGetConnection Lib "mpr.dll" _
            Alias "WNetGetConnectionA" _
            (ByVal lpszLocalName As String, _
            ByVal lpszRemoteName As String, _
            cbRemoteName As Long) As Long
     
Private Declare Function PathIsUNC Lib "shlwapi" _
            Alias "PathIsUNCA" _
            (ByVal pszPath As String) As Long
     
Function FileUNC(ByVal strPath As String) As String
Dim strNetPath As String
    strNetPath = String(255, Chr(0))
    WNetGetConnection Left(strPath, 2), strNetPath, 255
    If PathIsUNC(strNetPath) Then
        FileUNC = Left(strNetPath, InStr(1, strNetPath, Chr(0)) - 1) & _
                Right(strPath, Len(strPath) - 2)
    Else
         FileUNC = strPath
    End If
End Function

FileUNC(ActiveWorkbook.FullName) should then return the full UNC path to the acive workbook.

A.C.

 

[SunGodly]

Okay, I pasted the code into a new module but it does not appear to work. I am afraid I know nothing about public and private declaration of functions, so I'm guessing I have something wrong. Any additional help would be GREATLY appreciated.

Thanks again!
SunGodly

 

[acron]

Actually if you palce the code in a general module, both can be (should be ?) Public.

How did the code fail ?

The line

FileUNC(ActiveWorkbook.FullName) should then return the full UNC path to the acive workbook.

was just an example, and should be included in a subroutine and assigned to something.

Try and see if following works, together with the code posted earlier. Use it whilst the active workbook is loaded from a Network drive.

Sub Test()
    Dim strUNC as String
    strUNC = FileUNC(ActiveWorkbook.FullName)
    MsgBox strUNC
End Sub

A.C.