Find and replace string in all Excel files workbook in a directory

Question

I am writing a VBA code to replace a particular string in multiple Excel files (workbooks) located in a particular directory. I tried searching on Stack Overflow and I find answer but that is related to replacing a string in a text files through a macro in Excel. The link for the same is find and replace string in a file.

My all workbooks has three worksheets each with Bloomberg formula in cell A2 :

=BDH("CBK IN Equity","BID","07-04-2015 09:00:00","07-04-2015 16:00:00")

The study requires a macro that will replace CBK in many such workbooks in a particular directory with ALBK. This is done in order to download the data for ALBK in place of CBK.

tells · Accepted Answer · 2015-09-23 17:09:29Z

3

Use the formula property to get the string value of formula in a cell then reassign it after replacing the text.

Dim formulaString as String
formulaString = ThisWorkbook.Sheets(1).Range("A1").Formula
formulaString = Replace(formulaString,"CBK","ALBK")
ThisWorkbook.Sheets(1).Range("A1").Formula = formulaString

To go through each worksheet in your workbook

Dim wb as workbook
Dim i as integer
set wb = thisworkbook 'or whatever you choose to open here

for i = 1 to wb.worksheets.count
    wb.sheets(i).range("A1").Formula = Replace(wb.sheets(i).range("A1").Formula,"CBK","ALBK")
next i

edited Sep 23, 2015 at 17:09

answered Sep 23, 2015 at 16:50

tells

6304 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

gaurav kumar Over a year ago

many thanks for the help. I am doing it for a workbook. Dim wb As Workbook Dim wsh As Worksheet Dim formulaString As String For Each wsh In wb.Worksheets formulaString = wb.Worksheets.Range("A1").Formula formulaString = Replace(formulaString, "CBK", "ALBK") wb.Worksheets.Range("A1").Formula = formulaString Next wsh ..This code is giving error. Please help in correcting the code.

tells Over a year ago

Right. So you can iterate to your worksheets in the workbook. Use for i = 1 to wb.worksheets.count to go through each worksheet and use wb.sheets(i).range('your range here').Formula - Don't forget to accept the answer if you've found this to be your solution

gaurav kumar Over a year ago

Dear now the code is browsing all the files and giving NO Errors but my hard luck that still the code is unable to replace CBK with ALBK.

gaurav kumar Over a year ago

Many thanks for the answer as it started working Now. That range was A2 instead of A1. :)

tells Over a year ago

ah right.. didn't give you the A2 reference in the example.

Collectives™ on Stack Overflow

Find and replace string in all Excel files workbook in a directory

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related