Replacing strings within macro's

**tneva** · 05-04-2009, 06:41 AM

Situation: There's whole bunch of excel files. All contain certain line which has certain string within them. Is there any way it would be possible to have macro find all excel files within folder, search through all macro code and if said string appears replace it with new one?

Lots of code but lots of files so this might be faster(and done right would help if something similar needs to be done).

If possible maybe you could provide names of functions/methods I need to look? I have this link: http://www.vbaexpress.com/kb/getarticle.php?kb_id=800 providing basic idea for searching all xls files within folders but how to access all their macro code and look for required string(and replace that line with new one)?

Thanks.

**stanleydgromjr** · 05-04-2009, 08:03 AM

tneva,

Thank you for the code.

This should get you started in the right direction:
Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

Introduction

You can write code in VBA that reads or modifies other VBA projects, modules, or procedures. This is called extensibility because extends the editor -- you can used VBA code to create new VBA code. You can use these features to write custom procedures that create, change, or delete VBA modules and code procedures.

**Special-K** · 05-04-2009, 08:05 AM

If you're searching a string of data you've entered why not search using the operating system? If you're searching for reserved VBA/macro/functions you may be in trouble as I believe they would be stored internally in a different format. Maybe someone else can confirm this.

**tneva** · 05-04-2009, 08:24 AM

Originally Posted by Special-K

If you're searching a string of data you've entered why not search using the operating system? If you're searching for reserved VBA/macro/functions you may be in trouble as I believe they would be stored internally in a different format. Maybe someone else can confirm this.

Can the operating system replace all correct strings from ALL excel files automaticly? If yes then please do tell how. I'm out of ideas how to do that. Manually opening each and every file and replacing them by hand via replace method would aparantly be too lenghty process(aparantly there's quite a lot of files to go through). Macro would be good in that it would go through them all automaticly.

**Special-K** · 05-04-2009, 05:00 PM

Sorry my mistake, I didnt read your post correctly, I thought you were just searching, didnt realise you needed to replace it too.

**TonyS** · 05-04-2009, 07:56 PM

Hi,
Change the value for B1 and B2 then execute Main().
I did not add the codes for all worksheets in the workbook.
It'll replace the value for the 1st ws in every wb.
Also, I did not write all the modules and I can not remember where I copied some of the modules from .
Tony

**ravishankar** · 05-05-2009, 12:07 AM

Hi
the macro in the attached file lists all files in the folder and opens each file, searches the text string in each worksheet and replaces it with new string and saves & closes it. so I suggest you try it on a copy of 1 to 2 files in a separate folder and if it is OK, you can run it on original folder.
Ravi

**stanleydgromjr** · 05-05-2009, 08:55 AM

tneva,

have macro find all excel files within folder, search through all macro code and if said string appears replace it with new one?

I have been going thru the code that I referenced in my post/answer to you.

If you can send me one of the workbooks that contain the macro, in which you want to change a line of code or text string, I will attempt to write the macro for you.

What is the Module name?

What is the Macro name?

What is the full path to the directory/folder where the "xls" files are stored?

What string do you want to change?

What is the new string?

See my Private Message to you (top right hand corner of ExcelForum, Welcome, tneva., "Private Messages:".