Update links to other workbooks while running macro

  • Thread starter Thread starter kaininito
  • Start date Start date
K

kaininito

Hi, this must be easy, but I´ve searched and can not find the
solution:

I have an Excel spreadsheet with realtime stock prices (spreadsheet A).
I have written a little tool in a separate file (spreadsheet B) that
links to the realtime prices and performs some checks on them.
However, while the macro is running through its user-specified amount
of loops, the data is frozen. How can I avoid this? Can I tell Excel to
keep updating the link from sheet B to sheet A, or how can I include an
update routine into the macro loop?

Thanks a lot
Kai
 
1. You could try adding the lines
Calculate
DoEvents
in various places.

2. Perhaps open another instance of Excel
 
Hi again, unfortunately DoEvents and Calculate do not solve the
problem, the external links still stay frozen while the macro is
running.

Any other ideas?
Thanks
Kai
 
Thanks okaizawa. For some reason this did not work either, at least not
when the source of the realtime data and the DDE spreadsheet were open
at the same time.
I got a message saying "update method failed" ..
Luckily I could solve the problem in a different way. The macro in
question was supposed to alert the user with sound when a certain
condition occurred, I found a way to do it without a macro altogether,
see:
http://www.j-walk.com/ss/excel/tips/tip59.htm
works like a charm.

Thanks to all,
Kai
 
Back
Top