Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Shared Drives (2000)

    I have a problem: Whenever I save a file to a common or shared drive (the "U" drive), and another party that also has access to that drive retrieves the file, all of the embedded links that were from th U drive in the file I had saved now appear as though they are located on the retrieving party's "Y" or Outlook drive. This can really cause some problems-updating doesn't seem to help either-we are forced to change every link manually.
    Any help?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared Drives (2000)

    Is it possible that when your computer had the shared drive mapped it was called "U" and on the other person's computer the same drive was mapped using "Y"?
    Also, instead of using the drive letter I think you can use the drive name. Our file like that is located on our server and it's called "Server", the share file is called "Shared Data" and any link in an Excel cell that refers to something in there looks like ='Servershared data[filename.xls]Sheet1'!A1
    I don't know how much help this is but hopefully it's a start.

    Stats

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

    Re: Shared Drives (2000)

    I suspect that Stats has described your problem.

    You should never use mapped drive letters in either Links of VBA code as other useres may have their network drives mapped differently. It is even possible that your own mappings might change.

    I think the best tip is that if you are going to create links to a file while it is open, you should first have opened the file directly from the network rather than from your mapped drive. To do this, in the file open dialog, navigate to Network Neighborhood, find the appropraite Server and Volume and open the file from there. Excel should then return the full UNC path in any links that are created.

    To deal with your currrent problem try the following.

    With both the Workbook with links and the workbook that the links refer open, select the latter and do FileSaveAs and navigate to Network Neighborhood and save the file to the appropriate ServerVolumeFolder . . . path.

    The following function should return the UNC path fro a give Drive letter :

    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

    In your case if you use [b]strPath = FileUNC(

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Shared Drives (2000)

    Thanks to Stats and Andrew.

    Andrew, is there a macro that I could put in my personal.xls workbook that I could run that would convert the links from the "U:" to their proper link? That way, it would save me a lot of time of having to go into literally hundreds of files and doing a global search and replace.
    Thanks,
    Jeff

  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: Shared Drives (2000)

    Firstly, sorry for leaving out some important declarations in that code I posted above. See below.

    The following code should do what you want, provided that U: is not used in any other connection. The code works on all sheets in the active workbook, replacing U: with the fully qualified network path.

    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

    Sub AddUNC()
    Dim xlCalc As Integer
    Dim ws As Worksheet
    strDrive = "U:" ' <-- This the the Drive that Network path is mapped to
    With Application
    xlCalc = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    End With
    For Each ws In ActiveWorkbook.Worksheets
    ws.Cells.Replace What:=strDrive, _
    Replacement:=FileUNC(strDrive)
    Next ws
    With Application
    .Calculation = xlCalc
    .ScreenUpdating = True
    End With
    End Sub

    Hope it helps.

    Andrew C

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Shared Drives (2000)

    Andrew,
    Thanks. However, I am a little confused-what do you mean by: "The following code should do what you want, provided that U: is not used in any other connection."
    Also, I assume I insert this in a new Module in my Personal.xls file-right?
    Thanks,
    Jeff

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

    Re: Shared Drives (2000)

    Yes, i'd suggest a new module as the declarations must go at the top of the module.

    < provided that U: is not used in any other connection >, was a warning that all instances off U: will be replaced, and perhaps a better word to use was context instead of connection.

    For example If you have a formula referring to colum U (U:U) you would have problems as it would altered and cause you much problems.

    Andrew C

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Shared Drives (2000)

    Works great!
    Thanks,
    Jeff

Posting Permissions

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