查找并替换目录中所有Excel文件工作簿中的字符串 [英] Find and replace string in all Excel files workbook in a directory

问题描述

我正在编写一个VBA代码来替换位于特定目录中的多个Excel文件（工作簿）中的特定字符串。我试图搜索Stack Overflow，我找到答案，但这是通过Excel中的宏替换文本文件中的字符串。该链接为
查找和替换文件中的字符串。

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.

我的所有工作簿都有三个工作表，每个工作表都有单元格A2中的布隆伯格公式：

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")

该研究需要一个宏，可以在特定目录中的许多这样的工作簿中用ALBK替换CBK。这样做是为了下载ALBK的数据代替CBK。

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.

推荐答案

使用公式属性获取字符串值的公式，然后在替换文本后重新分配。

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

这篇关于查找并替换目录中所有Excel文件工作簿中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！