Tuesday, 6 July 2021

To Find a value and replace in multiple excel files


Const FromValue = "MobiRoot_Locatetext"
Const ToValue = "MobiRoot_Text"



Dim WshShell, strCurDir
Set WshShell = CreateObject("WScript.Shell")
sPath    = WshShell.CurrentDirectory
Set fso = CreateObject("Scripting.FileSystemObject")
Set oExcelApp = CreateObject("Excel.Application")
    Set ObjFolder = fso.GetFolder(sPath)
    Set ObjFiles = ObjFolder.Files
        For Each ObjFile In ObjFiles
            If LCase(Right(ObjFile.Name, 5)) = ".xlsx" Or LCase(Right(ObjFile.Name, 4)) = ".xls" Then
With oExcelApp
   .Visible = False
   .DisplayAlerts = False
   Set oWB = .Workbooks.Open(ObjFile)
       End With
Set oWS = oWB.Sheets(1)
oWS.Cells.Replace FromValue, ToValue
oWB.Save
oWB.Close
oExcelApp.Quit
            End If
        Next
Set WshShell = Nothing





No comments:

Post a Comment