PDA

View Full Version : Solved: Shared Workbook: Check for another file



Stargazer
10-05-2011, 09:03 AM
Hiya,

We have two workbooks that talk to each other. NOVA is the supervisor workbook that sends work out. SupaNova is the Agent workbooks (one per person) that receives the work and from which each agent works from.

To allow the SupaNova workbooks to be opened, have work pasted into them by NOVA and then saved and closed, they are all shared. As you all know already, shared workbooks are slightly imperfect and the agent will only see that more work has been provided to them if/when they save SupaNova themselves and the changes are synchronised.

An early version of our automated save code caused some problems when SupaNova tried to save itself while NOVA was still doing its allocating. Thus we have been seeking an improved method.

Our intention is to automate this so that the agent can send a request to the supervisor with the click of a button, and then trap itself in a cycle so the moment it becomes safe to do so, it will save itself only once NOVA says it has finished, the changes show for the agent and he/she can then resume work.

This is a brief outline of our process:

1) Agent clicks button on Userform to request work
2) SupaNova creates a 'Request For Work' (RFW) file on the network
3) SupaNova then tries to save itself
4) The BeforeSave event checks to see if the RFW file exists
5) If it does then save is cancelled
6) A RefreshCycle Sub is activated that keeps repeating the first 6 steps before reactivating the Cycle
7) When the NOVA sheet puts work into the shared SupaNova file, it activates a special clause in the SupaNova BeforeSave that allows the Supervisors PC to save the agent workbook.
8) SupaNova is closed on the Supervisor PC and then the RFW file is deleted.
9) by this time SupaNova is trying to save again like in Step 4.
10) Because the RFW file has gone, the save is successful on the Agent PC and the new work is visible immediately.
11) the cycle is broken and the workbook returns control back to the agent.

All of the above, we have working... Sort of. You see, it was all good, until I went to test it on a Shared version of the SupaNova sheet. It can create the RFW file, it can delete the RFW file, but everytime it goes to check the existence of the RFW file, it says "This file is locked. Please try again later."

This is our beginning of our code for checking a files existence:-




1: Dim CheckFSO
Dim File As String
File = NOVALocation & ThisWorkbook.Name 'NOVALocation already declared much earlier

Set CheckFSO = CreateObject("Scripting.FileSystemObject")
If CheckFSO.FileExists(File) Then
'Do a bunch of stuff
End If



Do you know why this would throw up the error message I was getting or do you know of another, maybe more efficient manner in which to check the existence of another file somewhere on the network without upsetting the workbook sharing?

Many thanks,

Rob.

Kenneth Hobs
10-05-2011, 09:42 AM
MsgBox Dir(NOVALocation & ThisWorkbook.Name)<>"" 'Exists if True

This might be helpful:
Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function

Stargazer
10-06-2011, 01:27 AM
Thank you for the code suggestions, however, I may have over simplified my definition of the RFW file.

We do name it after the Agent workbook, but it isn't a workbook in itself. Instead, it is an extensionless file with a name and nothing else. So it can't be opened or modified. It is there purely to be looked at and deleted.

So to get the Function to check instead of attempt an open, is it possible to say something along the lines of "If RFWFile Exists Then" and then add in some command code to the condition? The only way we know to do it is with the FileScriptingObject method and that appears to break once the workbook is shared.

Cheers,

Rob.



I liked the look of that Dir & File .Find so I turned it into an If statement and dropped it in. I figured it would be a softer, more passive file search than the FSO and would therefore be the ultimate answer. Alas, it returned the same message. I'm thinking that I may have to scrap the upgrade to the project and tell people to continue shrieking at each other from across the office.

If there are any other options, I'd love to hear them, but I fear the sad fact that Excel files were never meant to be shared is fast catching up with us.

Harrumph!

Stargazer
10-06-2011, 02:55 AM
Update:

I'm now convinced it is nothing to do with the file checking. I now believe it is my RefreshCycle that is kicking out the error. Here is the code I have been using.



Sub StopClock()

Application.EnableCancelKey = xlDisabled

' Cancels the OnTime event (stops the clock)
On Error Resume Next
Application.OnTime NextTick, "UpdateClock", , False
End Sub

Sub RefreshCycle()

Fudge = True 'Passes a boolean value to Userform to help bypass certain actions if not clicking the button yourself

Application.EnableCancelKey = xlDisabled

Call StopClock 'Tried it with this disabled and it made no difference

NextTick = Now + TimeValue("00:00:10")
Application.OnTime NextTick, "SaveNow"

End Sub


I'm at a total loss when it comes to working this out. I'm going to continue chucking message boxes into everywhere in the hopes I can pinpoint the exact moment it fails.

How I wish code was viewable while shared!

Stargazer
10-06-2011, 03:39 AM
It would appear thr answer is to remove ones Privates!

I discovered, after a great many message boxes, that I was being humbled by a simple ThisWorkbook.Save command.

By turning the BeforeSave sub in ThisWorkbook from a Private Sub to just a Sub, the problem has been eliminated.



Thanks for the help and thansk for another nice way to check a file exists.

Rob.

Aflatoon
10-07-2011, 05:36 AM
As you all know already, shared workbooks are slightly imperfect

I think that may win an award for understatement. 'prone to corruption, data loss, bloat and bizarre behaviour' might be a more accurate description.