Change workbook path in formula
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("A1:DZ250").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 |
All times are GMT +1. The time now is 10:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com