Isnot there anyone who knows this?

[kapil.dalal]

Have already posted my problem, but there was no reply. Here it goes again:

I have this spreadsheet that is linked to the internet. It uses some add in functions to extract info from the internet. As soon as I open this sheet, all the values get updated. Now, this sheet is huge. It takes around 5-10 minutes to extract all the info. I have another spreadsheet which has a macro that copies relevant info from the first spreadsheet and pastes special the values. And this macro is a part of a much larger program.

Now the problem: Once I run this program, the macro just opens the first spreadsheet and copies from there and pastes special. The trouble is that the first spreadsheet hasnot calculated anything by that time. So what I get is a 1000 #N.A.s. How can I ask the macro not to run till the first spreadsheet has stopped calculating?
(I have tried the wait function. But firstly, I need to give an absolute time and I dont want to change the code everytime I have to run the program (No point of whole automation). Also, the time taken by spreadsheet randomly varies between 2 to 10 minutes depending on the processor speed).

Please help.

[Dick Kusleika]

kapil

Set calculation to manual, open the workbook, do a calc, then set calc back
to what it was. Like this

Sub dostuff()

Dim wb As Workbook
Dim lCalc As Long

lCalc = Application.Calculation
Application.Calculation = xlCalculationManual

Set wb = Workbooks.Open("C:\****\wbmyfunc.xls")

With wb.Sheets(1)
.Range("a2").Value = 4 'simulate getting values from web

.Calculate 'program won't resume until this is done
End With

Application.Calculation = lCalc

MsgBox wb.Sheets(1).Range("b3").Value 'do stuff that needs the right
values

End Sub

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

kapil.dalal wrote:
> Have already posted my problem, but there was no reply. Here it goes
> again:
>
> I have this spreadsheet that is linked to the internet. It uses some
> add in functions to extract info from the internet. As soon as I open
> this sheet, all the values get updated. Now, this sheet is huge. It
> takes around 5-10 minutes to extract all the info. I have another
> spreadsheet which has a macro that copies relevant info from the first
> spreadsheet and pastes special the values. And this macro is a part of
> a much larger program.
>
> Now the problem: Once I run this program, the macro just opens the
> first spreadsheet and copies from there and pastes special. The
> trouble is that the first spreadsheet hasnot calculated anything by
> that time. So what I get is a 1000 #N.A.s. How can I ask the macro
> not to run till the first spreadsheet has stopped calculating?
> (I have tried the wait function. But firstly, I need to give an
> absolute time and I dont want to change the code everytime I have to
> run the program (No point of whole automation). Also, the time taken
> by spreadsheet randomly varies between 2 to 10 minutes depending on
> the processor speed).
>
> Please help.

[Nick Hebb]

What is triggering the 2nd macro to run?

The most obvious solution is to wait until the 1st macro is done before
running the second macro.

An alternate method would be to store a readiness state variable in a
cell somewhere in the workbook and have the second macro poll that
before running.

There are also OO-oriented and event-driven approaches that could be
done as well. It's a hard question to answer without knowing why your
2nd macro is trying to run at the same time as the 1st macro.

[NickHK]

kapil,
Look into the .BackgroundRefresh property of your web query.

NickHK

"kapil.dalal" <[email protected]>
wrote in message
news:[email protected]...
>
> Have already posted my problem, but there was no reply. Here it goes
> again:
>
> I have this spreadsheet that is linked to the internet. It uses some
> add in functions to extract info from the internet. As soon as I open
> this sheet, all the values get updated. Now, this sheet is huge. It
> takes around 5-10 minutes to extract all the info. I have another
> spreadsheet which has a macro that copies relevant info from the first
> spreadsheet and pastes special the values. And this macro is a part of
> a much larger program.
>
> Now the problem: Once I run this program, the macro just opens the
> first spreadsheet and copies from there and pastes special. The trouble
> is that the first spreadsheet hasnot calculated anything by that time.
> So what I get is a 1000 #N.A.s. How can I ask the macro not to run till
> the first spreadsheet has stopped calculating?
> (I have tried the wait function. But firstly, I need to give an
> absolute time and I dont want to change the code everytime I have to
> run the program (No point of whole automation). Also, the time taken by
> spreadsheet randomly varies between 2 to 10 minutes depending on the
> processor speed).
>
> Please help.
>
>
> --
> kapil.dalal
> ------------------------------------------------------------------------
> kapil.dalal's Profile:
http://www.excelforum.com/member.php...o&userid=23853
> View this thread: http://www.excelforum.com/showthread...hreadid=375301
>

[Kaak]

It sounds like you're dealing with Bloomberg like a did.

I solved it like this:

First you need a counting cell with a formula like: =COUNTIF(E2:EF2,"#N/A")

Then in the Private Sub Worksheet_Calculate() of the sheet with the calculation cell you check if that cell is zero.

Worked like a charm for me.

If you need anymore help let me know

Jeroen

[kapil.dalal]

Amazing idea man! It indeed worked and it was such an intuitive thought. Hats off to you. And yeah, I was working on Bloomberg.... Where do you work?

[Kaak]

I'm working at Palladyne Asset Management B.V. in the Netherlands.