sharing excel files over a network

[stickboy]

The problem :- I am trying to setup a system that allows more than one user to work on a spreadsheet over a network.
I can share the files - but conflicts always arise upon saving the spreadsheet.

Possible solution :- have 10 separate files that users can use, which can get sucked into one main one whilst no one is using it.

Question :- Is there a way I can write a piece of VB coding that can check if a certain file that is opened is currently being modified by anyone else on the network - and if so it will then try the next file in the series, so that the user can get a file that they can solely use and modify without and data collisions.

One thought I have tried is to save the file, and if an error occurs(i.e. the file is open elsewhere and you cnnot save a readonly file) then try a different file.
Not sure if will work.

Thanks
Stuart

 

[Richie(UK)]

Hi Stuart,

I hate to spoil your possible solution, especially as I can't think of an alternative, but what happens if each of your 10 users adjust the same cell? Eg, say they all enter a different figure in A1, how will you decide which value to use?

 

[stickboy]

Cheers for replying Richie,

that is indeed the problem.

So what i wanted to do was get the code to open say file a.xls, then check to see if anyone is modifying it, if they are it closes a.xls, and then opens b.xls, and so on etc.

The reason for this is so that there can be no data duplication in any cells as you stated.

I can do all but the check to see if the file is being modified by anyone else!

I've tried the code

on error goto 1:
activeworkbook.save

If the workbook is readonly (as it would be if someone else is already in it) then it would throw back an error which i could identify.

any thoughts?

 

[Andrew Poulsom]

This might help you.

http://www.mrexcel.com/board/viewtopic.php?topic=22241&forum=2