Save Excel 2003 when being opened as Read Only at the same time

Posted on 2011-05-10
Last Modified: 2012-05-11
Dear Experts,

I have a specific request which needs to make use of the Excel as multiple user tool, even though it is not good at.
I have a Search file which will open another one File_X.xls as Read only via vb script
Code: (For i = 1 To .FoundFiles.Count
                   Set oBook = Workbooks.Open(.FoundFiles(i), ReadOnly:=True)
            Next i)
File_X.xls is opened from UserA and then from UserB.
UserA is allowed to make some changes and via vba I store some information into database and then I need to save also the File_X.xls. The code for saving is simple: (ActiveWorkbook.Save)

Because the UserB has it open, UserA wanted to save gets a message: (A file named 'File_X.xls' already exists in this location. Do you want to replace it?)
When UserA clicks Yes option, it seems it has saved it. When re-opening, even after UserB has closed it, the change on the File_X.xls is not present.

I hope to have made it as clear as possible. A soon solution (preferably in VB) is appresciated.
Thanks in advance!
Question by:Soni83
    LVL 5

    Accepted Solution


    I don't think you cannot save a readonly file using the same name in the same location. suggestion to try saveas instead.

    Author Comment

    Hi dr_shivan,
    Thanks for your comment but I have done a test. If you create a empty excel file and open from two different users at same time. UserA will open first, UserB will get the notification that file is in use and he would choose open as ReadOnly. When UserA saves the changes and closes the file, the changes are saved.

    What I understand the difference of this test with my situation is that if you explicitely choose to open read-only when file in use, it saves the changes. In my case it has been open prior as read-only.

    Please other suggestions are welcomed!

    Assisted Solution

    Hi dr_shivan,

    I must apologise for my above statement. I just realised that I missed something. UserA on my test had opened on Edit mode.

    I tried to do SaveAs but it was important for me to save with same name. It didn't solve the issue.

    For the ones that would later want to know how I solved this here is my workaround:
    From the Search file I found first if the person is allowed to change(in relation with what we authorised him in Database), then for that person open as ReadOnly:=False, for the other ones is ReadOnly:=True

    Author Closing Comment

    The reply from dr_shivan triggered me to think of a workaround. His reply was not complete solution but is true what he says, therefore multiple accpeted solutions.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now