Results 1 to 5 of 5
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Data Link refresh (2002 SP2)

    Following Hans V suggestion I created links to another workbook using the GETPIVOTDATA command. All was OK. Today I opened the workbook, there was no box asking to update links, (which I found a bit strange), and I cannot get the data to refresh. Tried re-entering the formula still no upate. went to the links menu and clicked update, nothing happened. Is there some hidden link or whatever that has been corrupted, or am I missing something obvious to get this to update?
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Link refresh (2002 SP2)

    Does pressing F9 work by any chance to update your links?
    You might consider going to TOOLS | OPTIONS, and click the calculation tab and check if Automatic is selected. Links update automatically if automatic is selected!
    If this is not the case, do your formulas still contain a reference to the external workbooks?
    Regards,
    Rudi

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Data Link refresh (2002 SP2)

    Hi Paul,
    I don't believe that GETPIVOTDATA will work if it references a closed workbook. Does your formula update if you open the referenced workbook?
    As regards not getting the Update Links prompt, if you look under Tools - Options on the Edit tab, is the Ask to update automatic links box checked? (it should be)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Data Link refresh (2002 SP2)

    Thanks for your reply Rudi. F9 made no difference, and automatic was turned on, and the refernce text was correct. Tried dtect and repair no joy. Rebuilt the model now works fine. Put it dowon to one of the many Excel great unknowns.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Data Link refresh (2002 SP2)

    Hi Rory, Yes the referenced file was open, Tried the usual, save and re-open, then open before and after the report model, but no joy. Update automatically under options was ticked so I knew that was not the cause. Tried detect and repair, but that did not help either. Ended up re-creating anew the report model, which now works fine. Put it down to one of the great Excel unknowns I suppose. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

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