Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to change part of a formula across worksheets
Hi all - I hope someone can help with this...
I have formulas that include links (not hyperlinks) in their calculations e.g. Cell A5 formula is: ='C:\Documents and Settings\Bob.JOB-Bob\My Documents\Bob Personal\Inventors\Meetings\[XClient Plan for Restructuring and Rebudgeting as at 06_10_09.xls]Detailed Report'!$U$18+$C83 I need a macro to prompt the user to enter the OLD workbook name into an input box e.g. [XClient Plan for Restructuring and Rebudgeting as at 06_10_09.xls] The code looks for all cells with that string and then replaces it with the new workbook name (entered by the user into an input box e.g. "[XClient Plan for Restructuring and Rebudgeting as at 07_10_09.xls]" (Note - the date changed in the filename). Nothing else changes in the formulas that the system finds. I was using the following code - but it asks the user to: 1. Enter the old and new files names for every worksheet (I have 9 worksheets) AND 2. Select the workbook and worksheet name in a File/Open window for every worksheet. Is there a way to reduce the nominating process i.e. the user only has to enter the old and new workbook name only once and it apply to all worksheets without File/Open windows appearing being required? Earlier I got some help from "Jeff " (the following code) - but it's looking for Hyperlinks, not formula links and therefore it isn't working - but I understand the logic of what it's doing... Can I change it from looking for hyperlinks to looking for links to external workbooks in a formula? : Sub UpdateLinks() Const FolderPath As String = "\\NetworkShare\YourFolder\YourSubfolder\" Dim aWorkbook As Workbook Dim aWorksheet As Variant Dim OldFile As String Dim TargetFile As String Dim Link As Hyperlink Set aWorkbook = ThisWorkbook OldFile = InputBox("Enter Old File Name") TargetFile = InputBox("Enter New File Name") ''''Loop through worksheets For Each aWorksheet In aWorkbook.Worksheets '''''Loop through Hyperlinks in worksheet For Each Link In Worksheets(aWorksheet.Name).Hyperlinks ''''''Check for oldFile Name If InStr(1, Link.TextToDisplay, OldFile, vbTextCompare) 0 Then With Link '''''Update Hyperlink info ..Address = FolderPath + TargetFile ..TextToDisplay = FolderPath + TargetFile End With End If Next Next End Sub -- Thank for your help BeSmart |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change part of hyperlink formula | Excel Discussion (Misc queries) | |||
change color of all cells with formula or are part of a formula | Excel Discussion (Misc queries) | |||
Using formula in code...Part 3! | Excel Programming | |||
Using formula in code....Part 2! | Excel Programming | |||
Change part of formula | Excel Worksheet Functions |