Results 1 to 6 of 6
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    recreate WB w/o external links (xl97/xl2000)

    I have a 34MB workbook that I am rebuilding (due to other errors) and I find that when I either copy or move the sheets to a new workbook, I get thousands of external links. I've re-read all the threads on this list about external links and removing them. I have used the suggested programs and they do not totally solve the problem. (I've even sent email to Bill Manville who is the author of one of the FindLinks programs and he's responded nicely. He's never run across a workbook with as many links as I had plus I got some errors from his program).

    My question though is HOW do I make an exact copy of this one workbook without also getting stuck with all these external links that are referencing the old copy? I open the source workbook then create a new destination workbook in Excel. I drag/drop the sheets from old to new (or group multiple sheets) but this creates links back to the original workbook. If I write VBA to do the copy for me will that prevent these $^@*$# links from showing up? I know how to write the code so don't need help there. It's just so frustrating to have wasted an entire day doing something that should be so simple.

    Thnx, Deb <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recreate WB w/o external links (xl97/xl2000)

    I don't quite understand whether your goal is to maintain the links or get rid of them entirely in the new workbook.

    Have you tried doing it with the linked workbooks open?

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recreate WB w/o external links (xl97/xl2000)

    The goal is not to have any links that reference an external workbook (the old or source workbook). If a cell used to reference $C$51 then I still want it to reference $C$51 not "D:somePathsomeFoldermyoldworkbook.xls!SheetDeb!$C $51" (whatever the full path syntax is). The point being that when I try to make a copy of the workbook by move/copy the sheets into a new workbook, the name of the source workbook gets stuck in the new workbook.

    I need a way to create an exact duplicate of the old workbook like SaveAs but that doesn't get rid of the interal garbage and it still remembers the original/source workbook.

    I'm giving up on this workbook after spending 1-1/2 days rebuilding it several times and using various find link programs none of which worked totally. It's a huge 33 MB workbook with 12,200 links (that's what those find link programs told me).

    Thanks all for your suggestions.
    Deb <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

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

    Re: recreate WB w/o external links (xl97/xl2000)

    For cell formulas, you should be able to do just a replace. Find:
    D:somePathsomeFoldermyoldworkbook.xls!

    and replace with ""

    It's probably not going to help with graphs or things
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recreate WB w/o external links (xl97/xl2000)

    You could try these steps:

    - open an empty workbook
    - make sure it gets the same sheetnames as your master file
    - now -sheet-by-sheet- copy all cells (NOT THE SHEETS THEMSELVES!) to their corresponding new sheet, by first selecting them all (click the small rectangle to the left of column A, above row 1), then copying, then going to the new wkbk and pasting.

    If a sheet contains references to other sheets, you'll get external links again, but that can easily be overcome:

    - select Edit, links
    - click on a link and select change source
    - browse to the file you currently have opened (i.e. the workbook that *contains* the links), that way you change the links to the workbook itself, effectively removing them. Note that you have to have saved the workbook first (otherwise there is no file to browse to...)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recreate WB w/o external links (xl97/xl2000)

    Good. Because my way should work for you:

    File-Save as to a different name. Hit Edit-Links and change all the external links to the original file's name. When you're all done, save the file, close it, and rename it to the original name.

Posting Permissions

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