I have several workbooks in c:\project\ named 'pr_A', 'pr_B', 'pr_C'...
they all have in common a worksheet 'total_data' where are several tables
that agregate the data in the remaining worksheets in the same workbook.
each project as a diferent way of agregate the information but all use the
same worksheet 'total_data'.
i have another workbook named 'main_project' that have also a workshet
total_data but in this case it goes to the file c:\project\pr_a.xls for the
data.
ex: formula in A8 := 'c:\project\[pr_a.xls]total_data'!A8
formula in B9 := 'c:\project\[pr_a.xls]total_data'!B9
(...)

i have the following sub

Public Sub change_file(ByVal actual_pr As String, ByVal new_pr As String)

Application.Workbooks.Open "c:\project\" + new_pr + ".xls"
Application.Calculate
Application.ActiveWorkbook.Close True
Sheet1.Range("A1Z250").Replace What:=actual_pr, Replacement:=new_pr,
SearchOrder:=xlByColumns, MatchCase:=True
Application.CalculateFull

End Sub

i wanted that 'main_project' could change its data "on-the-fly"
if i wanted to see project B i would do

change_file getname_actual_file(), "pr_b"
setname_actual_file "pr_b" ' saves the string in a worksheet cell

however each time an value its replaced it gives an error message (no quite
this because its in portuguese)
says the link was not updated because the "pr_b.xls" wasn't recalculated
last time it was saved. ok to update or cancel.

there are hundred or replacements

what i'm doing wrong ?
it's possible to answer yes to all by default?

p.s.:
calculation its set to manual because some external ad-ins, that are too
complex and if its automatic each time i make a change it take hours...


Thank You
Daniel

Try Application.DisplayAlerts = false

Excel will choose the default response for each alert for you.

http://msdn2.microsoft.com/en-us/library/aa175241(office.11).aspx

Hope this helps!

Colby Africa
http://colbyafrica.blogspot.com