Write Protection Of Data

  • Dear Excel Wizes!


    I have a problem that I think I might have a solution for but I wanted to check with you guys first.


    The problem:
    Multiple users are accesing the same spread sheet and are not satisfied with the limitations of the Excel share document function. (Links can not be created etc.)


    Solution??
    I save all the data in Excel workbook "A". Then I start a new workbook "B" and make a user form with a nice GUI. Workbook "B" will then access data from workbook "A" (I do this because I need to have Workbook "B" as fast and possible and "clean" as possible).


    My plan is thus to store local versions of Workbook "B" on different computers and have them contact Workbook "A" to store and load data. Kind of like a database. I plan to simply put a flag when a row is loaded to prevent it from being tampered with by another user. For example:


    Adam is loading data from row number 12.
    - I put a flag in the code saying that row number 12 is being accessed.
    A couple of minutes later Christen is trying to load data from row number 12 and than gets a message that she cannot store any data on row number 12 at the moment.


    Now, I guess that there must exist functions like this in VBA, maybe using semaphores or something, in that case, does anybody know how to use them. Or do you recon that my way will work satisfactory?


    I mean, I guess it could work but I don't know what will happen when to users try to access the row at the same time, before the flag indicates that it is being accessed?


    I also see a problem if a user wants to delete a row with a lower number. For example:


    Adam is accessing row number 12.
    Christen wants to delete the entire row number 10.
    When Adam is done with his work and wants to save the data again, row no.12 is now row number 11 since Christen deleted row number 10. How can I overcome that problem?


    Puh... A lot of info here but I would be really greatful for any answers! Do I have to involve a database, like SQL or Access or can I solve this with pure Excel VBA?


    Thanx guys!


    /Fredrik

  • Re: Write Protection Of Data


    How can you have mulitple people reading and writing to the same file? won't it be read-only when the second user tries to access this data?


    I think your going to have permisson problems here.


    Or are you just opening>loading data> then closing?

  • Re: Write Protection Of Data


    Quote from gmccreedy

    How can you have mulitple people reading and writing to the same file? won't it be read-only when the second user tries to access this data?


    I think your going to have permisson problems here.


    Or are you just opening>loading data> then closing?


    Hi Gmccreedy,


    Thanks for your reply.
    My thought is that I would never even open workbook "A". I would only load variables from it. Is that possible?


    /Fred

  • Re: Write Protection Of Data


    You can load info from a file without opening...there is a "workaround" on this site. Just do a search.


    However, I do not think you can import information back to a closed file. It has to be opened at some point.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!