[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

excel search and replace multiple files in a subfolder

HI

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

live
test
demo
train1
train2

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

etc

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?

Thanks

Joy
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, _
                        Replacement:=strNew, _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, _
                        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
 
        '-----------------------------
        wb.Save
        wb.Close True
        Set wb = Nothing
       
        strFile = Dir    ' Get next entry.
    Loop
   
End Sub

Open in new window

update-environment.xls
0
joypmor
Asked:
joypmor
  • 3
  • 2
1 Solution
 
dineeshCommented:
Hi Try the following code..

save as .vbs file and update the sFolder= to your path.

*****Try on a sample data first please.

____________________________________


 Dim fso, folder, files, NewsFile,sFolder
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
 
  Set fso = CreateObject("Scripting.FileSystemObject")
  sFolder = "C:\tests\two"
  Set folder = fso.GetFolder(sFolder)
  Set files = folder.Files
 
  For each folderIdx In files
if right(folderIdx,4) = ".xls" then

Set objWorkbook = objExcel.Workbooks.Open(folderIdx)
Set objWorksheet = objWorkbook.Worksheets(1)

Set objRange = objWorksheet.UsedRange

objRange.Replace "oldurl.com", "new url"
objWorkbook.Save
objWorkbook.close true
end if
Next



0
 
joypmorAuthor Commented:
Hi, thanks for your update.  I don't have access to full visual basic at work though, can this be done as a VBA script?

Sorry

Joy
0
 
dineeshCommented:
hi Joy,

just copy the code to a text file and save as  ".vbs" is will work on XP systems and above by default.

regards
Dinesh
0
 
joypmorAuthor Commented:
Thanks Dinesh - I will do that - I didn't know that!
0
 
joypmorAuthor Commented:
Thanks, that worked a treat!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now