Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
search and replace within macros across workbooks?
We're migrating a large amount of files from one storage
system to another due to capacity constraints. We have a set of excel files that contain VB macros with hard coded links to the full URL path of some other files that are also being moved. Does anyone know if there is a way to do a global search and replace of text within Excel VB macros for multiple Excel Spreadsheets? I.E., original macro may contain text: \\server1\directory\subdir\file.xls We want to change any instances of that reference to: \\server2\directory\subdir\file.xls Thanks! Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
search and replace within macros across workbooks?
Mike,
Try the macro below on a subset of your files first, copied into the folder C:\Test Folder. It will require that you set a reference to MS VBA Extensibility. Then after testing and to run it, change .LookIn = "C:\Test Folder" .SearchSubFolders = False to .LookIn = "C:\" 'or other drive .SearchSubFolders = True 'to do all folders HTH, Bernie MS Excel MVP Sub FixAllFilesOnDrive() Application.DisplayAlerts = False With Application.FileSearch .NewSearch .LookIn = "C:\Test Folder" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute 0 Then For i = 1 To .FoundFiles.Count Workbooks.Open .FoundFiles(i) ReplaceCodeInModule ActiveWorkbook ActiveWorkbook.Save ActiveWorkbook.Close Next i End If End With Application.DisplayAlerts = True End Sub Sub ReplaceCodeInModule(inBook As Workbook) Dim myCode As String Dim myComp As VBComponent For Each myComp In inBook.VBProject.VBComponents With myComp.CodeModule myCode = .Lines(1, .CountOfLines) myCode = Replace(myCode, _ "\\server1\directory\subdir\file.xls", _ "\\server2\directory\subdir\file.xls") ..DeleteLines 1, .CountOfLines ..InsertLines .CountOfLines + 1, myCode End With Next myComp End Sub "Mike Enfield" wrote in message ... We're migrating a large amount of files from one storage system to another due to capacity constraints. We have a set of excel files that contain VB macros with hard coded links to the full URL path of some other files that are also being moved. Does anyone know if there is a way to do a global search and replace of text within Excel VB macros for multiple Excel Spreadsheets? I.E., original macro may contain text: \\server1\directory\subdir\file.xls We want to change any instances of that reference to: \\server2\directory\subdir\file.xls Thanks! Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search and replace | Excel Worksheet Functions | |||
Search and Replace | New Users to Excel | |||
Macros & search /replace | Excel Worksheet Functions | |||
Search and replace | Excel Worksheet Functions | |||
Search and Replace text strings within Macros | Excel Programming |