mutual exclusive write to Excel file

hi, im having 2 Excel files, one is the database file and the other is a user interface file. Users will then use the interface file to input data and click save, and then the interface file will save the data into the database file.

i need to a way to ensure that there is no concurrent writing to the database file, is there a way to have mutual exclusive access? or perphaps a way to catch the error if the file is already opened?

For example, if the adminstrator has directly opened the database file (without using the interface file), any user who clicks save will then be prompted that the file is being used. Or if the 2 users are writing at the same time then only one of them will be granted access.

im currently using this code to read and write from Excel files,

Set xlapp = New Excel.Application
Set wb = xlapp.Workbooks.Open({FileName}, 2, False, 5, _
{password}, {password})

and with this method, if the administrator has directly opened the database file, as soon as the user click save, a new copy of the database file will be created and saved in the Excel default file location, which then the actual database file will not be updated.

i do not wish to use ms access because it can be very slow.

can anyone help me out? Thank you.

Hi dominicb, thank you so much for your help. Indeed, the code helped me a lot, however, the code does not seems to work if i have set a password to read the Excel file, but it works fine with password to write though. i presume it is encrypted in some way?

i wouldnt mind hard coding the password, would u be able to help me further on this? thank you

FYI, i had tested the shared workbook function, i didnt even know this function existed, thank you so much once again.

however, it will not work for my situation because each save to the database is a new appended row in Excel, and therefore there is a chance that one user will overwrite another user's record, e.g. overwrite the same row.

im also aware that Excel is not an appropriate software for database, however, our database are light weighted, therefore it should be fine.

from our experience, Access seems to be lagging a little when the records reached around 9000 rows n above, n most of the times the retrieving can be slow (in comparison to retrieving from one Excel file to another Excel file).

thanks

Hi dominicb, not sure if it still helps, i was reading one of your threads about the checkbox1

there is a sub,

Private Sub init_userform()
End Sub

it is the first sub that a userform will execute before anything else