Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Result Change when Workbook Sent by E-mail | Excel Discussion (Misc queries) | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
I have to change a bunch of formula that need to reference a different workbook. | Excel Worksheet Functions | |||
how to change absolute path to relative path | Excel Worksheet Functions | |||
How to interpret a path to another workbook and use in formula bar | Excel Programming |