samcory
asked on
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\producti
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!!!
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\producti
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!!!
ASKER
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.
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.
do not update links when you open the workbook
go straight to Edit Links once the workbook is opened
go straight to Edit Links once the workbook is opened
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 ?
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 ?
ASKER
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!
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!
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\Roa
C:\Users\<you>\Documents\M
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?
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\Roa
C:\Users\<you>\Documents\M
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?
ASKER
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? "
How do I "check the path information under Trust Centre? "
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"
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"
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
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
ASKER
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\producti
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!!!!
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\producti
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!!!!
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 "
on the right, check the box on the bottom that says "Allow Trusted Locations on my network "
ASKER
Yes we ticked that
perhaps these are locations that you do not have permissions for ... ask the IT guy to give them to you?
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.
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.
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?
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?
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).
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).
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it was a workaround rather than a fix
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).