Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to find filename and replace
Hi
I just found the answer to "Find and Replace in VBA" and tried to adapt it for what I'm doing, but it's more difficult so I need some help please. I have multiple worksheets in an excel 2007 workbook - a monthly report. Within the worksheets I have many cells that link to last months workbooks - (i.e I compare last month figures to this months figures). I need the macro to do the following: - Create an Input box for the user to "enter the name ofTHE OLD months workbook" - Create an Input box for the user to "enter the name of THE NEW months workbook" - Excel finds cells with links containing the OLD workbook name and replaces it with the NEW workbook name The problem: After the user enters the NEW name excel asks the user to also select the new workbook from a window and sometimes requires the worksheet to be selected as well. Is there any way to stop this requirement, but to ensure that the revised links work? I tried including "Application.ScreenUpdating = False" and "Application.DisplayAlerts = False", but then the revised formulas turned to #REF once all of the old & new workbook names had been entered (9 times each for 9 worksheets). Also - is there a way to enter the Old and New workbook names once instead of once for every worksheet in the workbook? Old worksheet name: ..../[Plan for Restructuring and Rebudgeting as at 06_10_09 XP version.xls]/Monthly Detailed P&L New worksheet name: ..../[Plan for Restructuring and Rebudgeting as at 07_10_09 XP version.xls]/Monthly Detailed P&L No other details in the links change (i.e. locations like the folder names, worksheet name and cell etc are in exactly the same place in the new workbook so they don't need to change) Current code: Sub Replace() Dim WS As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each WS In Worksheets WS.Cells.Replace What:=InputBox("Enter prior workbook words to be replaced in links"), Replacement:=InputBox("Enter new workbook words to be entered into links"), _ LookAt:=xlPart, SearchOrder:=xlByColumns, _ MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub -- Thank for your help in Advance BeSmart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to find filename and replace
Hi BeSmart, If I completely understand you want to loop though the workbook
adn each sheet in that workbook updating your hyperlinks. Right? If thats the case try somthing like this. 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 OldFile Name") TargetFile = InputBox("Enter 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 "BeSmart" wrote: Hi I just found the answer to "Find and Replace in VBA" and tried to adapt it for what I'm doing, but it's more difficult so I need some help please. I have multiple worksheets in an excel 2007 workbook - a monthly report. Within the worksheets I have many cells that link to last months workbooks - (i.e I compare last month figures to this months figures). I need the macro to do the following: - Create an Input box for the user to "enter the name ofTHE OLD months workbook" - Create an Input box for the user to "enter the name of THE NEW months workbook" - Excel finds cells with links containing the OLD workbook name and replaces it with the NEW workbook name The problem: After the user enters the NEW name excel asks the user to also select the new workbook from a window and sometimes requires the worksheet to be selected as well. Is there any way to stop this requirement, but to ensure that the revised links work? I tried including "Application.ScreenUpdating = False" and "Application.DisplayAlerts = False", but then the revised formulas turned to #REF once all of the old & new workbook names had been entered (9 times each for 9 worksheets). Also - is there a way to enter the Old and New workbook names once instead of once for every worksheet in the workbook? Old worksheet name: .../[Plan for Restructuring and Rebudgeting as at 06_10_09 XP version.xls]/Monthly Detailed P&L New worksheet name: .../[Plan for Restructuring and Rebudgeting as at 07_10_09 XP version.xls]/Monthly Detailed P&L No other details in the links change (i.e. locations like the folder names, worksheet name and cell etc are in exactly the same place in the new workbook so they don't need to change) Current code: Sub Replace() Dim WS As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each WS In Worksheets WS.Cells.Replace What:=InputBox("Enter prior workbook words to be replaced in links"), Replacement:=InputBox("Enter new workbook words to be entered into links"), _ LookAt:=xlPart, SearchOrder:=xlByColumns, _ MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub -- Thank for your help in Advance BeSmart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to find filename and replace
Hi Jeff
Thanks for your code - however I'm not using "Hyperlinks"... I have formulas that include links 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 XP version.xls]Detailed Report'!$U$18+$C83 The macro needs to prompt the user to enter the OLD workbook name (above) as and then enter the new workbook name e.g. "[XClient Plan for Restructuring and Rebudgeting as at 07_10_09 XP version.xls]" (the date changed in the filename). Nothing else changes changes in the formula. With the code I used before wanted the user to select the file and sometimes the worksheet as well - for every worksheet... We only want to change the workbook name once (when the user enters the two names into the input boxes) - without having to also navigate through to the actual file in a FILE/OPEN window for each worksheet. Hopefully this will only be a small change to the code you have already written. -- Thank again for your help - I really appreciate it. BeSmart "Jeff" wrote: Hi BeSmart, If I completely understand you want to loop though the workbook adn each sheet in that workbook updating your hyperlinks. Right? If thats the case try somthing like this. 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 OldFile Name") TargetFile = InputBox("Enter 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 "BeSmart" wrote: Hi I just found the answer to "Find and Replace in VBA" and tried to adapt it for what I'm doing, but it's more difficult so I need some help please. I have multiple worksheets in an excel 2007 workbook - a monthly report. Within the worksheets I have many cells that link to last months workbooks - (i.e I compare last month figures to this months figures). I need the macro to do the following: - Create an Input box for the user to "enter the name ofTHE OLD months workbook" - Create an Input box for the user to "enter the name of THE NEW months workbook" - Excel finds cells with links containing the OLD workbook name and replaces it with the NEW workbook name The problem: After the user enters the NEW name excel asks the user to also select the new workbook from a window and sometimes requires the worksheet to be selected as well. Is there any way to stop this requirement, but to ensure that the revised links work? I tried including "Application.ScreenUpdating = False" and "Application.DisplayAlerts = False", but then the revised formulas turned to #REF once all of the old & new workbook names had been entered (9 times each for 9 worksheets). Also - is there a way to enter the Old and New workbook names once instead of once for every worksheet in the workbook? Old worksheet name: .../[Plan for Restructuring and Rebudgeting as at 06_10_09 XP version.xls]/Monthly Detailed P&L New worksheet name: .../[Plan for Restructuring and Rebudgeting as at 07_10_09 XP version.xls]/Monthly Detailed P&L No other details in the links change (i.e. locations like the folder names, worksheet name and cell etc are in exactly the same place in the new workbook so they don't need to change) Current code: Sub Replace() Dim WS As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each WS In Worksheets WS.Cells.Replace What:=InputBox("Enter prior workbook words to be replaced in links"), Replacement:=InputBox("Enter new workbook words to be entered into links"), _ LookAt:=xlPart, SearchOrder:=xlByColumns, _ MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub -- Thank for your help in Advance BeSmart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replace special character for filename save | Excel Programming | |||
Find/Replace Event or Find/Replace for Protected Sheet ... | Excel Programming | |||
Replace first 8 letters of filename with Macro | Excel Programming | |||
Formula to replace invalid filename characters | Excel Worksheet Functions | |||
Set Location and Filename to replace GetOpenFilename | Excel Programming |