Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Amsterdam, the Netherlands
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update, don't Update

    On my Windows 7 PC with Excel 2010 I am using API to
    get information in a few cells in my spreadsheet.
    That works fine, no problems but:

    When I start, Excel asks me to Update or Don't Update.

    I like to update only occasionally since I am working
    on my sheets and while doing that I don't like to
    wait looking at the blue rotating circle that indicates
    that the workbook is being updated so I select Don't Update.

    Then after a while, I like to update.
    Is there another way of initiating this ? a litle macro
    under a butom would be nice.

    Or is the only way to close/save and restart Excel and then
    select Update, then close/save and restart to get back
    without updates.

    A suggestion would be appreciated.

    regards,

  2. #2
    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
    You can use:
    Data - Edit links
    then Select all the links and press [Update Values]

    Steve

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Amsterdam, the Netherlands
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried that but it does not work.
    Its the sequence which I don't know I guess.

  4. #4
    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
    What do you mean by "does not work"?

    Steve

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Amsterdam, the Netherlands
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    the API is with two brokers (IB and TOS) from which my spreadsheet retrieves data.
    Portfolio data and stock quotes. It requires a login etc...
    The "edit Links" has its sources alfabetically which is definately not the right sequence.
    By does not work I mean that the cells are not updated.
    cells like this: =abcde|err!errorMsg
    or like this: =TOS|LAST!'.RUT110820c940'

    thanks for helping me.

  6. #6
    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
    Have you opened the file and confirmed the values saved to the files are not the current values in the open workbook?

    Steve

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Amsterdam, the Netherlands
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    there are no files other than the workbook.
    Cells show N/A also when I update as per your suggestion.

  8. #8
    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
    If the linked files do not exist or the workbook can not find them, when you try to update, it has not values so you get #NA errors. This results seems expected to me and I would describe the update as "working" as expected. Are you trying to break the links to keep the current values without updating?

    If so then:
    Data - Edit links
    then Select all the links and press [Break Link]
    Then press [Break Links] again to confirm

    Steve

  9. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Amsterdam, the Netherlands
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When I do that, the cell formula (e.g. TOS|LAST!'.RUT110820c940') is replaced
    by its value. When I press Don't Update, the cell formula is preserved, thats what I want.
    I want to update later, when I break the links I can never update since the formula's
    are gone.
    The values come from a running program (called TOS) not from a file.

  10. #10
    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
    That was my original reply. WHEN you want to update press
    Data - Edit links
    then Select all the links and press [Update Values]

    But I thought you said the files were not available, which is why breaking the links is the option if the linked files don't exist to preserve the values without getting the errors.

    Perhaps I don't completely understand your situation. Could you elaborate?
    Steve

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    Amsterdam, the Netherlands
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There are two programs "abcde" and TOS running on my computer.
    Then there is only one workbook.
    In some cells are links to these programs:
    cells like this: =abcde|err!errorMsg
    or like this: =TOS|LAST!'.RUT110820c940'

    when I start the workbook I get the choice to update or not to update.
    when I select update I am being interrupted every minute since then the
    workbook is updating again which takes several seconds.
    When I don't select update nothing is happening but I have to save and close, then reopen and select update to have an update and then save and close and reopen to get back to the no-update situation.

    there are macros or program modules running in the workbook continuesly when I select update, how can I dig into that to stop the updating.

  12. #12
    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
    It seems to me that you will have to goto VBA (Alt-F11), open the project and look at the code and see where the code works with updating. It may the programs abcde and TOS which do it and in that case you will have to talk with the writers of that code. If it is done in Excel VBA and you have access to it, we can help here, if not you will have to go to a site that helps coding in those programs

    Steve

  13. #13
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Just curious - Would the MSN Stock Quote Add-in give the info that is needed? It lets you choose when to update quotes with the press of a button. Attached is info available thru MSN.

    MSN Stock Info.xlsx

  14. #14
    New Lounger
    Join Date
    Dec 2009
    Location
    Amsterdam, the Netherlands
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That could be a better way to retrieve my Stock Quotes.
    But I also need my portfolio data, and only my broker can provide me with that.
    He has a spreadsheet template and I don't know (yet) how to modify the code that
    does the auto updating every minute.
    Thanks for helping me.

  15. #15
    New Lounger
    Join Date
    Dec 2009
    Location
    Amsterdam, the Netherlands
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have almost solved this annoying problem.
    Steve, your suggestion to break the links was helpful, thanks for that.
    I made a button and a macro but the recorder creates hundreds of statements like
    " ActiveWorkbook.BreakLink Name:="abcde|tik!id536?close", Type:=xlOLELinks "
    is there a beter way to code this ?
    e.g. ActiveWorkbook.BreakLink All
    When the Names change, my macro would not know that. "All" is what I want.

Page 1 of 2 12 LastLast

Posting Permissions

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