Sharing an excel spreadsheet - Conference Room Scheduling?

[menstroy]

Hello,

I have a semi simple taske. We want to be able to share an excel spreadsheet that will be used as a schedule for all of our conference rooms in our building The biggest concern we have is people making changes to the schedule at the same time. If there is a better way to organize and schedule a conference room please advise.

I know with excel your can use the shared workbook feature. The only problem that I noticed is that:

Two Users open the file at same time - User (A) & User (B)
User (B) Makes changes to the spreadsheet and saves
User (A) will not see those changes until they Save the document.

This can be a problem for example is one user opens the schedule sees an open slot but does not reserve the timeslot and save the file for an extended period, this means that user is viewing outdated data and by the time they actualy save/reserve that spot it may already be taken. Having realtime data would solve this.

Basicly I was wondering if there is a way to make a vba script that can either update the spreadsheet every 2 seconds, or save the file every two seconds... It appears that when a users saves the spreadsheet any changes from another user are shown then...

Please advise

 

[tbl]

This is not as simple as it appears. What I do if I have to use Excel, (which I usually do due to its superb flexibility), is to write a timer module which checks for the presence of a check file. This check file is written to the directory where the master copy is running. If the check file is present then other users get a message that the file is being written to, and to please wait. When the file is no longer in use, the check file is deleted and another file is created to indicate a change has occurred.This forces a save when another user attempts to use the file. They must first save in order to see the latest changes. This works fine with 2 users, but for obvious reasons not with more than 2. If you have more that two users it is best to force a save before accessing.For systems where all users can be guarranteed to be online, the save update can be automated by detecting the user and saving automatically locally on each PC, using the timer as the driving force.

Richard

 

[SkipVought]

Hi,

Are you using Outlook with an Exchange Server?

If so, that might be something to consider.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[menstroy]

Unfortunately we are not using Outlook or Exchange, we use Lotus notes, which does have some scheduling featrues for this purpose but it doesnt look worthwile..

Isnt a simple solution just to have a VBA script running in the background that automaticly saves the file every 5 seconds? Is this something that is hard to do?

 

[tbl]

This doesn't help much as it won't change what the users see on their screens. That only changes when a save occurs on their computer. Sharing works with copies of the master which are synchronised by saving locally.
Yes I can sent you a script to save every few minutes. This can be done with the On Time function in 3 lines, but it won't change what the users are seeing.

 

[SkipVought]

It's not foolproof! It might work 98% of the time.

But the 2% it doesn't could cause royal problems.

That's why programming is so meticulous, in order to NOT allow ANY kind or ambiguity, like tbl's approch.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tbl]

I think that once you see what it is like working on a PC with a VBA timer running every 5 seconds, you will abandon the idea. When the timer is operating it is impossible to do anything serious on the PC. With this sort of program you are not talking in terms of many changes per hour, but you are subjecting all of the PCs using the program, as the timer runs on all of them at the same time, to intense disruption.
I have got such programs running in shared Excel which have been running in an industrial environment on the shop floor for 5 years. Some work with complex updating and some with simple blocking until an update has occurred. This is what I recommend in this case case. Write a blocker file when changing data to freeze the file to others, save and delete file on completion, and force evryon to save before changing using an On Entry macro.
Please be aware that using shared Excel you cannot see you VBA code or debug, and that protection and formatting are not supported ! VBA does work perfectly - you just have to debug it in the single mode.

Richard

 

[menstroy]

Let me see if I understand you and you udnerstand me

Why wont this work.

User A Opens File - (Looks for open slot)
User B Opens File - (Finds slot and Fills in data)

Every 2 seconds the file is saved.

Open saving the file any new changes made by a different user are shown...

This way if user A is still looking for a slot they will see after 2 seconds that User B has already taken that slot.. Granted there is a 2 second period where both can try to take same spot, but I think this is acceptable for out situation because this happening may be very unlikely.

 

[tbl]

When you run a timer in VBA every 2 secs you won't get much pleasure out of Microsoft office while it is running.
2. Shared Excel takes a disproportionately long time to save over a network. Certainly longer than 3 secs. The timer will run on all of the PCs all of the time !
If you want to test this I will send you a 2 second timer with a save, but unless it can detect on which PC it should run, which is quite possible but more complicated(detect user or PC), all the PCs will be saving every 2 secs !

 

[SkipVought]

menstroy,

You're looking for a silver bullet.

Ain't none! Nada! :-(

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tbl]

Try this by pasting ALL the text into a BLANK VBA module. Save the excel file to a network disk and set it shared.
Run via the Macro menu.

Public NextTick As Date
Sub Timer()
NextTick = Now + TimeValue("00:00:02&quot
Application.OnTime NextTick, "Timer"
Application.StatusBar = "Please Wait. Incorporating new data."
ThisWorkbook.Save
Application.StatusBar = False
End Sub
Sub TimerStop()
Application.OnTime NextTick, "Timer", , False
End Sub

 

[VBAjedi]

Jumping in late here, but I have a different approach to consider. It's just a raw concept, and may have issues I haven't thought of yet, but:

What about a two-workbook approach? Book1 stores your actual data, and is saved on the network. Copies of Book2 are stored on each users PC. When a user opens Book2, it opens Book1 in "Read-only" mode long enough to load the conference rooms current reservation data (also, at any time data can be refreshed with a "Refresh" button).

The user can then specify any open slot and click "Reserve". That triggers code that opens Book1 in Read-write mode (unless another user's code is currently doing the same thing, in which case it trys again in five seconds), checks to verify that slot is still open, books it if it is, and saves/exits Book1.

This way Book1 is only opened in "Read-write" mode by code, and only for a few seconds at a time. Odds are low that two users will do this at exactly the same time, and even if they do, it just means one user waits five seconds and risks losing their desired slot (if the other user took it).

Any thoughts on this approach? It might be deemed too involved for this particular situation, but would the concept work? Why or why not?

VBAjedi

 

[tbl]

This works fine but gives no advantage over the situation where a lock file is written if the file is being used. As soon as the lock file is not present, the shared filed can be used. All users must first save before writing. Do not underestimate the effect of sharing on the write time accross a network.

 

[VBAjedi]

The advantages my approach would have would include:
- no timer running to bog your system down
- Book1 would not have to be shared (avoiding the slowdown filesharing often causes across a network).
- The user does not have to save or refresh Book2 before requesting their reservation, even if another user has submitted a change to Book1 since the user's last refresh. The code would just check to see if the desired slot is still open.

Skip, have you ever tried an approach like what I outlined in my first reply?

VBAjedi

 

[SkipVought]

VBA

I have not. Try to avoid such situations.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tbl]

I'm not suggesting using a timer, just a lock file. When another user wants to access the file they just save first and access. If the file is in use , which is fairly unlikely in this specific situation, then they just wait a few moments. I only use a timer when rapid response is essential and then only with 2 users.

Richard

 

[VBAjedi]

The reason I'm curious is that in my opinion the difficulties raised by trying to allow multiple users to access the same Excel data source are far and away Excel's biggest weakness. I've written 30+ major spreadsheet tools for my company in the last nine months. It's a tossup as to whether it's a bigger headache to try to get twenty users to use the same copy of a tool, or synchronize twenty copies of that tool! Multiply that by 30 tools and 20+ corporate locations, and you have a SERIOUS handful.

<SIGH>

That's a big part of the reason I'm learning MySQL/PHP/Apache/HTML right now. Out of the frying pan, into the fire!

LOL



VBAjedi

 

[menstroy]

Ok, Let me tell the details of the project and then we can all throw ideas around again, heheh

Current System: Paper callendar in binder at 1 persons desk that shedules 6 conference rooms

Proposed: Electronic scheduler where users can see all conference rooms for a given date and see what is free.

Idea: Excel sheet 1-File per each month withen that file have 30tabs for each day of the month, On left side of screen ahve the hours, and top have 6 collums for each conference room

Problem: Can be opened by several users at once. What is one user opens file at 8am and doesnt close it till 3pm? What if two users attempt to take same slot at a given time? Due to the size of our company 500employees and only 15-20 of them making reservations this would be very unlikely but possible

 

[VBAjedi]

I still think the two-files approach is worth investigating if you want to really do it right, but:

A quick workaround would be to tell your 15-20 users that they will only be allowed to open the workbook long enough to make/save a reservation. Presumably these will be managers that have enough intelligence to understand why they can't monopolize a file that everyone needs access to. Don't share the workbook (file sharing causes problems in a case like this!). Use the Workbook.Open event to execute an OnTime (Now plus 4 minutes) statement, which in turn runs a sub 4 minutes after the user opens the workbook telling them they need to save and close the workbook now (or, if you want to play hardball and be unpopular, does it for them).

Simple, but will occasionally cause other users to wait five minutes to access the workbook.

VBAjedi

 

[lucrahouse]

Purhaps the two file system might be good idea. What if your file on your desktop were upon change to a cell, try to open the master file and copy the information to the proposed cell. If file was in use, then the message could be file in use. Otherwise, if it found the corresponding cell not empty then it would fail and return a message saying conference room reserved for that time and then close the master file.