Bulk Find and Replace in 500 files

[yossel]

Hi All:

I have 500 Excel files in a folder, all with the same macro. I need to change the text in the macro. Is there a product out there that will go through all 500 files and do the find and replace in the macro?

Thanks,

Yossi

[DanielCrinson]

Hi, what is text that needs changing in each macro?

Is it things like sheet name etc?

[yossel]

I want to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[alansidman]

If you put your macro in a personal.xlsb file then you will not need to edit it 500 times.

Look at this link.

http://www.rondebruin.nl/win/personal.htm

[yossel]

Yes, I know that. Right now, however, I have to live with this being in 500 files at another location where the owner does not want this in his personal.xlb.

So I still need know if there is any product that will do a bulk search and replace on Excel, particularly in macros.

Thanks,

Yossi

[DanielCrinson]

From what i've tested it would seem you need to remove the existing module in your Old Sheets and replace it with a new one.

I did this by using the following macro...

i had a folder with 10 XLSM files in with a "Module2" and had a list number from 1-10 and told excel to open them one at a time inserting the filepath and "Book" before the number open it and remove Module2 and replace it with the new one i expoerted.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[yossel]

Thanks! I'll look at it and let you know.

[Brendan_Floyde]

Yossi - yes VBA can do this you need to whack a couple of things together. Something like this will do it:


'make sure that access to the VBA project is allowed.
'In the Trust Center, under Macro Settings, check the box labeled 'Trust Access to the VBA Project'.
'set a reference (Tools > References) to the Microsoft Visual Basic for Applications Extensibility library.
' assumes the text is in a standard module so uses - vbext_ct_StdModule
'Change the constants as needed

Please Login or Register  to view this content.

[alansidman]

@Daniel and Brendan

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between

Please Login or Register  to view this content.

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(I have added them for you today. Please read all Forum Rules and comply in the future.)

[yossel]

Yossi - yes VBA can do this you need to whack a couple of things together. Something like this will do it:


'make sure that access to the VBA project is allowed.
'In the Trust Center, under Macro Settings, check the box labeled 'Trust Access to the VBA Project'.
'set a reference (Tools > References) to the Microsoft Visual Basic for Applications Extensibility library.
' assumes the text is in a standard module so uses - vbext_ct_StdModule
'Change the constants as needed

Please Login or Register  to view this content.

Error:

Please Login or Register  to view this content.

user-defined type not defined

Sorry for my lack of experience

[yossel]

Error:

Please Login or Register  to view this content.

user-defined type not defined

Sorry for my lack of experience

Error:

Please Login or Register  to view this content.

user-defined type not defined

Sorry for my lack of experience

Oops! Never mind, I did not follow your instructions to turn on a reference to the extensibility library.

[yossel]

I can't get either method above to work because the project is protected with a password, which I know. What code do I need to remove protection?

[Brendan_Floyde]

VBA does not expose the VBProject passwords in its object model. So you need to use sendkeys.

Some code (originally written by Bill Manville), that can be cobbled together with the above - have a go at fitting together - if you struggle come back.

Note sendkeys are notoriously unpredictable and it's not a great solution but it's the only option i'm aware of. If you do get it working i certainly wouldn't attempt 500 files in one go. I'd break it down if you can, perhaps 10, then 50, then 100 etc.

Please Login or Register  to view this content.

[yossel]

I can't get the SENDKEYS to work. I think I will take another approach:

1. I will create a master copy of the file to be a template.
2. I will make the changes in the master copy.
3. I will then execute a macro that loops though files to be changed
a. open the first old file
b. open the master template.
c. Clear all variable cells from the template.
d. copy all variable cells from the file to the template
e. save the template AS the same name to a new file in another folder
f. close files
f. loop

[yossel]

Worked perfectly!

Here s the code:

Please Login or Register  to view this content.

[Brendan_Floyde]

cool glad you worked out a methodology can you mark this thread as solved as per forum rule 9

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.