Can't Edit Source for bad data link in excel - HELP!

[TheGMan]

Okay, so I've got an excel spreadsheet that accesses data from five other xlsm spreadsheets/worksheets. Four of the links in the spreadsheet are good; however, one is bad (it's looking at the 'users$' folder on the server instead of the network drive where the source spreadsheets are located) hence I need to Change the source. Easy? So, I go to the Data tab on the ribbon > Edit Links on the 'Connections' area which opens up the 'Edit Links' box. I select/highlight the bad/broken link and click on 'Change Source' I then navigate to the correct destination (where the excel spreadsheet I want to link to is stored) and click on 'OK' expecting it to update the location - but it just doesn't work! The location still shows the 'users$' folder and if I click on 'Open Source' I still get the 'unable to find file, try changing path etc' message (which I've just tried to do)

If I highlight all five links and click 'Break Links' four of the links disappear but the bad/broken link remains!!

I can create a folder in the 'users$' folder on the server and put the spreadsheet in there to 'bodge' it but don't really want to have the linked folder in there. I want it to be with the other linked folders on the network drive.

How can I change this 'seemingly' unchangeable source location??

TIA

[David A Coop]

I am not in an environment where there is a server any more (been retired for 6 years) but I do recall a similar problem about ten years ago.

It was caused by the "owner/creator" of the file linking to files on the server AND a file on his own hard drive. (Users link). Could this be the problem in your situation?

Regards,

David

[TheGMan]

Quite possibly. I can make the link good, as I say, by creating that path of folders in the users$ folder on the server but I don't like the idea of having it there in case the user deletes any of the other 'user' data stored in this location. Ideally need to update the link but I don't think this is gonna let us so may have to be a new spreadsheet that's set up with the correct links.

[David A Coop]

Can you move the problem file? If you can click OPEN SOURCE, then save to a new location. If you put it in the same location as the rest of the files being accessed the problem should be resolved.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[Chance2]

Have you checked to make sure there are no named ranges in the file? Usually a named range (Formulas --> Name Manager) is NOT updated by the change source.

[TheGMan]

This is very strange......

Okay, so I tried the 'Open Source' route which opened the spreadsheet from the users$ folder (the wrong location). I then clicked 'Save As' and saved it to the correct location. This appeared to work as the data link then showed the right path (to where the other spreadsheets are kept). I clicked 'close' in the data links box. I then double checked by opening 'Edit Links' again. Still showed correct path in the 'Data Links' box. So I used 'Save As' and renamed the spreadsheet say 'Spreadsheet2'

I closed the spreadsheet and re-opened 'Spreadsheet2', checked the link and what d'ya know, back to the wrong location! Argh!

With regards to Named Range (Formulas --> Name Manager) there's lots of stuff in there such as print_area, print_titles, company, data. The values appear to reference areas on the main spreadsheet, not the source spreadsheets.

One thing I have noticed is the 'source' spreadsheets have links and some of those also refer to the 'users$' folder. Could that be what's corrupting it or would it have no bearing on the main spreadsheet what-so-ever?

[David A Coop]

Hi GMan,

Chance2 is quite right about Named Ranges etc. They can be troublesome, especially when in objects like charts, or in Conditional Formats, or Named Formulas, or Data Validation. (This is often the cause of being unable to "Break Links")

There is a lot of discussion re issues with Breaking Links. I found this one quite useful.
http://blog.contextures.com/archives...inks-in-excel/

You need to determine whether these named ranges are actually used by your model. Sometimes you can just pick them up - especially when there are other users workbooks involved.

I have seen situations where I had to strip the model down, and virtually rebuild it in a new workbook which didn't have the unnecessary detritus that you get from workbooks created by someone else.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".