Macros in a network based environment

[robbo88]

I recently setup a spreadsheet for a number of SUPERVISORS to use for a weekly report of their work. Each week a ADMINISTRATOR goes on the network drive, and sets up all the files for the supervisors to use for that week.

To help her set the files up each week, i created a macro and saved it in her personal.xls file.

All the files are kept on a network drive

Here is my problem!!!

she set up this weeks files last week, and when the supervisors went to use the files this week the were greeted with the message "[filename] is locked for editing by" the administrators user ID however she did not have them open (same message on every supervisors file for the previous few weeks)

Why is it doing this?and how do i fix it?

does it have anything to do with the supervisors file trying to access the administrators personal.xls file???

Also, where is our personal.xls file kept? I have looked in
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
and there is nothing there. Are they saved somewhere different on a network based setup?

Any help would be greatly appreciated!

Cheers

 

[bobsan42]

some more details? like the macro code for example?
can the admin open the files? where did the admin put the personal.xls file?
how exactly were the files created? were the NTFS permissions copied with the files?
a lot of things may cause this.

 

[robbo88]

macro code was more or less (if you actually want the code let me know):
select sheet
find non locked cells
clear contents

admin cant open files
dont know where the personal.xls file is (went into excel, created macro, stored in personal.xls...then imported module in vba)

i created a template file (and saved onto network drive), then admin saved as for each supervisor

NTFS...i have no idea sorry

thanks

 

[bobsan42]

i think i know what you mean but it looks a bit like excel crashed and left the files locked or for some other reason (for example NTFS permissions) the files are locked by windows.
it is more an OS issue than excel.
possible workarounds:
- try to restart the file server
- empty the %temp% folder from files created by excel
- your admin should be able to check for locks on the files on the server or the permissions on the files
- open the files as "read only" and save as a different file name
- remove these files from the server and create them again
- try the Unlocker program to check for locks

 

[Sandeep Warrier]

I have faced this problem many times. It usually resolves itself after a restart. If not then I have my Sys Admin end the particular user's instance on the server.

 

[robbo88]

thanks everyone for your help

Have read only...saved as new file name and started again.

For the moment have removed the administrators macro from her personal.xls file until i gain confidence that its not going to stuff up again.

Thanks again

 

[kpark91]

Well anyways, the personal.xls are saved in
C:\Users\YourName\AppData\Roaming\Microsoft\Excel\XLSTART