Path name problem with pivot data source

[David]

I have a situation with many users mapped to the same server under various
'drive letters' Lets say the server name is 'abcd\'. when users map to
network drive they may get 'X:\' or 'Y:\' or 'Z:\'. They will see
adcd\MyDatabasefolder:\.. as Z:\MyDatabaseFolder\.. etc. I need to force
'abcd\MyDatabaseFolder\' as the pathname when setting up the data source for
the pivot table so that all users can refresh the pivot table via a valid
path regardless of which drive letter has been aloocated on their machine. I
suspect there is a convention for naming the path that I am not aware of.
I've tried the obvious, but no luck so far
Any ideas?

 

[Dave Peterson]

First, this is untested and from memory. So you'll want to test it yourself.

When I had to do this, I found that I had the best luck by disconnecting that
mapped drive. Then I'd use the UNC path when I created the link/pivottable...

I don't recall any problem when the user opened it. But I have read posts where
if the user saved the file, the link would convert to the mapped drive.

I don't recall seeing this problem, but I did my best to make sure that
important drives were always mapped to the same letter for each user.

 

[David]

Thanks Dave
Typing the UNC and full patname when linking the pivot table fixes the
problem (I didn't need to disconnect my mapped drive)
I did have to build my excel file again though - could not find a way to
edit the 'problem' pathname in the original file?

 

[Dave Peterson]

I'd rebuild. But I still don't have a way to test to see if there's something
easier.

But I would verify that it worked on the other pc's (if you haven't already).

 

[Dave Peterson]

ps.

You could open the pivottable wizard for that pivottable and hit the back button
a few times, change the source and then finish (or go forward), though.