Jtdylktuy

Excel documents stored on network drive, for example, \networkdataExcelShared

Data workbook.xls contains tables with data entered into.
Report.xls contains UNC paths pointing to \networkdataexcelsharedworkbook.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: (\networkdataexcelshared), 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 \networkdataexcelshared, but instread will refer to the location machine)

Any ideas?

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.

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).

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

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

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

This seems to work.