error with Excel links to other data sources

We have a Excel spreadsheet with links to other data sources which works perfectly on all the other computers in the office.
But on my computer when I try to open the spreadsheet
I get "this workbook contains links to other data sources  etc etc"
click on UPDATE or DONT UPDATE
If I click on UPDATE  I get "this workbook contains one or more links that cannot be updated"  with the buttons "Continue" or "Edit Links"
If I choose EDIT LINKS  it shows all the links with an error "Source not found"
If I look at the location line it says:
"\\server\factory\production stats\2015"  
when it should say:
"\\server\COMPANY FOLDER\factory\production stats\2015"
I do not know why the "\COMPANY FOLDER\" is missing from the link.
If I click on "Change Source" and browse to the correct location  the error disappears. but as soon as I save and reload the spreadsheet
the same error comes back!!!
samcoryAsked:
Who is Participating?
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.

regmigrantCommented:
troubleshooting will depend on a couple of factors:-

1. which version of excel
2. which filetype
3. - most importantly - what sort of connection is in place? the 'workbook contains links' dialog applies to those save in a separate query file, those linking to (eg) a SQL server and those created using 'paste link'.

Off the top of my head: - if its a query file based link check that you don't have a local copy with incorrect information in it. If its a 'paste link' make sure that you have the correct filepath(s) in the 'trusted locations' section of options - in later versions this is buried under options, trust centre, Trust Centre settings. In older versions I remember it was a bit more obvious (tools, options, file locations? --- but its been a while).
0
samcoryAuthor Commented:
it is office 2010 and the file is on the server, everyone in the office opens it with no problem , only I get this problem.
Got another user to log on to my pc to see if it was my profile that was causing the problem but they had the same issue.
Yet when they log on to their own computer no problem.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
do not update links when you open the workbook

go straight to Edit Links once the workbook is opened
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

regmigrantCommented:
I believe there's a local file or setting on your PC which is being picked upl does the problem follow you if logged on at someone else's PC?

if you open a new spreadsheet and create a new data connection (data, 'from other sources'), choose advanced and then look at the 'all' tab - there is a network location towards the end of that list - is it blank?

You've not said how the connection was created - does it have a connection file ?
0
samcoryAuthor Commented:
Sorry it took me some time to get back I was away on a trip.
This is the situation
If I logon on my computer the links are wrong
If another user logs onto my computer the links are wrong
If the same user logs onto their own computer the links are good
If I log on to the users computer in my name the links are wrong!

If I follow the suggestion from Crystal:
"do not update links when you open the workbook "
"go straight to Edit Links once the workbook is opened"
and edit the links and save the spreadsheet then stops working for all and I have to do a restore to getting working again for the others!
0
regmigrantCommented:
ok, if the problem follows you around then it must be attached to your profile; I assume you are the author of the sheet since your changes affect the others.

we need to find out which configuration or file is linked to you and here are some folders that might contain variations which are causing the problem:

C:\Users\<you>\AppData\Roaming\Microsoft\Excel
C:\Users\<you>\Documents\My Data Sources
C:\Program Files (x86)\Microsoft Office\Office14\XLSTART  (maybe OFFICE15 etc)

You've not yet answered the question of where the data is coming from - is it a pasted link or data query?

And have you checked the path information under Trust Centre?
0
samcoryAuthor Commented:
I do not know how to answer this question "You've not yet answered the question of where the data is coming from - is it a pasted link or data query?"

How do I "check the path information under Trust Centre? "
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
To set Trusted Locations in Excel:

File > Options > Trust Center

click Trust Center Settings

from the left list, choose Trusted Locations

on the right, check the box on the bottom that says "Allow Trusted Locations on my network "

also, here is where you can add specific trusted locations
I like to choose roots (ie: c:\)
and check box that says "Subfolders on this location are also trusted"
0
regmigrantCommented:
There are different ways of creating links, one is to 'paste, special, link' another is to use a data query.
I would guess that you are using Paste, Link as you didn't know how to respond.


its been a while since I used 2010 but I think its: File, Options, Trust Centre, Trust Center Settings, trusted locations:-

I suggest you make a note of them then remove any that are there - obviously you are looking for ones that match the location you are a having trouble with - and see if that helps

There also a trusted document group where you can make all documents 'not trusted' - this just means you will be asked to trust them again next time you open one
0
samcoryAuthor Commented:
I went to the trust center and there were no links similar to the one we have with a problem.
I added it and put in a tick for the network.  
However here is the curious bug
Every excel spread sheet with links do not work!
they
all follow this pattern:
"\\server\COMPANY FOLDER\factory\production stats\2015"  is the correct link
"\\server\factory\production stats\2015".  is the link they are showing.

I could not find anything in appdata local or roaming that looked like it could cause a problem.

However if I log in my name onto our Terminal Server REMOTE DESKTOP
I get no errors from the linked spread sheets!!!!
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
in the trusted locations setting, perhaps you missed this?

on the right, check the box on the bottom that says "Allow Trusted Locations on my network "
0
samcoryAuthor Commented:
Yes we ticked that
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
perhaps these are locations that you do not have permissions for ... ask the IT guy to give them to you?
0
Danny ChildIT ManagerCommented:
do you have any different drive mappings to the other users?  If you connect at a different point on the folder tree, that could explain why that folder is getting missed out.  

I would reboot, keep excel closed, disconnect all network drives by r-clicking them, and then reconnect just to the one that you need, using the EXACT method that your colleagues use.  You *must* involve your IT guys in doing this.  Then, open Excel, browse for the file (don't select it from any Recently Used lists), and see how the links are now.
0
Davis McCarnOwnerCommented:
Because Excel gets broken for the other users if you change the link, I think we need to be looking at either system variables or variables that might be fed to Excel.
In a CMD window, if you type SET<enter>, is there any difference between your PC and one that works?
And/or, have you tried searching the registry for \\server\factory?
0
Danny ChildIT ManagerCommented:
The more I think about this, the more I'm convinced it's to do with your network drive mappings.  If you have a network drive that connects one folder further down than everyone else, this would create exactly the same symptoms that you see.

Then, when you save your new, shorter link, it will be broken for other staff.

This drive letter obviously follows you from PC to PC, as you've proved, so it would occur when you worked on a different PC (which I think would not happen with system variables).
0
regmigrantCommented:
to test DanCh99 theory go to a cmd prompt and type 'net use' - this will list the mappings for your login, if he is correct you should see one that points to the 'wrong' folder tree.
0
samcoryAuthor Commented:
Please accept my apologies for not answering earlier, I was again sent away for my work.
None of the suggestions gave me a result I could understand.
As the folder in question was a public one inside the main share, I just shared that folder for everyone and the problem was solved, although the original reason for the problem was not found.
Thanks for everones help
0

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
samcoryAuthor Commented:
it was a workaround rather than a fix
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.