Results 1 to 9 of 9

Thread: Links

  1. #1
    BobG
    Guest

    Links

    I moved a sheet from one file to another. Now Excel says that there is a link to another file. However, Edit, Links only shows me what file I am linking to, not which cell has the link. Searching for the file name, or even just "*.xls" doesn't find anything. I would like to find which cell is creating this link so that I can remove it.

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links

    A common problem when moving or copying worksheets is that the defined names get carried along. Check your copied sheet for defined names that have references pointing back to the original workbook. Delete those names, and your 'Links' should go away.

  3. #3
    BobG
    Guest

    Re: Links

    When I check the Names, Define command, the only name I see is the "Print_Area". Is there somewhere else I should be looking?

  4. #4
    ellisbill
    Guest

    Re: Links

    If you are sure that the required data exists in the 'new' workbook, you can eliminate the link by selecting the 'new' workbook name as the 'new' source (i.e. link it to itself) under Edit/Links.
    Make sure you are working on an unprotected sheet.

  5. #5
    BobG
    Guest

    Re: Links

    I tried to change the source file, but I just get a large series of errors. It doesn't remove the links, and I still can't find the source. It tells me that I have an invalid reference to an external file.[img]/w3timages/icons/yikes.gif[/img]

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links

    If the link is in a cell, you can use VBA to find the link

    The following is quick and dirty. There are ways of finding the max rows and cells in a sheet, I'm just plugging in values I know for the worksheet I'm woring on

    ** This is rough! I know it can be polished up heaps (eg, "for each cell in ..." but I'm in Quick & dirty mode here **

    dim iRow as integer
    dim iCol as integer

    for iRow = 1 to 100
    for iCol = 1 to 50

    ' Print out references for any formula which refers to another workbook.
    if instr(activesheet.cells(irow, icol).formula,"!") > 0 then
    debug.print i j activesheet.cells(irow,icol).formula
    next iCol
    next iRow


    If you can find external references there- great. But (as aleady pointed out) there can be references in named ranges and in charts- probably in other places as well. But the code may help you to find some answers anyway
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Links

    I encountered the same problem. In my case, I was unable to re-link the file to itself because some of the sheet names were different in the new file. My get-around was to add in temporary sheets with the same sheetnames as those in the file from which I copied. This should avoid the error messages when re-linking the file to itself. I then deleted the temporary sheets.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links

    1. Make sure that the "Print Area" reference is not pointing back to the original workbook.

    2. You have to do the Name / Define routine on each worksheet since it's possible the name is defined at the worksheet level and will only show up when that sheet is active.

    3. All the other advice you've been getting is valid -- links can be in cell references, chart references, and name references, so you have to check them all. Using the Find command with a search argument that consists of a portion of the external reference name is the best way to do a quick check on the cells. Search for something like ".xls]" without the quotes. You need to do this on each worksheet since Find does not work reliably on multi-sheet selections depending on which Excel release you're using.

  9. #9
    BobG
    Guest

    Re: Links

    AnneD: Thank you .... I copied all of the sheets from the linked files, then closed them. Then I re-pointed all links back to the open file and that seemed to clear them. Now I just have to review things to make sure they still work! There should be something on the Links menu to identify the cells with the links!

    GeneO: I tried that .... didn't find any external references. Thanks for the tip.

Posting Permissions

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