I want to be able to change the text of a cell in several files in a sub-folder.
The reason for this is in each spreadsheet I have a url entered in cell b3.
I need to be able to change this url in all files in a sub-directory.
I will need to do this five times - once for each of five subdirectories.
so I will have for instance five subdirectories
For all files in the live folder I want to change the url in cell B3 on a worksheet named select to liveurl.com
In test it needs to be testurl.com
In demo it needs to be demourl.com
I have some code below but it is not working at all - I just click the button and get nothing.
I have attached a snippet of code to replace the text oldurl.com to newurl.com
I used to have this working for working in a folder c:\updatereport but it wno't work for sub folders.
Can anyone give me some help please?
Private Sub CommandButton1_Click()
Dim ss As Worksheet
Dim strSearch As String
Dim strFile As String
Dim wb As Workbook
Dim strOld As String
Dim strNew As String
Set ss = Application.Worksheets("Select")
strSearch = "test" 'Search Directory do not use a closing "\"
strOld = "oldurl.com"
strNew = "newurl.com"
strFile = Dir(strSearch & "*.xls")
Do While strFile <> "" ' Start the loop.
Set wb = Workbooks.Open(strSearch & "\" & strFile)
'Run your Find Replace command here
'To replace all of the instances of strOld use this
ss.Range("3:3").Replace What:=strOld, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Set wb = Nothing
strFile = Dir ' Get next entry.