Results 1 to 6 of 6
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    UNC Address of a file (Excel97)

    Is there a way to obtain the Universal Naming convention (UNC) address of file using VB?
    Using "fullname" gives the mapped drive which may be different between network users as not all the drives are mapped identically and can be different on even your own computer!

    I want to compare the "fullname" versus a standard name, but it can vary depending on how the user mapped the drives, how he opened the file, what the shortcut list was, etc, etc unless the UNC is included.

    In NT you were only allowed to map a drive using server and share names:
    U: mapped as = servershare
    Fullname = U:divisionsubdivisongroupsubgroupfilename.xls

    But now you can add the paths also to the mapping:
    U: mapped as = serversharedivisionsubdivision
    Fullname = U:groupsubgroupfilename.xls

    and you can also create:
    G: mapped as = serversharedivisionsubdivisiongroupsubgroup

    If you open the G-map and open the file the fullname is:
    G:filename.xls

    BUT if you open the identical file but use explorer to wade through the U drive the fullname is:
    Fullname = U:groupsubgroupfilename.xls

    Steve

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UNC Address of a file (Excel97)

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: UNC Address of a file (Excel97)

    Thank you for the information. I was mildly surprised at how convoluted the method is, but then I remembered that we are dealing with microsoft. I checked it out and it seems to work.

    Again, thank you,

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: UNC Address of a file (Excel97)

    Thanks for you effort.

    I noticed one error:
    UNCName = Left(strNetPath, InStr(1, strNetPath, Chr(0)) - 1) & _
    Right(strPath, Len(strPath) - 2)

    Should be:
    FileUNC = Left(strNetPath, InStr(1, strNetPath, Chr(0)) - 1) & _
    Right(strPath, Len(strPath) - 2)

    otherwise you function yields a null string since it is not assigned.

    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UNC Address of a file (Excel97)

    Oops, you are correct., sorry about that.

    I just amended a function I had that returned just the portion that represented the Mapped drive, and for got to change that line.

    I will amend the original.

    Andrew C

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UNC Address of a file (Excel97)

    This might be slightly less convoluted and can return the full UNC name for a file :-

    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

    Use the following little routine to test

    Sub Test()
    MsgBox FileUNC(ActiveWorkbook.FullName)
    End Sub

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •