GetOpenFilename - Full Network Location

[emmiewoo]

Hi,

I have a macro that returns the file path of a document (see below).

Is it possible to return the full network location rather than the drive letter?

e.g. \\mna5dg\ewok\reports
instead of
K:\reports

Cheers

Sub getfilename()
'This macro is used in the form to get the filename
Dim fn
Range("File_Path").Clear
fn = Application.GetOpenFilename(, , "Find File")
Range("File_Path").Select
ActiveCell = fn
Calculate

End Sub

 

[GlennUK]

Is this any use:

http://support.microsoft.com/kb/160529

?

 

[Andrew Poulsom]

Maybe this will help you (Emily's code in the Comments):

http://www.dailydoseofexcel.com/archives/2006/06/21/unc-path/

 

[Sandeep Warrier]

How about

Private Declare PtrSafe Function WNetGetConnectionA Lib "mpr.dll" (ByVal lpszLocalName As String, ByVal lpszRemoteName As String, cbRemoteName As Long) As Long

Sub GetFileName()

    Dim FilePath As Variant
    Dim FullFilePath As String, PartFilePath As String, Buffer As String
    Dim ReturnValue As Long
    
    FilePath = Application.GetOpenFilename(, , "Find File")
    
    If FilePath <> "False" Then
    
        If Mid$(FilePath, 2, 1) = ":" Then
            DriveLetter = Left$(FilePath, 2)
            PartFilePath = Replace$(FilePath, DriveLetter, "")
            Buffer = String$(256, "^")
            ReturnValue = WNetGetConnectionA(DriveLetter, Buffer, 256)
            FullFilePath = WorksheetFunction.Trim(Left$(Buffer, InStr(Buffer, "^") - 1)) & PartFilePath
        Else
            FullFilePath = FilePath
        End If
        
        MsgBox FullFilePath
        
    End If

End Sub