Link to home
Start Free TrialLog in
Avatar of mamelas
mamelasFlag for Greece

asked on

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
Avatar of Thomas Rush
Thomas Rush
Flag of United States of America image

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.
Avatar of mamelas

ASKER

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.
Avatar of mamelas

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Thomas Rush
Thomas Rush
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mamelas

ASKER

Thanks