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?

 

[Firefly2012]

Though this will remap the links for external workbooks that are currently open, I'd recommend closing the linked workbook(s) first.

Hi Jerry - why would you recommend that?

 

[Jerry Sullivan]

Hi Jerry - why would you recommend that?

Hi Firefly,

The remapping of the links should be the same regardless of whether the linked workbook is open or closed; however if the linked workbook was opened using the mapped drive path instead of the UNC, then the references will be changed to no longer be pointing to an open workbook in the Workbooks collection.

Using the earlier example, this reference
=[MyFile.xls]Sheet1'!$A$2

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

...even though the link is still pointing to the same file.

The effect is that a change made to the value Sheet1'!$A$2 is no longer immediately updated in the cell with that remapped link. Those references will be treated as if they were made to a closed workbook. The user would need to save MyFile.xls then update values to see the changes.

If the user understands this, then it is no worse that if he or she had first closed the linked workbook; however if they don't understand it, then it could cause some confusion or errors.

If the linked workbook had been opened using the UNC path, then the opposite occurs. The references change from being external references to a "closed" workbook to being live links to an open workbook in the Workbooks collection.

 

[Jerry Sullivan]

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

I just noticed a typo in my earlier post. I meant to write "It won't work on references to open workbooks..."

 

[Firefly2012]

Thanks for the explain

 

[sricc]

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

This function is perfect except it does not work on some PC's using Windows 7 and Office 2013. Are there limitations based on Windows/Office versions?

Thanks,

Sam

 

[friel300]

Hey Sam, OP here. Based on the dates of the Original post Windows 7 and Office13 would have been my setup at the time. This works with mapped drives only and returns nothing if you give the function a drive letter that isn't a mapped drive.