Find and replace a row in an entire Excel file workbook in a directory

Question

I am writing VBA code to replace a specific line in several Excel files (workbooks) located in a specific directory. I tried searching in Stack Overflow and I found the answer, but this is due to replacing the string in text files with a macro in Excel. Link to the same find and replace the line in the file .

There are three sheets in my books with each Bloomberg formula in cell A2:

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

Research requires a macro that replaces CBK in many of these books in a specific directory with ALBK. This is done in order to load data for ALBK instead of CBK.

Answer 1

, , .

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

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