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

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.

Solution

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