Need macro to find and replace text in footer of multiple excel files

[ToMeee]

Hi there!

At the company, where I work, the abbreviatons refering to the departments has been changed, e.g. "LOG" to "SCM" (logistics to supply chain management). Plus in some cases, the number-system will change, too: "LOG-003" to "SCM-005". Therefor almost every file, has to be changed. I have an excel file containing the old abbreviations in column A, and the new ones in column B.

The excel files only contain the abbreviations in their footer. I searched the internet for hours, but I couldn't find a macro that could find and replace certain values in multiple excel files' footer from batch. I would be really grateful if someone could help me!!

[alansidman]

Perhaps this will get you on the right track

http://www.cpearson.com/excel/headfoot.htm

[ToMeee]

Thank you, but I just started to work with macros, and I need a quick solution. I was hoping that someone knew a whole VBA code or a tool, that I only have to modify, because I don't really have the time right know to learn to write my own code. We have to change the documents in a very short time. If you know a macro that could help, I would be really grateful!!!

[alansidman]

There is nothing generic. It will have to be developed. So, a couple of questions.
1. Are all the files that need to be updated in the same Folder?
2. If so, what is the folder name and path?
3. Are there other files in the folder that don't need to be updated?
3. What is the name of the file holding the old and new names?
4. Is the current Footer Left? Center? Right?
5. If 1 is no, I would urge you to put them all in the same folder.
6. If 3 is true, then I would urge you to move them out so that we only have files that need to be updated in the one folder.

[Reidelemanuel]

Please Login or Register  to view this content.

use this Alt (F8)

[ToMeee]

1. The excel files are in 2 different folders, but if it's necessary I will just run the macro separately in the 2 folders.
2. It would be great if I could give the folder path, because it can change.
3. There will be word, and power point files, but if it's necessary, I will move them
4. Name of the excel file that is holding the old and new names is: Transfermatrix.xlsx
5. The values that need to be changed are in the left footer, in every excel file

I use this macro to work with word files:

Please Login or Register  to view this content.

Is there a way to modify it, so that it can find and replace in excel files footers as well?

Thank you very much for your help!

[alansidman]

This is untested code as I didn't have your files to test with and to develop a test environment was to time consuming.
For those interested, I borrowed some code from "The SpreadsheetGuru.Com" and then adapted it as I understood your issue. Read the comments in the code to make sure you understand what is happening before you run it as you may need to make some changes prior to running.

Please Login or Register  to view this content.

[jolivanes]

Not all too different from Post #7 I assume.

Code needs to be changed if any of the following is not required/true.
It checks all Sheets in every Workbook in that Folder.
It checks/changes all 3 (Left, Center and Right) Footers. Change in code as required.
Depending on amount of files, go for a coffee while it is running.

Please Login or Register  to view this content.

Read Post #6 after. Take out the Center and Right Footer change.

[ToMeee]

Thank you both! They look great! However, I can not seem to make to work either of them :/

Alansidman: I did as instructed, but it stops after I run the macro and select the folder:
Run time error "424"
Object required

Here:

Please Login or Register  to view this content.

What can be the problem?

Jolivanes:
I can run the macro without problem, and it modifies the excel files, but it doesn't change anything inside them. The footer is the same :/
The old values are in Column A and the new ones are in column B. Is there anything else I have to change in the macro?

I really, really appriciate your help guys! If we can make one of the macros to work, I will be forever in your debt!

[jolivanes]

What do you mean by:
"it modifies the excel files, but it doesn't change anything inside them"
Did you change the Folder and Folder Path and have the backslash (\) at the end?
Do any of the values, in the footers or in the sheet, have extra spaces in it?
Could you attach the workbook with the code in it as well as the footers values in Columns A and B. (needs to be in the same workbook)

[alansidman]

I thought that might be an issue, but without seeing your data files, I cannot test and repair. Vlookup doesn't always work as you expect in a VBA solution. See if Jolivanes solution works for you once you answer his questions.

[ToMeee]

jolivanes: I think I figured out that what is the problem. In most of the excel files the left footer, beside the code, contains a version number, too, like this: "LOG-012 V01". And the macro will only change the footer, if it is an exact match, with the value in column A.

So if I have the old code name in column A, like this: LOG-012, and I want to change it to SCM-012, it wont change the left footer, because the footer contains other characters as well. Sadly, not all excel files contain version numbers, plus version numbers can be different (not all files are version 1). Is there a way to search in left footer just for a predefined code (LOG-012) and change only that, and leave other characters as they were?

Thank you!!

[jolivanes]

Is it always 7 characters, like LOG-012, that need changing, leaving the rest, if there is any, as is?
The emphasis is on 7.

[jolivanes]

If it is the 7 characters, try replacing this

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

[ToMeee]

No, some codes are longer than 10 characters Damn it, I thought it would be as easy as with word files...

[jolivanes]

It doesn't matter how long they are, it is the first 7 characters that you're worrying about.
You're other option in my opinion would be to get all the left footers into a sheet, see what needs changing and, with code, put the replacement Footer in the column beside it and run the code you have now to change the footers.

This should give you all the Footers. Again, this time you might have to go for Coffee and Dinner while it runs.

Please Login or Register  to view this content.

[ToMeee]

You are amazing!!!! It works :D Thank you so much, you are the best!!! If you ever come to Hungary, let me know and I will buy you a beer Thank you, again!

[jolivanes]

Thank you for the kind words. As long as it works for you, that's the main thing.
I was in Hungary several times a few years ago. Stop over when I was on the way to Atyrau.

Good luck