Writing to another workbook

Periodically when a cell is updated I need whichever of the 10 to 20 satellite workbooks to post three cells to the receptionist's workbook.

The receptionist's workbook is normally open, but if it is not, I have included code to test and open it.

Because we are only posting three cells I expect the action to be very fast (I hope).

The receptionist never will alter any cell in the receptionist workbook, only view.

Receptionist normally will have receptionist workbook open when a satellite workbook must activate it and write to it.

Because of the relative lack of frequency, the chances of more than one satellite workbook needing to access and write to the receptionist workbook are remote, but it is possible (Over the course of a full day, perhaps a total of 200 writes to the receptionist notebook are expected).

What problems can I anticipate and program to avoid (and how if you can!)?

Hi,
If I understand right, the receptionist do not need read/write access. Lock files for editing by password and let the macro code to access receptionist file anytime. I think it should work. Unfortunately I cannot try it, have just one PC.


cheers,

Tomas

I also only have a single user system. I think Bruce's concern is not from the receptionist, but from a possible race condition when two satellites attempt to update the receptionist simultaneously.

One way to possibly reduce collisions (it might make things worse) is for the satellites to attempt to open and lock another Excel file prior to accessing the receptionist Excel file. The other Excel file would just serve as a locking mechanism.

Pseudo Code

While NOT able to open and lock file XYZ
  wait 200 milliseconds
While End

Access and update the Receptionist file

Close file XYZ

You could test file XYZ with code similar to (actual working and tested code on Vista 32 bit single user system using Excel 2003):

Function IsFileOpen(sPathAndFileName As String)
  'This determines whether a file is open
  
  Dim iFileNumber As Integer
  Dim iError As Integer

  On Error Resume Next       ' Turn error checking off.
  iFileNumber = FreeFile()   ' Get a free file number.
  
  'Attempt to open the file and lock it.
  Open sPathAndFileName For Input Lock Read As #iFileNumber
  Close iFileNumber          ' Close the file.
  iError = Err               ' Save the error number that occurred.
  On Error GoTo 0            ' Turn error checking back on.

  ' Check to see which error occurred.
  Select Case iError

    ' No error occurred.
    ' File is NOT already open by another user.
    Case 0
      IsFileOpen = False

    ' Error number for "Permission Denied."
    ' File is already opened by another user.
    Case 70
      IsFileOpen = True

    ' Another error occurred.
    Case Else
        Error iError
        
  End Select

End Function

Lewis

Thank-you.

I will experiment and report progress.

Seems strange that this kind of thing has not been done by others, but then it seemed strange all the trouble I ran into trying to work with linking in the same basic model...