Hi all,
This one will not be easy I believe. Assume you have a workbook "A" that is linked to workbook "B". The problem is that workbook B file name and sheet name is changing every day, therefore links in workbook A will be broken. Is there a way to write a macro in workbook A that would change references within formulas?
Thanks
Last edited by ABSTRAKTUS; 02-03-2012 at 10:40 AM.
Something like this, perhaps?
That will change all references on the active sheet, but you'll have to add some code to ask for, or work out, the old and new file names.
Please Login or Register to view this content.
Idealy I would like user to be able to hit a button that would call windows browser. User then chooses what workbook to pull information from and depending on users choice formulas in the workbook would relink to chosen workbook. Is this doable? Honestly, I need to remove human factor as much as possible (no rewriting formulas, no typing etc. as not all of our users have more than one brain cell).
Something like this, then ...
It opens the file dialogue box to let the user select a new file name.Please Login or Register to view this content.
The value sREFERENCE_CELL points to a cell which contains a formula which has the old file name in, and the old file name is picked up from there.
Does that work for you?
Have you tried the Edit Links dialog?
Good luck.
Spot on Andrew-R!!!, exactly what I'm looking for! Good job! Thanks!
The only thing, it is not one cell (A1), it is a whole range. Where and what to change within the code?
It should change all cells, it just needs one cell with the old file name in that it can extract it from.
Yes< got it now. Thanks mate!
Just realized that code doesn't change the tab name. When I selected different workbook, formulas have ruturned REF errors. Any idea?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks