How to bypass static Excel Links and Functions

Not seeing any responses, I'll simply sugget that you consider that you can use search and replace to change all the (now bad) link instances.

Alternately, a bit more initial work but easier subsequently, you could build the link so that it uses the server name stored, say, in cell A1 on Sheet2.  Now all you have to do is change the text on Sheet2:A1 and update, and you're good.

Last possibility, can you grab the new server name from a Windows environment variable, so that you simply have to run a batch file once on each client machine to set the servername properly, and they will get the new and correct server name automatically every time they reference a cell?  See http://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-do-i-use-an-environment-variable-within-the/2f12706c-28fd-4b62-baf3-198eadcdc569, http://www.wiseowl.co.uk/blog/s387/environment-variable-vba.htm,  and
http://www.myengineeringworld.net/2014/05/vba-windows-environment-variables.html

I suppose if you wanted to get truly crafty, you could have "backupserverpath" always set in the users' environments, and then check to see if the main server was up before changing the path to the backup server.

Anyway, the last method seems the most elegant and least work once it's going.

Dear SelfGovern,

Thank you for your reply.

The excel files that I have are thousand and the links and functions inside them unknown.
Renaming/Pointing each cell that contains functions and links to the new Server is not
applicable and is not a permanent solution for the future.


- Could you please specify the Enviroment Variable option because I cannot understand the procedure?

- I have also heard that DFS may solve my problem by replacing the Server Name with Domain Name (\\DomainName\DFSRoot) but in this option do I still have to replace the ServerName with the Domain
name for every relevant Excel File?

Finally I was wondering, am I the only one who is facing such problem?
What happens when an IT Admin replaces the File Server that contains such files with a new one?

The link I posted outlines how to create an environment variable and how to access it from Excel.  You need to use Visual Basic (other options may be possible, but VB should be your first choice) to grab it and use it in your formula.

Do you have to go in to your spreadsheets and change the old formulas that don't work?  Yes, unless you want to keep the old formulas that don't work.   It's like if Ford used the wrong bolts to put your car together, and they kept breaking.  "What can I do?" you ask.  Well, you can replace every bold that is the wrong bolt, or you can deal with the bolts breaking if you don't change them.

Now, the benefit of having a spreadsheet instead of a car is, you can do a search and replace to remove the bad string in Excel and replace it with a good string.  With a car, you have to physically take things apart, swap in good bolts where the bad bolts were, and then put everything together.    With search and replace, it can all be done automatically.  Perhaps you could use VB or some other tool to do it globally without even having to manually open the spreadsheet.

DFS may solve your problem.    Until you develop a "do what I mean" module for Excel, it will continue to "do what I say" -- thus, in order to get it to behave differently, I would guess you need to change what you say.

Dear SelfGovern,

Thank you for your prompt answer!

A manual "Search and Replace" would solve
the problem but
1) it would take too long to complete
2) it is not a permanent solution. In case of replacing the Server which will have a different name in the future, it would lead to the same headache

Perhaps it would wise to combine a VB script with DFS since the domain name is usually permanent.

Could you advise such VB script or a tool in order to perform global changes in a more fast, efficient and automatic way?

Thanks