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:\Dick\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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-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.