• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Update and save data in a Read Only Excel

amit6488

New Member
Hi,

I have created a Master sheet which extracts data from another excel(database) and if required, make changes to the retrieved data and update the excel (database) with the new information.

This Master sheet along with the Excel(database) will be used by multiple users and there is a possibility that the Excel (database) is open at one of the users system and when another user tries to update information, the VBA code will give an error as the database is already open with some other user.

The challenge I want to overcome: Is there a way or code that can allow the user to open the database in Read Only mode, update the new information and save it to the database (somehow by changing the file read only mode to Read Write mode) and then close the database. all this happens when the another user with whom the database was opened originally is still working on it and does not impacts his/her work.

I came across a code (see below) but somehow it is not working. But, as per the user who posted it in a forum, this code works properly for him.

Code:
Activeworkbook. ChangeFileAccess Mode:=xlReadWrite

         MsgBox "Run My Code Here"
   
         Activeworkbook.Save
   
    Activeworkbook.ChangeFileAccess Mode:=xlReadOnly
 
Last edited by a moderator:

vletm

Excel Ninja
amit6488
Some thoughts:
eg with cell B11.
If someone has modified that cell
and You will edit and save same cell then cell's value will be Yours
... and if that someone will save next then cell's value will be someones.
If someone still modify that cell
then You try to modify same cell then there will be a challenge.

If someone and You are modifying different cells in same time, then it could work (depends of 'sharing of file' or so),
means both can save own modifications.

If Your above code works properly for him, then there are something else too.
 

amit6488

New Member
amit6488
Some thoughts:
eg with cell B11.
If someone has modified that cell
and You will edit and save same cell then cell's value will be Yours
... and if that someone will save next then cell's value will be someones.
If someone still modify that cell
then You try to modify same cell then there will be a challenge.

If someone and You are modifying different cells in same time, then it could work (depends of 'sharing of file' or so),
means both can save own modifications.

If Your above code works properly for him, then there are something else too.
Hi Vletm,

Thanks for the response and sharing the pros and cons. After doing some analysis i dropped this idea temporarily and created a workaround for this situation (multiple users accessing at the same time).

What I observed is that in my case majority of the times when multiple users would be working on the same time, they would be looking to retrieve the information from the master sheet (which is the database). So I created a code (well not really created as I have no knowledge in coding, but found related codes via google) which would prompt the user for keyword or IDs which they are looking for and the code will open a new excel, save it on their desktop with a predefined filename and retrieve all the data pertaining to the user input from the master sheet.

:) :cool:
 
Top