Replace a string in over 250 excel files

Sup,
I've been given the assignment to replace an old company name with the new one in an excessive amount of excel files. All the excel files seem to be some kinds of forms with fancy buttons and such. I've been trying to research this quite a bit but only found some shady programs which I'm not installing on company computers so I decided to write a batch script to try and accomplish this, if you have any other ways to do this do let me know. So far I've only got a basic skeleton of the batch script, like looping through all the excel files in a directory and then loop through all the worksheets within the file. Here's how it looks

Please Login or Register  to view this content.

I just want some confirmation that this would actually work and how to replace all occurrences of a string in a workspace.

Thank you!
Sincerely, Glitches.

Thank you so much!
But how do I run this? do I just install Excel VBE and add it as a module and run it there?

Sincerely, Glitches.

Thank you, I managed to get it running, and I've got one more question, Does it work recursively, like does it go to into all directories?
This is currently going through 1,2 GB of excel files, I'm just gonna leave it over a couple of days to see if it can't manage to finish all of the files, cuz this is quite a lot massive files.
It's been 15 minutes and it's still on the first file, so I'm not sure if it's working, but whatever.
I'll report back and tell you how everything went.

Sincerely, Glitches.

Well, I won't go back into work until Friday, and I just left it running on my machine so I'll just have to check on it then and see if it's still on the first file.
I'm not sure if I can remote desktop into the machine from home... I'll have to check on that.
The recursive script would be great, otherwise I'll just put all the files in a single directory.

Thank you, you've been extremely helpful!
Sincerely, Glitches.

Thanks!
I'll check it out in the morning, did you test it out recursively?

Sincerely, Glitches.

Ok, I've got it running but it's kind of strange, I had to change (ObjFile.Name Like "*.xls?") to (ObjFile.Name Like "*.xls"), otherwise it would skip all of the files (except one for some reason). It stops on the first file on (Loop While Not C Is Nothing And C.Address <> FirstAddress) with a run-time error: 91 "Object or variable not set". I can't see what's wrong

Sincerely, Glitches.

Another way would be to put all *.xls files into an array using the Dos Dir command and then loop through the array and use the Findnext code to 'Find and Replace" the text..

Here's a snippet.. if you need.. I will make a solution tomorrow (it's way late here)..

Please Login or Register  to view this content.

I would really appreciate that, but would it work recursively and with multiple sheets, though?

Sincerely, Glitches.

Hey..

Yeap. the /s part of the Dir command makes it include Sub Folders..

Multiple sheets... just loop through each worksheet in each Workbook..

I'll post something tomorrow...

This should do it too..


Change OldComanyName, NewCompanyName and the filepath to suit..

Please Login or Register  to view this content.

Thanks, I'll check it out on monday!
But all my files are password protected, would that work with this?

Sincerely, Glitches.

See Post #4 by Tindendr...

If you can't implement something similar.. post back and let me know if just the Workbook is password protected, the Worksheets or both..

I'll see if I can implement it myself.

Thanks
Sincerely, Glitches.

It seems to stop on the first file as well with a run-time error '432' "File name or class name not found during Automation operation" on the line "With GetObject(x(i))"

I just found out that some files are password protected and others are protected but with no password, so when the script runs (WS.Unprotect "MyPassword") it says it's incorrect on the files with no password. but they still need to be unprotected just with (WS.Unprotect "") I think. Is this possible?

Thanks
Sincerely, Glitches.

Wait, I just realized some of the files aren't even protected so they are giving me an error at (WS.Unprotect "Pass") It would be nicer if it would be possible to check if the sheet is protected and then try a password and if that doesn't work try no password