2

Excel documents stored on network drive, for example, \\networkdata\Excel\Shared

Data workbook.xls contains tables with data entered into. Report.xls contains UNC paths pointing to \\networkdata\excel\shared\workbook.xls

Users enter data into data workbook.xls, and then see this reflected in report.xls- one workbook pulls data from the other.

One machine is giving issues. If this machine opens, edits and saves these documents, it changes the paths to reflect the local machine!

The machine has the path mapped as a drive, eg H:\ (\\networkdata\excel\shared), so opening a file from here will give the path H:\data workbook.xls. I came across this article online which seems to talk about a similar issue-

The Knowledge Base article indicates that if a workbook is opened from a mapped drive, and the UNC >refers to that same drive, the UNC in the link is updated to the mapped drive designation.

Thinking that I had struck gold, I compared the set up (mapped drives, shortcuts etc) to another user's machine to identify a difference. The other machine has no issues but has the network location mapped exactly the same, as the same drive letter.

As a result this machine cannot be used to edit these shared workbooks, as if it saves it, it no longer points to the correct workbook on the network (eg it won't point to \\networkdata\excel\shared, but instread will refer to the location machine)

Any ideas?

0

According to Microsoft, this is typical behavior established by the method the file is opened.

When a source data workbook is linked, the link is established based on the way that the workbook was opened. If the workbook was opened over a mapped drive, the link is created by using a mapped drive. The link remains that way regardless of how the source data workbook is opened in the future. If the source data file is opened by a UNC path, the link does not revert to a mapped drive, even if a matching drive is available. source

The likely cause is the file being opened differently on the various computers, some using the UNC and the other using a mapped path.

| improve this answer | |
  • To add a solution to CharlieRB's answer: Try making a shortcut that uses the UNC path on the bothersome machine. It will require human discipline to open the file using that shortcut instead of whatever the user's current method. It would also behoove you to find out what their current method is and try to switch it to a UNC path at some point. – Engineer Toast Jan 20 '15 at 19:56
0

I have had this issue with a few clients. Both were using a mapped drive to access the file. One user would open the file, it would change the links to %appdata%\XLSTART*corresponding file path to mapped drive*

I addressed this by making Excel open in the mapped drive for these users. Options, Advanced, General, At Startup, open all files in: (put the network path).

| improve this answer | |
  • We use several mapped drives, often with Excel files from each. Should it be a specific one? – RASB Mar 12 '15 at 11:30
0

I usually open my files via the Explorer window, and got into this issue when I didn't notice the UNC path in the header bar; it used to be the full regular path. I set my shortcuts to the network folders, but they were all created as as UNC paths.

I opened up a fresh window, navigated to the network drive; all new links would now show up correctly. Killed all old Favorites; new ones now work fine.

Anil

| improve this answer | |
0

Using a simple macro I tried to refresh the default file path each time before saving.

sub saveme() Application.DefaultFilePath = "\blabla\blabla$\" thisworkbook.save end sub()

This seems to work.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.