Excel linking problems on a network

[predetc]

The setup for this company has everyone using remote desktop to log in and work on a central server.

As a result the Excel workbooks on the server can be accessed as:
c:\foldername1\foldername2\filename.xls
\\servername\foldername1\foldername2\filename.xls
H:\foldername2\filename.xls (mapped drive letter)

Depending on who opens the file (or if a file gets saved under a different name, the linking references may change and cause a "Source not found" error with linking.
Excel seems to change the references from one type to another (may just put a mapped drive letter in front of the original path). This is also true with Personal.xls which is stored on the C drive of the server which people remote in to (C:\Program Files\Microsoft Office\Office11\XLStart). Sometimes the reference to Personal.xls shows up as "H:\Program Files\Microsoft Office\Office11\XLStart", which does not work.

Do I need to add Auto_open macros to each file to remap the links to the proper locations or is there an easier way to fix this problem.

Thanks in advance!

 

[SkipVought]

Hi,

c:\foldername1\foldername2\filename.xls

is NOT server path and should never be encountered.

\\servername\foldername1\foldername2\filename.xls

is the unambiguous path, regardless of drive mapping.


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[predetc]

Thanks for replying.

C:\foldername1\foldername2\filename.xls
is the local path on the server. From what I understand, since everyone remotely logs into the same server, this path is everyone's "local path" when using Remote Desktop.

Even when I use \\servername\foldername1\foldername2\filename.xls, the links sometimes show up for people as source not found. (For example, they may open the file using their mapped drive path and the link references show up differently.)

Also, since the default location that applies to everyone logged into the server for the Personal Macro Workbook (personal.xls) is the XLStart folder down from Program Files on the C drive of the server, sometimes this reference is changed if workbooks have buttons in them that trigger macros in Personal.xls. The link source for Personal.xls may show up as something like this:
\\Servername\Folder1\Program Files\Microsoft Office\Office11\xlstart.

I'd appreciate any other input.

 

[SkipVought]

"C:\foldername1\foldername2\filename.xls
is the local path on the server.

That is double-speak. C & D are not on a server. They are local files on any individual PC. Some other person cannot access a file on YOUR C drive. Everyone that has permoission can access a file on a server.

I'd say that if the drive is C or D, then you do not want to change anything, because those files are LOCAL to that person's PC.

Any other drive ought to use the \\server\ notation.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[predetc]

Pardon the double-speak, I'm partially bilingual.

I won't say that I may be misunderstanding something about how Remote Desktop works, but let me explain further

When I use the term "server" in this statement:
"C:\foldername1\foldername2\filename.xls is the local path on the server." I am generically referring to the hard drive path of the physical machine upon which the server software is installed.

The key here is that everyone in the company only works in the Remote Desktop environment, so they never use the C Drive on their own physical PC. (They may not even have Excel installed on their local PC.) Therefore, the C drive I am referring to is the C drive that is available while in Remote Desktop, and therefore the same C drive for all users.

I can go to anyone's desk in the company and since they are logged into the same server (computer) through Remote Desktop, I can pull up the remote C drive and navigate to the same Personal.xls file (C:\Program Files\Microsoft Office\Office11\XLStart) I just worked on from my on computer. If the Personal Macro Workbook was not stored at that location, it would need to be copied to every user's Documents and Settings into their XLStart folder under Application settings.


So I still need to figure out why sometimes the link source for Personal.xls may show up as something like this:
\\Servername\Folder1\Program Files\Microsoft Office\Office11\xlstart when Excel is used in the Remote Desktop Setting.

 

[SkipVought]

Now I understand. I have always been in a large company where PC's are not used as servers. Sorry for misunderstanding you.

"...they never use the C Drive on their own physical PC"

Where is the operating system and application software then for each PC? Surely, it is on each person's PC C DRIVE.

Therefore, c:\ can ONLY mean the C drive on the PC I'm banging keys on. The SERVERS's C drive, might then be...
[tt]
\\server\C\
[/tt]
or some other logical id.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[predetc]

Skip,

I agree it is a strange setup. I am doing some consulting here at a small company, and I had never previously seen this kind of setup.

Each user has Windows XP installed on their own machine, but the procedure is to turn on and log onto the local PC and immediately log in thru the Remote Desktop to the same companywide server/computer. Therefore, the application software that they are using is all installed on the companywide master computer hosting the server. (It does create performance bog-downs, but that is a separate issue...)

So that is the environment I am working in.

 

[SkipVought]

No, the point that I am making is that on anyone's PC, when they access C:\anything... it is a path on THEIR INDIVIDUAL PC, and not any other PC, server or not. Is that not true?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

Ahhhhhhhh.

Remote Desktop. They are DRIVING the remote PC.

Can more than one person be driving at the same time?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

Still trying to understand your environment.

I can, for instance, from home, via a VPN, log into the company network.

I can ALSO, while on the network, log into my Company PC, and use the applications and files that I have installed on my company PC, as if they were on my home PC. In addition, I can access other servers from my company PC, just as I would is I were sitting at my desk at work.

Is this at all similar to the issue?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[predetc]

Yes, if you treat each person's work desktop PC like a computer at home, they would go to Start->All Programs-->Accessories-->Communications-->Remote Desktop Connection

Everyone has the same location/"remote computer" to log in to (for example call it "AcmePC"). There is only one server (\\ACME) running for the company and it is installed on AcmePC. All desktop applications (like Goldmine, Outlook, and Excel) are all installed and used from AcmePC.

If you access a file stored in \\ACME, you are accessing a file stored somewhere on the "C:" drive of AcmePC.

That is why there is one universally available location for the Personal.xls file. That is also why files may be opened through the paths:

c:\foldername1\foldername2\filename.xls
\\ACME\foldername1\foldername2\filename.xls
H:\foldername2\filename.xls (mapped drive letter)

I don't know all the rules for when/why Excel changes the way it refers to external sources in its external links. The same file, however, may give different results depending on which path you navigate (C, server path, or mapped drive letter) to open the file.

It seems like Personal.xls needs to be referred to using the absolute C:\Program Files path, but the others may or may not.

Here is code that I put into individual files to fix their references:


Sub Autpen()
' Using the name "AutoOpen" for the macro causes this macro to run
' automatically every time the file is opened

Call RemapLinkPERSONAL_LocalVersion

End Sub

Sub RemapLinkPERSONAL_LocalVersion()
' SUMMARY: ' will check to see if Personal.xls is referenced (linked to--probably by macro assignments)
' to the server's local C:\ drive (correct)or network path (incorrect)
' and will change source if necessary

Dim aLinks

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
If InStr(1, aLinks(i), "\\ACME\Accounting\Program Files\Microsoft Office\OFFICE11\xlstart\Personal.xls", vbTextCompare) > 0 Then
ActiveWorkbook.ChangeLink Name:=aLinks(i), _
NewName:="C:\Program Files\Microsoft Office\OFFICE11\xlstart\Personal.xls", Type:=xlExcelLinks
End If

If InStr(1, aLinks(i), "Q:\Program Files\Microsoft Office\OFFICE11\xlstart\Personal.xls", vbTextCompare) > 0 Then
ActiveWorkbook.ChangeLink Name:=aLinks(i), _
NewName:="C:\Program Files\Microsoft Office\OFFICE11\xlstart\Personal.xls", Type:=xlExcelLinks
End If
Next i
End If

End Sub

 

[SkipVought]

I don't understand, then, what the fuss is about, unless each person logging in to the report desktop (ACME server) has individual profiles that are different enought to screw things up.

If that's the case, then it would seem prudent to me to set up some kind of boilerplate user profile.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}