Local Drive Links

[Eric Golf]

Hi All,

I have a workbook on a shared drive that several people access at different times and that contains links to other workbooks. The problem is that Excel converts file paths from full path to local drive letters on saving. Not everyone uses the same drive letter or even assigns the same folder level to a drive, which then means the link breaks for others.

e.g. \\Server\MainFolder\FolderA\FolderB\... can become Z:\FolderA\FolderB\... for one person and Z:\FolderB\... for another, or even a different drive letter.

Is there a way of forcing the Workbook, or Excel, to maintain links as full file paths?

Thanks in advance,

Eric.

 

[PoeticRobot]

The below code looks at the activeworkbook.path to find the mapped network drive, then uses a function to bring back the UNC path for that drive.

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

Public Sub UNCPath()

    
Dim strDrive As String
Dim strActiveWorkbookPath As String

strDrive = Left(ActiveWorkbook.Path, 2)

If Mid(strDrive, 2, 1) = ":" Then
    strActiveWorkbookPath = Replace(ActiveWorkbook.Path, strDrive, GetNetworkPath(strDrive))
End If

MsgBox strActiveWorkbookPath

End Sub

 

[Eric Golf]

Hi PoeticRobot,

Thanks for answering, unfortunately not really what I am looking for.

I know the paths, what I want is to ensure Excel uses the full path in the links, as seen under Tools | Edit Links.

As far as I can tell when it uses the short (local) path it can go wrong.

Thanks again,

Eric.