• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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

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!
0
Soni83
Asked:
Soni83
  • 3
2 Solutions
 
dr_shivanCommented:
Soni83,

I don't think you cannot save a readonly file using the same name in the same location. suggestion to try saveas instead.
0
 
Soni83Author Commented:
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!
0
 
Soni83Author Commented:
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
0
 
Soni83Author Commented:
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now