Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Location
    Morris, Minnesota, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy worksheet with links hangs PC (Excel 2003 SP1)

    Here's the scenario: we have a price book spreadsheet from which we copy worksheets to create a new quote for our customers, which we then e-mail to them. Each of the files has many links to 2 network files that contain much of the underlying information. This has worked very well since March 1, 2005.

    About 2 weeks ago, we noticed that the copy/move operation would hang for as much as 3 minutes (prior to that, it would copy in 1-5 seconds). Our IT department claims that they didn't make any changes to the file server or network around that time and they don't know what could be causing this delay. They're also swamped with a couple other high priority projects right now and don't have the resources to look at it right away, so we're really getting frustrated.

    Here's a few things that I've uncovered while looking into it:

    1) There is no process in Task Manager that is tying up the CPU at all, and the memory allocations do not spike during this time.
    2) It doesn't make a difference whether I "copy" or "move" the worksheet.
    3) If I disconnect from the network prior to copying the worksheet, it happens without any delay whatsoever.

    Which means to me that I have what I believe is a network problem, but I can't figure out what it is or where to look. I don't think it would help to attach a spreadsheet, because anyone who does this off our network has no problems whatsoever.

    Any ideas?

    Jeff

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

    Re: Copy worksheet with links hangs PC (Excel 2003 SP1)

    I guess the wisest thing to do is to copy, paste special values, paste special formats all cells of the worksheet to an empty worksheet in a new workbook. That way, your clients will also not be bothered with updat elinks questions.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Location
    Morris, Minnesota, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy worksheet with links hangs PC (Excel 2003 SP1)

    Your suggestion will definitely work; however, it does not help me identify the underlying problem that's created the concern. I don't need a workaround, I just need it work like it used to. Also, clients do not get the question because we have chosen "Don't display the alert and don't update automatic links" for the Link startup prompt.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy worksheet with links hangs PC (Excel 2003 SP1)

    You state yourself that it's probably a network problem, and that your IT department doesn't have time to look into it. I don't think it's reasonable to expect the Lounge to solve that. A workaround is the next best thing, and that's what Jan Karel Pieterse offered.

  5. #5
    New Lounger
    Join Date
    Oct 2003
    Location
    Morris, Minnesota, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy worksheet with links hangs PC (Excel 2003 SP1)

    Point well taken. Sorry if I sounded grumpy.

    The reason I don't really want to paste values and formatting is because that will eliminate the interactive ability to change options and update language and pricing, using check boxes and linked cells within the worksheet.

    The workaround we're currently using is simply to unplug the network cord when we want to copy. It's a pain, but not nearly as much of a pain as a PC that's locked up for 3 minutes.

    By the way, even if I break all of the links before copying the worksheet, it will still hang for approximately 3 minutes.

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

    Re: Copy worksheet with links hangs PC (Excel 2003 SP1)

    Breaking the links might not help if the source file is also saved on a network drive. Once you copy out one worksheet of the source document, you might create links from the copy back to the source.

    You could try copying all worksheets to a new file and then removing the ones you don't want.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    New Lounger
    Join Date
    Oct 2003
    Location
    Morris, Minnesota, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy worksheet with links hangs PC (Excel 2003 SP1)

    Turns out we can eliminate the problem by turning off the file server antivirus protection. Still don't know what changed 2 weeks ago to create the problem, but we've at least identified where to look. I don't know what protection we use for the servers, but we've got Computer Associates anti-virus on our PCs.

    Thanks for the thoughts and suggestions.

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

    Re: Copy worksheet with links hangs PC (Excel 2003 SP1)

    In that case, modiying the server's AV protection to exclude Office files (or setting them less strict) may help.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    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: Copy worksheet with links hangs PC (Excel 2003 SP1)

    You might be able to solve it by changing the AV settings and not have to disable the AV protection.

    Steve

Posting Permissions

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