Update links to other workbooks while running macro

[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

 

[BrianB]

1. You could try adding the lines
Calculate
DoEvents
in various places.

2. Perhaps open another instance of Excel

 

[kaininito]

Thanks Brian, I´ll try that.

Kai

 

[kaininito]

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

 

[okaizawa]

Hi,

i have not tested but
how about updating the links by using 'UpdateLink' method at proper time
in the loop?

UpdateLink
http://msdn.microsoft.com/library/en-us/vbaxl11/html/xlmthUpdateLink1_HV05205722.asp

 

[kaininito]

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