Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Edit links manually only

    Another "opportunity"... I have another file in which I am linking to several different Excel documents coming from Sharepoint. I'd like to be able to only request "manual" updates as opposed to "automatic". The "manual" button under "Data" then "Edit Links" is grayed out... How can I make this a "manual" update so I only update when I want to as opposed to "automatic" updates?

    Thanks!

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Try Data Tab> Connections> select the workbook> Properties> uncheck "Refresh every XX minutes" See if that solves your problem.

    Maud

  3. #3
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maud, this doesn't work. It only shows one "connection" and that is to the Sharepoint list and I have all of the stuff unchecked. The other files this destination file is linking to are all "Excel" files and not lists. They update the minute a change is made to them... I'd like to only get updates manually.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts

  5. #5
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maudibe, this article dances all around the issue but I don't see how to "manually" control updates to links in Sharepoint coming from both lists and plain old Excel files.

    All other suggestions are welcome!

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    In Excel 2013, 2010 and 2007, if you click
    File>Options
    Then select >Advanced
    Then scroll down to General, there is a checkbox which you can set "Ask to update automatic links"

    perhaps this may help you?

    zeddy

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Zeddy,

    According to MS website, "When the Ask to update automatic links check box is cleared, the links are automatically updated. Additionally, no message appears".
    http://support.microsoft.com/kb/826921

    Mitch, if that does not work, try: Excel Options> Trust Center> Trust Center Settings> External Content> Disable automatic update of workbook links. There is also some good information here: http://office.microsoft.com/en-us/ex...010102348.aspx

    HTH,
    Maud
    Last edited by Maudibe; 2013-03-05 at 19:59.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Maudibe

    Yes, by default the checkbox is cleared. So links are updated automatically by default. and no message appears.
    So I was saying to set this checkbox, i.e. click it to put a tick in it, and then, as mitch wanted, it will ask first, and so you can then 'manually' decide if you want to update or not.

    zeddy

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Gotcha!

  10. #10
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    huh.. these all appear to be related to the specific user, these options. Is there a way I can turn off a spreadsheet (for all users) as of a certain point in time that it will no longer update or do I have copy paste specials values on all worksheets???

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Mitch

    ..you could add this line of code to the Workbook Open event:

    Application.AskToUpdateLinks = True

    ..and remember to save the workbook as a macro-enabled file if you are using Excel versions 2007 onwards.

    zeddy

  12. #12
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Zeddy, this is a Sharepoint set of links, from file to file. Some appear to be dynamic when the files are open so they don't have to necessarily refresh on open, or so it appears... For instance, I can have both files open and this update happens realtime... and doesn't wait until the file is opened to update.

    I'd like to be able to turn off all updates, period and this appears to be more of a problem than its worth...

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Mitch

    Here are the rules, as far as I am aware:

    Assuming you have an excel fileA, that contains links to other excel workbooks, e.g. fileB and fileC

    1. Whenever you load fileA, if any of the source link files are already open, then linked data will be updated automatically whether you like it or not (unless the calcs have been set to manual in fileA).
    So, if you have a link to any file and that file is open, you cannot turn off updates from that source file (other than by turning calcs to manual, not recommended)
    Because that is what links are for!

    2. Whenever you load fileA, if any of the source link files are closed (i.e. not open), then you have these choices:
    a) fileA can open and will grab the latest values from the closed source files automatically without asking
    b) fileA can open and will NOT grab the latest values from any closed file (will show last values as when fileA was saved)
    c) fileA can open and ask you whether to grab the lastest values or not from the closed source files

    Note that if choice 2(b) is selected, whenever a source file is then subsequently opened, the links will then be updated as per 1.

    To set the choice:
    Open fileA
    In the top-panel ribbon, select Data>Edit Links

    Forget the greyed-out Manual, click the button [Startup Prompt] underneath it
    Now click your choice e.g. the second option button: "Don't display the alert and don't update automatic links"

    Personally, the way I would do it is to avoid links altogether, and use vba to grab data 'on demand' from specified source files, importing the data as values only.
    Typically, I would add my own [Refresh] button(s) as required, and assign the relevant 'grab latest data' routines to these.
    With linked files on networks, many times I've seen it take ages to load an excel file because of remote links etc.

    zeddy

  14. The Following User Says Thank You to zeddy For This Useful Post:

    ruosChalet (2013-03-17)

  15. #14
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Zeddy, thanks... for whatever reason the Startup Prompt is also grayed out. I don't know VBA so that grab data option is not available for me.

    Is there a VBA macro out there I can use to only get data as I want it?

  16. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Mitch

    I suspect you have opened the excel file directly in sharepoint, so are using the sharepoint 'web browser version' of excel, as compared with starting a full local Excel program copy.
    And no, I'm afraid there is no generic vba macro I know of that will give you what you are asking for.

    zeddy

Posting Permissions

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