Excel 2002 Links 1

[janetlyn]

1. We have 7 computers hooked to a server.
2. All documents are saved to the server drive (M.
3. Have one Excel workbook (called Jobs) with a list of
numbers in the first column and names in the second
column.
4. Have second Excel workbook (called Timecard) with a
Vlookup, that when someone types in a number it go to
Jobs first column, and picks up the matching "name,
second column".
5. Both workbooks are located in the same folder.
6. Up until 6 months ago, we were using Windows NT.
Then, we leased 7 Dell computers. Now are using XP. and
Excel 2002.
7. A copy of the hard drive of each old computer was
made and placed in the c:\Documents and Settings for each
individual computer.
8. When people started opening the Timecard workbook, it
pointed at the Jobs folder located in the c:\D&S folder
instead of the server drive (M. No big deal, I just
updated the links to point to the M:\ drive.
9. When I saved and reopened the Timecard workbook, all
links were fine. Until the next day when they would open
the Timecard workbook again. (Each individual has their
own "Timecard" workbook, but there is only one "Jobs"
workbook.
10. I then went to each persons computer and changed
their options to always automatically save to the M:\
drive, instead of the c:\D&S folder. For some reason,
this worked on all the computers but one. Everyday, when
he opens the Timecard workbook, we have to go through and
redirect the links again.

Help please? Janet Lyn

 

[MMSantiago]

It may be that the Windows and/or Office settings on that machine are configured to remember the settings for each user, so when you login as ADMIN, to change the settings, you're not really changing the USERS settings. It'll work until a reboot because the system will cache the settings.

If the user doesn't have rights to change settings, then temporarily give his account Admin rights, make the change and then set it back.

On a similar note, make sure that the user has rights to that folder as well. Once you login as Admin, even if you logout and relogin as the user, XP will keep the Admin rights to certain things. Once you do a full reboot, those rights are lost and Excel will default to the local directory again.

Hope this helps...

 

[janetlyn]

Since yesterday, I have found out new information. The folder the settings keep changing back to (located in the c&S folder) is the XLSTART folder. This is now effecting all my workbooks with links pointing to this one workbook (jobs). You talked about admin rights and settings. Although I am admin I am logged on my computer as myself. Everyone has rights to the workbooks with the problems; especially since most of the workbooks are timecards and expense sheets. How do I change each persons computers settings, what settings do I need to change, do I do this on each persons individual computer? One of the problems is that the folders are in a "public" folder that is on the server. Whose rights are those? Please, any help you can give I would appreciate. Janet Lyn

 

[janetlyn]

I just found out that my problem stems from Microsoft's "improvements" to Excel 2002. Since my workbooks are all stored on the server, when someone opens these files, all links will always point to their XLSTART file. Then when I open the workbook, they will point to my XLSTART file. So, to resolve this issue, I will have to take the one workbook (jobs) that all my workbooks are pointing to and put it in the XLSTART file for each computer. And everytime there is an update to that file, I will have to go to each computer and replace it with the one that is there.

 

[combo]

MMSantiago is right - all changes should be done when logged as a real User.
As you can see in explorer, there can be many profiles in D&S, and one of their items is the desktop. If you keep the file (not a shortcut) on the desktop, even in case of the same name, files can be different, depending on user logged. Try rename/move old Filecard file and see what will happen.

Some other guesses:
- when you use 'save" and 'save as' commands with pointing other place but the same name, you have two files with same names in the most recently used list. It is easy to point wrong file from MRU in this case.
- Excel, in Tools>Options, has some directory settings. In my XP default file location (in General tab) is D&S\My login\My documents, box below is empty, autorecover path: D&S\My login\Application data\Microsoft\Excel.

 

[janetlyn]

Combo, I believe I semi-understand the concept of Users rights. However, I am having a hard time converting it to my specific situation. I think I need to give some more details and see if that doesn't help. In the first thread, #4, I explained about the Timecard. Although the 7 timecards are stored on the server, each individual will open up his own timecard (workbook). Those 7 workbooks all reference, through VLOOKUP, one workbook on the server called jobs. I, although I am admin, login as myself. I also open all the timecards so I can do payroll and billings. At first I assumed like you did that it had something to do with the file location. So I went to each computer and changed their Save and File Location to be the server drive and not their c:\. I just now went and opened all the individual workbook timecards. Of seven workbooks, only two were pointing to the c:\ drive. Yesterday, I had fixed the source of the links and corrected it to point to the m:\ drive. So why just the two computers and not all timecards.

Also, I definitely did not understand the first paragraph of your explanation agreeing with MMSantiago. These files can't be kept on each individual computers desktop. They get changed too often and I need access to all the workbooks. Sorry for being such a ditz. Anymore suggestions? And thanks so much for your time. JL

 

[MMSantiago]

I'm not talking about the Excel files but instead talking about the configuration settings within Excel.

When you said "So I went to each computer and changed their Save and File Location ...", who were you logged in as? If my assumption is correct, you logged in as either ADMIN or as yourself. When the actual person who will be using the file logs in, Excel will have forgotten all about M:. This is because Excel will remember the specific settings for EACH USER separately. You need to be logged into the computer as the user who will be accessing those files when you make the changes to the Excel settings and test the files. That's the only way to ensure that your settings and security rights are proper for each user.

If all your machines are configured for 'Personalized Settings', then you'd have to log into the EVERY computer as EACH PERSON and make the changes individually (i.e. 7 people times 7 computers = 49 times you have to go through the settings).

 

[janetlyn]

MMSantiago, When I went around to everyone's computer I did sign on as them. But, I just went to one of the persons whose machine is still giving me problems. I had him open his Excel timecard. He got a message to update links. I verified that his Save and File Locations were still the M:\ drive and they were. I then went to my computer and opened his timecard. I received the message that the links were pointing to the C:\ drive in my D&S folder and not the M:\ drive. So it must be just my machine that is messed up. So, I just now stopped typing this and went to look at my Excel. I had it saving to the M:\ drive. However, I did not have anything in the box for "at startup, open all files in". So, I set it to the M:\ drive. Maybe this will work. What do you think? JL

 

[MMSantiago]

That's not going to work. That option is if you want Excel to automatically open a file whenever you start Excel. You probably want to remove M: from that field and leave it blank.

Here's something to try: Instead of point the opening location to the mapped drive letter (M, why not point it to the UNC location. For example, let's say your server is called JLNET and the shared folder is off the root of the drive and has the shared name of XFILES. The UNC would be\\JLNET\XFILES. In some cases, Excel prefers the UNC over a mapped drive. Worth a shot.

 

[janetlyn]

Sorry, that did not work. I went into the timecard and made all the links, instead of saying, M:\employees\timecards\2003\joblist-2003.xls; I typed:

\\'Midwest_fs1\Sys'\employees\timecards......

Is that what you mean by UNC? Sorry, I do not know the acronym for UNC. If that is the correct way to do it, it still did not work. Out of seven timecards, only two are giving me this problem. I am becoming desparate. Any other ideas? JL

 

[MMSantiago]

Yes, that's the UNC (Universal Naming Convention). Are you sure you have the name and path correct? What you typed above has apostrophes in it, which is not a typical character to use. If you double-click on Network Neighborhood, you should be able to search for that folder and see the full path in the top of the window.

You say that only two 'timecards' are presenting this problem. Does it matter what machine you are on when you access those timecards? You have several variables:

* Servers
* Computers
* Users
* Timecard files

The thing to do is to isolate where the problem is happening. If the timecard files are truly at fault, then you should be able to login to ANY computer as ANY AUTHORIZED user and access the WORKING timecards without incident. Is that what's happening?
If so, then I would back up the NON-WORKING timecards and copy one of the WORKING ones and rename it as the NON-WORKING one. Then try logging in as the user of the NON-WORKING file and try to use the copy. If it works, then it's the original file. If it fails again, then it's the users' security settings on the SERVER for that folder and/or file.

Let me know the results and we can get to a resolution from there.

 

[janetlyn]

From my workstation, I opened one of the timecards that is not giving me problems, then did a save as and replaced the timecard that is giving me trouble. This morning, I sat at my workstation and opened his timecard. The links were all messed up again. I asked him if he had opened his timecard from his station and he said yes. You said if it fails, then it is the users' security settings on the server. However, everyone's settings are the same. They have access to certain folders and the ones they do not have access to are hidden from them. If I was going to check and verify that the settings are correct, where would I go to to do that? We have Novell on our server. I have the capability of going in and changing any settings. However, when it comes to the server itself, I usually have my computer guy come by and fix things. However, I have told him about this problem and he has no idea how to fix the problem. I feel like I am pushing my luck and your patience, so I appreciate all the help you could give me to point me in the right direction to looking at the settings. But, the person who has a timecard problem has the same rights and security settings as one of the guys who don't have a timecard problem. Next? Janet Lyn

 

[MMSantiago]

At this point, we're pretty sure it's not a FILE issue, because the same file worked for you and not for him (or it worked for him once and then failed for you later). Something is making a change to the file that creates the problem after it's been accessed by this user.

That leaves:

* Server
* Computer
* User

Try this sequence:

* Log in as Admin (or you, which I'm assuming have admin rights)
* Open a working file and do the same SAVE AS like you did before.
* Close out of Excel and shut down your machine(It;s important that you shut down and not just LOGOFF)
* Start your machine up again and login as the user
* Access the file
* PART1: Does is work or not? If it works continue:
* Make a change to the file and manually click the Save button to save the changes.
* Exit Excel and shut down again.
* Start up again, login as yourself and access the file
* PART2: Does it still work?

My guess is that one of two things have happened:

A. PART1 FAILURE - When you access the file as the USER, it will NOT work as desired. My thought then would still be a server/security issue.

It might not be a USER SETTINGS issue but a FOLDER OR FILE RIGHTS issue. Check the properties of both the folder and the file and make sure that the user has the same rights to them as other users have to their folders/files. With Novell, you can right-click on the folder and the file in Windows and choose Properties. If you have the Novell Client installed, you should see a tab for Novell Rights.

B. PART2 FAILURE - It worked as the user but then asked you to save changes when you tried to exit Excel, even though you manually saved your changes. This would indicate that the option settings within Excel or the Windows settings, when logged in as the user, are different than when you log in as yourself. Two things to check:

1. Check the options while logged in as the user. Make sure they match.
2. Check the security rights of the user in Windows. It could be that you did not give them enough rights to be able to save properly to the network.

Let me know how it turns out.

 

[janetlyn]

Okay. I logged on as Admin for the network, and Admin for the workstation. I opened my timecard (it is one that works). It was broke. It needed the links updated. I opened a couple of other timecards I know worked before and they were messed up also (like the other two I can't fix). So, I got out of Admin, and logged back on as myself. I then opened my timecard and it was fixed. It did not give me that link message. I also opened up someone elses that I knew was working, but when opened as Admin was messed up, and it was also working again. I then went to the computer of one of the guys whose timecard is not linking correctly, and when I opened my timecard, it was messed up again. But when I come back to my workstation, it is fine. So, since I was unable to complete step 2 of your instructions, "open a WORKING file" I stopped and now I am at your mercy again. Although I was unable to complete your steps, I think it is a Part1 failure (from your previous thread). All users have all rights to the particular folder that the timecards are in. I do believe it is a Novell problem. This started happening before we went to XP/Excel 2002. Before we were on Excel 97 or 98. I cannot think of reason this would have started happening back then either, like new employee, changing computers, etc.). I'm going nuts. Now what? JL

 

[MMSantiago]

If you log in as YOU on one machine and it works, then log in as YOU on another machine and it DOESN'T work, then it can't be Novell because Novell doesn't typically care about the machine you're logged into. Even so, there are a few things that could be going on:

1. Check your version of the Novell Client. Make sure they are the same. If not, upgrade a failing system to the latest version and try again.

2. Make sure that all systems have the startup scripts running in the client. If you click ADVANCED at the login prompt, you should see a scripts tab and an ENABLE LOGIN SCRIPT option. If drive M: is mapped via Novell, this flag becomes critical.

3. Do you have a Windows server somewhere on your network? In some cases, a Windows server will create a group policy for a machine and deny rights to network files, even if the files are on the Novell server. If it's possible, just as a test, remove the Windows server from the network temporarily and see if the conditions change. If so, then you may need to look at the Windows server settings.

4. Do all your machines have unique computer names? In some cases, if you have the same name, a server will acknowledge the first system to connect and disregard the duplicate system for many functions. Make sure they all have a unique name.

5. Considering you were working with NT workstations previously, it may be that there are Windows settings in the Novell user profiles that are now conflicting with the XP setup that the users are currently using to log in. I haven't worked with anything past Novell 5.0, so you'd have to check the settings and make sure that there is nothing there that would tie a user to a specific computer.

Finally, you could always go with my favorite fix. To eliminate the machine and it's setup once and for all, Ghost one of the working setups onto the failing one. This will remove any doubt that the computer, the OS setup and the User settings within Windows XP and Excel are to blame. Just make sure you change the computer name before you connect the ghosted system to the network.

Ghost, in case you don't know, is a software package from Symantec that will allow you to create a perfect duplicate of a hard drive setup onto another drive. The latest version is 2003 and it's one of the best utilities when working with multiple machines with the same hardware setup.

Wish I had a solid answer for you. Let me know how it goes.

 

[janetlyn]

When we log onto a machine, we actually have to log on twice. Once to the server and once to the local workstation. I, logged on both places as myself, opened my timecard and it opened with no messages and no errors. I went to one of the machines where the problems are, logged on the server, but when I put my name and password at the local machine, it would not accept it, instead it wanted the actual user to log on. So, I logged on for using his password. I opened my timecard and it asked if I wanted to update links. I said yes and it did. So, no problem there either. So, do your steps 1-5 still apply? Thanks, JL

 

[MMSantiago]

Here's the thing: If both Excel files are on the server, and you have all the links (for all users and computers)pointing to M:, why would it be asking for a link update? The links SHOULD be exactly the same!

Based on your last test, three things are going on:

1. The 'failing' system only fails when you log into the Novell server as the user. When you log in as yourself, it works. In each instance, you're telling the computer you're the same person, so there's no change in the computer setup.

That means that the computer setup is OK.

2. The request for a link change tells me that something on that system is NOT pointing to M:. Either that or something within the Excel file is pointing to a local file, so it has to update every time you access it from a different system.

This means that the links within the file may NOT be completely pointing to M:.

Check the JOBS file as well, since there may be a cross-link in there that is not specifically pointing to M:.

3. The fact that it works when logged into the server as you but NOT when logged into the server as the user means that there is SOME server-based security restriction on the user that is preventing him/her from using the files effectively.

Check the startup scripts in the Novell login of you and the user and look for differences. Also, go back through my PART1 FAILURE and double-check the rights on each folder leading to the one with the files in it. To make the checking easier, just pick that one user and yourself.

Or, if you can, create a whole new user in Novell, give him the rights you expect the user to have and try it with this fictitious user. If it works, it may be best to just delete the failing user(s) and enter them in again (since you now have a working sequence for creating a Novell user that functions properly).

Let me know how it turns out.

 

[janetlyn]

Could any of #2 have anything to do with the fact that these timecards have pivot tables in them? When I update the links and get the "bad" timecard pointed back to the M:\ drive it updates all links except those in the pivot tables. I then have to go to the sheet with the pivot table, open the range table and redirect the c:\ to the m:\ again. Pivot tables are not my strong suit, and I did not create these tables and do not know how to create tables. Anyway, can the pivot tables be causing some of the problem? JL

 

[MMSantiago]

Yes it can. A pivot table is typically a bunch of links anyway, taking info from other sources and correlating it in some way. If any of THESE links are referencing a local file or path, then your problem would crop up. It may have been that, under your old WIN-NT systems, you had a common file in a certain location. It may have only been a scratch file that the pivot table used to put temporary data, but it was consistent. Now on the new systems, that file would change position depending on how you log in, and the links would have to be updated (or would fail if the file was not created in the first place). It's worth making a test copy of one of the files and looking closely at the pivot tables entries.

 

[janetlyn]

Okay, go back up to your thread on February 24th.

1. Versions are the same.
2. Startup script had the three boxes checked: a)Run scripts, b) Display results window, and c) Close automatically. The Login Script was set to <DEFAULT> and could not be changed. Same with the profile script. But, both machines were set the same.
3. How do I see if I have a Windows server?
4. All computers have a unique name.
5. Don't know how to check the profiles, but, this was happening on the two bad &quot;timecard machines&quot; before we switched to XP.

To your thread on the 25th:

1. You said &quot;The 'failing' system only fails when you log into the Novell server as the user. When you log in as yourself, it works. In each instance, you're telling the computer you're the same person, so there's no change in the computer setup.&quot; This is not true.

I am logged onto my computer as myself (not Admin). I open my timecard and it opens without any messages. I go to a machine that has a timecard problem. The persons whose machine it is is the person logged in, not me. They open my timecard. It asks if I want to update the links and they say yes. It opens the timecard. They save and close. I go back to my machine and open my timecard. Now I get the message that all my links are now pointing to the c:\ drive and not the M:\ and I have to fix them.

2. All links are correct and I could not find any that were pointing to the c:\ drive.

3. I don't think that statement is accurate either.

I am so sorry I am not making myself clear. But paragraph 1 above explains the situation exactly. I appreciate your continued efforts to help me resolve this issue. Janet Lyn