Macros in a network based environment

robbo88

New Member
Joined
Aug 10, 2010
Messages
9
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

New Member
Joined
Aug 10, 2010
Messages
9
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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
 
Last edited:

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672

ADVERTISEMENT

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

New Member
Joined
Aug 10, 2010
Messages
9
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-known Member
Joined
Jul 15, 2010
Messages
1,582
Well anyways, the personal.xls are saved in
C:\Users\YourName\AppData\Roaming\Microsoft\Excel\XLSTART
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,753
Messages
5,833,494
Members
430,214
Latest member
Krisevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top