Vba code to find and replace in excel closed workbooks.

[microhunt]

I would like a vba code to find and replace text in cell A1 in a all excel .xlsm files in the following windows folder c:\Files. The files are all closed and I want the code to find and replace the text without have to individually open the files.

I currently have a folder and have over 100 excel templates with a dispatch date in each template. Once a week I have to open all the templates and change the dates which usually takes about 90 minutes. I asked a friend who is into excel and VBA if he could help and he give me this code to test.

It does not work, I have copied the code into the VBA editor and used F5 to run but seems to be an error. Any help would be appreciated

Sub ReplaceTextInClosedFiles()
    Dim objFSO As Object
    Dim objFile As Object
    Dim StrFolder As String
    Dim findText As String
    Dim replaceText As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim cell As Range
    
    ' Set the folder path and the text to find and replace
    StrFolder = "C:\Files\"
    findText = "YOUR_FIND_TEXT" ' Replace with your desired text
    replaceText = "YOUR_REPLACE_TEXT" ' Replace with the replacement text
    
    Set objFSO = CreateObject("Scripting.FileSystemsObject")
    
    ' Loop through all .xlsm files in the folder
    For Each objFile In objFSO.GetFolder(StrFolder).Files
        If LCase(Right(objFile.Name, 5)) = ".xlsm" Then
            Set wb = Workbooks.Open(objFile.Path, ReadOnly:=True)
            For Each ws In wb.Worksheets
                Set cell = ws.Range("A1")
                If InStr(1, cell.Value, findText, vbTextCompare) > 0 Then
                    cell.Value = Replace(cell.Value, findText, replaceText, , , vbTextCompare)
                End If
            Next ws
            wb.Close SaveChanges:=True
        End If
    Next objFile
End Sub

 

[6StringJazzer]

It is not possible to change anything in an Excel file without opening it. I gather that you mean you don't want to open them manually, but it's OK if VBA opens them. The code you showed does indeed individually open all the files.

When asking for help about a VBA error, always provide the error number, the error description, and indicate which line of code raised the error. When you press F5 and an error is raised, the number and description are in the dialog box that pops up. If you click the Debug button, it will show you the line of code highlighted in yellow.