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.
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.