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.