Removing references from 500+ files

[bunny3]

Hello,
I developed a template for my office that eliminated redundant data entry by referencing another spreadsheet. I've created more than 500 files from that template. My supervisor recently expressed concern about the files pulling incorrect information if the reference spreadsheet is moved or corrupted. I've been asked to remove the references from all the files I've made from the template.

I've been opening files individually, copying the cells with references, pasting as values, and saving. If I have to do this for hundreds of files, I will. But does anyone have any better ideas? I don't have any experience with macros or VBA but I'd be willing to try. Please be advised, my office uses Excel 2003, so that's all I have to work with.

FWIW, I've advised my supervisor that all he needs to do when he opens an older file with a reference is click the "Don't Update" button. But he's not satisfied with that.

Thanks.

 

[Noodleski]

In the ribbon, under the data tab, there's the option of severing connections with outside references. This will make all external references values and leave all other formulae intact.
I'm sure you could put this into a VBA loop, but that's not my area to give advice in.

The top screenshot shows 'edit links'. This shouldn't be grey in your case and there you have the option I'm talking about:

How-to Connect Excel to an External Data Source - jmerrell.com

 

[Noodleski]

And to follow up, this thread on this very board seems to address your question better than I ever could.

http://www.mrexcel.com/forum/excel-questions/615722-break-all-links-visual-basic-applications.html