Searching code in multiple excel files

[rlcassel]

We have 4500 differnt historical excel files that we need to be able to search through the vba code and replace some text when found. The text would be the same in each excel file. We recently moved to a different network drive and there are about 64 references to the old drive in the code that is pointing to save locations and data files. I am an expert with Access but not excel. Any help.

Ryan

 

[WinblowsME]

Here's a suggestion, create a Data.ini file. Within this file, have the following lines:

SAVE_LOCATIONS=C:\SAVED
DATA_FILES=C:\DATA

Change your macros so that it'll read in this file and load the locations into variables. If there are changes in the future you'll only have to change this data file.

 

[PHV]

Have a look at the VBProjects collection of the VBE object.
You'll need to reference the Visual Basic for Application Extensibility library.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rlcassel]

Actually what i am trying to do is to search all the modules in each excel file for a text phrase and replace that phrase with another. I can open each excel file individually, pull up one of the modules, and do a find and replace on the phrase. I was hoping to write a batch job in access, excel, or another format to do this for all the files in all the folders. The designer had users use an excel file which, when some code runs, opens another excel file, hidden, for some data. He states in code the files location. i.e. S:\BWSC\... I need to search through every excel file in the directories and subdirectories for the occurance of the S:\BWSC\ in all the modules of each file. Then the users would save each excel file. There are over 4500 copies that need to be fixed, otherwise the calculations won't work when each files code is ran.

 

[rlcassel]

I just wanted to add, I am not searching the spreadsheets. I need to search the modules behind the scenes.

Ryan

 

[PHV]

Why not simply remap the S: drive ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CautionMP]

rlcassel,
Because I know that sometimes re-mapping a drive is easier said than done.

As PHV said, you'll need to reference the Visual Basic for Application Extensibility library.

Function UpdateWorkbookCode(WorkbookName As String, FindWhat As String, ReplaceWith As String) As Boolean
Dim objWorkbook As Workbook
Dim objVBProject As VBIDE.VBProject
Dim objVBComponent As VBIDE.VBComponent
Dim objCodeModule As VBIDE.CodeModule
Dim blnUpdated As Boolean
Dim lngLine As Long
Dim strBuffer As String

Set objWorkbook = Workbooks.Open(WorkbookName)
Set objVBProject = objWorkbook.VBProject
For Each objVBComponent In objVBProject.VBComponents
  Set objCodeModule = objVBComponent.CodeModule
  For lngLine = 1 To objCodeModule.CountOfLines
    strBuffer = objCodeModule.Lines(lngLine, 1)
    If InStr(1, strBuffer, FindWhat) > 0 Then
      strBuffer = Replace(strBuffer, FindWhat, ReplaceWith)
      objCodeModule.ReplaceLine lngLine, strBuffer
      blnUpdated = True
    End If
  Next lngLine
Next objVBComponent

UpdateWorkbookCode_Exit:
UpdateWorkbookCode = blnUpdated
Set objCodeModule = Nothing
Set objVBComponent = Nothing
Set objVBProject = Nothing
objWorkbook.Close blnUpdated
Set objWorkbook = Nothing
End Function

This is what I used to test the routine.

Sub Test_UpdateWorkbookCode()
Const cFilePath As String = "C:\ExcelTest\"
Dim strFilename As String
strFilename = Dir(cFilePath & "*.xls")
Do While strFilename <> ""
  If UpdateWorkbookCode(cFilePath & strFilename, "Excel test", "New test string") Then
    Debug.Print "Updated: " & cFilePath & strFilename
  End If
  strFilename = Dir
Loop
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[rlcassel]

Our files are being moved to another drive and directory so that is why I need to replace any code in any excel files that reference the old directory and drive.

 

[rlcassel]

I will try the code above.

Thanks

Ryan