Unprotect multiple files, & do search and replace

[natief]

Good day,

I have ±70 .xls files in one directory.

I need to unprotect the 1st sheet in each workbook, replace the content of a specific cell (some text in there), and reprotect the sheets.

I am sure there must be a better way than to do it manually, any assistance appreciated.

Thank you.

 

[Peter_SSs]

First, make sure you have a backup of the directory.

Then try this code. It assumes the first sheet of each workbook has no password to unprotect. If that is not the case, hopefully they all have the same password and the code could easily be modified to cope with that. I don't think this code will work with Excel 2007.

In the code, you would need to amend the path, the cell address you want to change and the text you want to insert in that cell.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()<br>    <SPAN style="color:#00007F">Dim</SPAN> mypath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    mypath = "C:\myExcelFiles\"<br>    <SPAN style="color:#00007F">With</SPAN> Application.FileSearch<br>        .NewSearch<br>        .LookIn = mypath<br>        .SearchSubFolders = <SPAN style="color:#00007F">False</SPAN><br>        .Filename = "*.xls"<br>        .MatchTextExactly = <SPAN style="color:#00007F">True</SPAN><br>        .FileType = msoFileTypeAllFiles<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Application.FileSearch<br>        <SPAN style="color:#00007F">If</SPAN> .Execute() > 0 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> .FoundFiles.Count<br>                Workbooks.Open (.FoundFiles(i))<br>                <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.Sheets(1)<br>                    .Unprotect<br>                    .Range("E10").Value = "myNewText"<br>                    .Protect<br>                    .Parent.Close (True)<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    MsgBox "Process complete"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[natief]

They are password protected, and in Office 2007, so that seems to be a problem.

Thank you.