How to bypass static Excel Links and Functions

Dear Exs,

Drawing.pdf
As per attached drawing I have a NAS that store’s, inside a folder named Docs, various Excel files.

The NAS is connected to a Windows 2008 R2 File Server (Server’s name: Office-SRV1)

All clients are accessing the Excel Files having a Map Drive of  \\Office-SRV1\Docs

Inside the Excel Files there are

 Links: ='\\ Office-SRV1\DOCS \ I C \Operations\[LOG.xlsm]……….

 and Functions:
=IF(INDEX('\\ Office-SRV1\DOCS \ I C \Operations\[LOG.xlsm]……….

The problem that I am facing is that in case:
1) I change the Server Name
2) The Server goes down
all the internal Excel links and functions will collapse.

I am strictly stuck to Server’s Name although the files are hosted inside the NAS.

How should I overcome this limitation (one idea would be to change the Server name on every excel link
but this is out of scope since the Excel Files are thousands)


Thanks in Advance,
Mamelas
mamelasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Thomas RushCommented:
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.
mamelasAuthor Commented:
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?
Thomas RushCommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mamelasAuthor Commented:
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?
Thomas RushCommented:
The solution I recommended was 1) Create a VB script that can grab an environment variable and which can be called by Excel
2) In those too-many spreadsheets, do a one time search and replace to find hard-coded server names and replace them with the VB call
3) When the server goes down, set the proper environment variable on each user's workstation to the correct variable so that they'll be grabbing data from the right server/system/array.  You don't have to touch the spreadsheet, you only need to change user environment variables.

Perhaps you could do it better/faster/cheaper with DFS; I don't know.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mamelasAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.