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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
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,343
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

Board Regularmmmm Pizza
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,597
Messages
5,512,326
Members
408,886
Latest member
kashifziatevta423

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top