Update links to other workbooks while running macro
 

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

Update links to other workbooks while running macro
 

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

2. Perhaps open another instance of Excel

--
Brian

-----------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...tinfo&userid=5
View this thread: http://www.excelforum.com/showthread.php?threadid=38806

Update links to other workbooks while running macro
 

Thanks Brian, I´ll try that.

Kai

Update links to other workbooks while running macro
 

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

Update links to other workbooks while running macro
 

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...HV05205722.asp

--
HTH
okaizawa

wrote:
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

Update links to other workbooks while running macro
 

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