show full file paths (UNC) not mapped drive letters

[friel300]

Hi

i am writing a macro for use on different computers.
we have certain drives mapped but because not everyones drives are called up by the same letter i need to use the UNC file path

eg: \\Extauk\nas1\Expert\Enquiries\Enquiries 2012
not this: Z:\Enquiries\Enquiries 2012

is there a way i can accomplish this?

 

[Jerry Sullivan]

You should be able to use the UNC file path within quotes the same way that you would use a file path with a mapped drive.

On my system, my I: drive is mapped to \\data1\ACCT\

Each of these VBA statements will open the same file:

Workbooks.Open Filename:="I:\Accounting\AR\AR Data.xlsx"
Workbooks.Open Filename:="\\data1\ACCT\Accounting\AR\AR Data.xlsx"

Which of these is Z: mapped to on your system?:
\\Extauk\nas1\Expert\Enquiries\
\\Extauk\nas1\Expert\

 

[friel300]

\\Extauk\nas1\Expert\
is mapped to z:

no you misunderstand

the problem isnt that either path dosent work in VBA, they both work fine.
but when selecting a file path using

....FileDialog(msoFileDialogFolderPicker)

for instance. it will output the mapped drive path when i need it to state the UNC one.
so that the resulting file path is uniform for everyone and will work for everryone.

P.s.: sorry for the late reply

 

[Jerry Sullivan]

Ahh...that clarifies things.

I imagine there is a way to read that current mapping and convert it to a UNC.
Before investigating how that would be done, I'm not understanding why you can't use the mapped path.

If a specific user selects a file path using:
FileDialog(msoFileDialogFolderPicker)
then wouldn't whatever path is returned be valid for that specific user during that session?

It would seem you would only need the UNC if that FileDialog(msoFileDialogFolderPicker) returned value was passed to different user or the original user remapped drives between the time they selected the folder and used that variable value.

 

[friel300]

That’s my point this will be used by different people on different computers, and they won’t generate the file path on every use, as the path will be semi permanent. (changed only at the end of the year or ig the folder is moved.)

So if I have my:

\\Extauk\nas1\Expert\
mapped as z:

and I use the mapped name in my macro
then I pass it to a collogue
then his is likely not to be the same, and will not work.
(we could really use some kind of uniform system, but this is the way it is ATM)</SPAN>

 

[Firefly2012]

I haven't used this myself but a quick Google search brought back the following function to identify the UNC path from the mapped drive letter (ie so pass "Z:" into the function):

VBA to Return the UNC(Universal Naming Convention) Path of a Mapped Network Drive

 

[friel300]

Option Explicit

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

Sub kTest()

    MsgBox GETNETWORKPATH("Z:")
    
End Sub

i knew there would be somthing reletivly simple

this returns the value:

\\Extauk\nas1\Expert

for me.

this is exactly what i wanted thanks for all of your help

Dan

 

[Jerry Sullivan]

Dan, The fog has cleared and now I understand why the folder selection is semi-permanent for your use.

Firefly, thanks for finding and sharing that code.

 

[lnchbx13]

Dan, The fog has cleared and now I understand why the folder selection is semi-permanent for your use.

Firefly, thanks for finding and sharing that code.

Will this work in cells that point to other workbooks? Where the cell value is =G:\XXX\XXXX.xls

will it change it to =\\server\XXX\XXXX.xls ?

 

[Jerry Sullivan]

Will this work in cells that point to other workbooks? Where the cell value is =G:\XXX\XXXX.xls

will it change it to =\\server\XXX\XXXX.xls ?

The code won't change cells, but it could be called from a procedure that converts references to external workbooks to use UNC paths instead of mapped drive letters.
References to an external workbook which is closed would look something like this:
='G:\Folder\SubFolder\[MyFile.xls]Sheet1'!$A$2

and could be converted to
='\\Server\Folder\SubFolder\[MyFile.xls]Sheet1'!$A$2

To convert references to a specific drive found in a range you could use....

Sub ReplaceWithUNC()
    Const sDriveLetter As String = "G:"
    Dim sUNC As String

    
    sUNC = GETNETWORKPATH(sDriveLetter)
    If sUNC = "" Then
        MsgBox "Drive letter " & sDriveLetter & " not mapped."
    Else
        With ActiveSheet.Range("A1:A100")  ' or Selection
            .Replace What:=sDriveLetter & "\", Replacement:=sUNC & "\", _
                LookAt:=xlPart, MatchCase:=False
        End With
    End If


End Sub

Two cautions in using that code:
1. It will convert any matching text "G:\" to the UNC, even if it isn't part of a formula reference to that drive.
2. It won't work on references to closed workbooks, since the paths aren't shown the formula is simply =[MyFile.xls]Sheet1'!$A$2

If you want to convert references to a specific drive throughout the entire workbook, another approach would be to remap the workbook links instead of the separate formulas.

Sub ConvertLinksToUNC()
    Dim sUNC As String
    Dim vLinks As Variant
    Dim i As Long

    
    Const sDriveLetter As String = "G:"

    
    sUNC = GETNETWORKPATH(sDriveLetter)

    
    If sUNC = "" Then
        MsgBox "Drive letter " & sDriveLetter & " not mapped."
        Exit Sub
    End If

        
    With ActiveWorkbook
         vLinks = .LinkSources(xlExcelLinks)
         If Not IsEmpty(vLinks) Then
             For i = 1 To UBound(vLinks)
                 .ChangeLink Name:=vLinks(i), _
                    NewName:=Replace(vLinks(i), Find:=sDriveLetter & "\", _
                    Replace:=sUNC & "\", Compare:=vbTextCompare)
             Next i
         End If
    End With

    
End Sub

This has the benefit of only modifying the true links and won't modify matching text isn't a link.
Though this will remap the links for external workbooks that are currently open, I'd recommend closing the linked workbook(s) first.