Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    External Links (XP)

    Upon opening an XL file having external links you are asked whether or not to update the external links. If for some reason the linked file is not present you are able to browse for the source file.

    Is it possible via formula to do a test to see if the file exists prior to establishing the link?

    Thanks,
    John

  2. #2
    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: External Links (XP)

    Could you elaborate on what you are trying to accomplish?

    To do what I think you are asking, you can write a macro to:
    Open the file and set the parameter to NOT update the links
    Then go thru each of the "link sources" and if it exists, update it

    This program would be outside the file with the link, since the question comes before the file is opened. I suppose you could write code in the workbook open to go thru each of the link sources and update as it finds they exist, assuming the user selects NO to update links.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Links (XP)

    Steve,

    Ultimately I am trying to return a value in a rangename (same range) in each of the files.

    As an example I would eventually have 100 files placed in the same directory. I open a control workbook which is linkeds to each file(s); prompted to update the links or not. This works just fine as long as all of the 100 files are in the directory. Files are placed in the directory thoughout the week. I do not control when the files show up. If the file is not there I am prompted to browse for the file. The alternative is to cancel or escape.

    Since the link process works just fine if all of the files are there I thought there may be a way to ignore the browse when the file(s) are not there.

    John

  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: External Links (XP)

    As I said you could:
    NOT update when you open the file
    Then have a macro in the Workbook open event which checked for the existence of each file (Use workbook linksources method to get an array of links, then use DIR to check for the existence)
    Use workbook Updatelink method to update the link
    You could also keep a String variable which kept track of the list of names that did not exist and at the end, use msgbox to display this list.

    I don't have time this weekend to test any code, but if you still need help, post back and I or one of the many "helpers" will try to help you.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Links (XP)

    Steve,

    Thanks for your suggestions. The keyword you mentioned was "workbook linksources". This was enough to point me in the right direction, do a little research and finally write some code to do what I wanted.

    John

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: External Links (XP)

    John
    Reading through this thread, I wondered if an autoexec containing "Application.DisplayAlerts = False" would work. Then on seeing your last post, wondered if you'd care to share your solution.
    Regards
    Don

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Links (XP)

    Don,

    What I am after is the cell contents of various range names in different XL files. As I previously mentioned the external links work great as long as all the files are posted to the directory. I don't control when they are posted so I need something flexible to recognize if the file exits and then creates a new formula (link). The code reads a laundry list of file names and places the rangename cell contents in the offset columns. One thing to note is that the Private Sub Workbook_Open() needs UpdateLinks = xlUpdateLinksNever placed in it to turn-off the link prompt when the control workbook is opened.


    The code is as follows:

    Sub EstablishLink()
    Dim Results As String

    Sheets("Sheet1").Select
    Range("FirstItem").Offset(1, 1).Select 'The range FirstItem is simply a heading for the XL file laundry list
    Do Until IsEmpty(ActiveCell)
    Application.StatusBar = "Establishing Link On: " & ActiveCell
    Results = Dir$(Range("PathOfFile") & ActiveCell & ".xls")
    If Results = "" Then
    ActiveCell.Offset(0, 1) = "File Does Not Exist"
    Else
    ActiveCell.Offset(0, 1).Formula = "=" & ActiveCell & ".xls!SomeRangeName"
    ActiveCell.Offset(0, 3).Formula = "=" & ActiveCell & ".xls!DifferentRangeName"
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
    Range("FirstItem").Offset(1, 1).Select
    Application.StatusBar = False
    MsgBox "Finished"
    End Sub


    I hope the code helps you. This is a great site as Gurus like HansV, sdckapr, Legare Coleman and a host of others provide valuable assistance and recommendations. And one more thing...welcome to the Lounge.

    John

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: External Links (XP)

    Thanks John
    As a self taught VBA user, I'm always looking for interesting code from which to learn; and Woody's lounge is indeed a treasure.
    Regards
    Don

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Links (XP)

    I am encountering strange behavior when I change the range "PathOfFile" to a network drive such as "M:NetworkDirectory".

    The formula being written to the cell is: =M:'M:NetworkDirectoryMyFile.xls'!SomeRangeName The issue is the "M:" (without the quotes)

    I can not determine why the "M:" reference is being written. If I delete the "M:" the formula is valid and the expected value is returned. I completed my testing in a desktop environment pointing to a local drive and everything worked just fine.

    Any suggestions would be appreciated.
    John

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: External Links (XP)

    Oddly enough this portion of the code did not like the variables within the formula section:

    ActiveCell.Offset(0, 1).Formula = "=" & ActiveCell & ".xls!SomeRangeName"
    ActiveCell.Offset(0, 3).Formula = "=" & ActiveCell & ".xls!DifferentRangeName"

    So with some tweaking the following code works:

    oRN01 = "='" & Range("pathoffile") & ActiveCell & ".xls & "'!SomeRangeName"
    oRN02 = "='" & Range("pathoffile") & ActiveCell & ".xls & "'!DifferentRangeName"

    ActiveCell.Offset(0, 1).Formula = oRN01
    ActiveCell.Offset(0, 3).Formula = oRN02



    John

Posting Permissions

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