Readonly issues

hatman

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
I did what you are never ever supposed to do: I mixed my Data in with my Application.

What started out as a quick and dirty tool for me and a couple of co-workers 2 years ago, has evolved into a massive VBA heavy application that simply has a bunch of dumb data sitting on a series of sheets. Now, upper management wants this thing to be opened up for use by the entire department... several dozen people at present, possibly as many as several hundred in the near future.

The data needs to be maintainable by all of the users. Currently, when a user initiates a database maintanence activity, the macro checks to see if the workbook is ReadOnly, if it is, then a message pops up telling the user to close and re-open in Write Mode. If the workbook IS in write-mode, then the new data is written to a sheet, and the file is saved (this is the ONLY access the user has to actually save this file). This works fine for the 2 users who play with this now... but not so much with many more possibly simultaneous users. (and don't forget the people who open the file at 7:00am, and leave it open at their desktop until 6:00 pm while they are in meetings all day)

My initial thought was to simply have everyone open the file as readonly... then on the rare occassion that someone makes a change to the database, simply change the FileAccess, save the file, and change it back. Probably generate a dummy text file in the same folder for the period of time while the file is not readonly, to prevent another user from trying access it at the same time. I have the whole system all planne dout for manageing save requests... what I can't seem to be able to do is change a Readonly file to Write. I have tried using SetAttr, ChangeFileAccess, and the FileSytemObject unsuccessfully. I think that the FileSystemObject has the most potential... but I simply don't have enough command of it's usage to make this thing happen.

Moving the data into an Access or Oracle database is not a viable option at this point. Does anyone have any other advice?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Saw your comments in that other RO post.

I've never really had a problem with the ChangeFileAccess method, but I'm only dealing with a handful of users (20 max) when using that method. The only real hiccups I've ever run into with it have been with a Shared wb, but they have enough problems as it is that I try to avoid them altogether. How aren't the methods you've tried failing?

From the scope of this it almost sounds like you'd be better off building a true Web application with a database back-end. I'm actually doing something like that myself.

Barring that can you build an intermediate wb? One that acts as an interface for the true wb and transfers data when needed?

Smitty
 
Upvote 0
Saw your comments in that other RO post.

I've never really had a problem with the ChangeFileAccess method, but I'm only dealing with a handful of users (20 max) when using that method. The only real hiccups I've ever run into with it have been with a Shared wb, but they have enough problems as it is that I try to avoid them altogether.
Click to expand...

Yeah, I also try to avoid the Shared Workbook method... for the same reasons.

How aren't the methods you've tried failing?
Click to expand...

Code: 
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
Yields a message box "Do you want to save changes before switching file status?"

Code: 
ThisWorkbook.ChangeFileAccess Mode:=xlReadWrite
yields a message box to the effect of "The file should be opened read only, do you want to open as read only?" (probably because I saved the file to be opened readonly).

From the scope of this it almost sounds like you'd be better off building a true Web application with a database back-end. I'm actually doing something like that myself.

Barring that can you build an intermediate wb? One that acts as an interface for the true wb and transfers data when needed?

Smitty
Click to expand...

Oh, I would love to go back in time and rebuild this with a separate front end. Not realistic at this point, however. Besides, I'm a lowly Mechanical Test Engineer who is doing programming in his free time... they don't actually give me resources to do any of this stuff. If I can't develope, test, distribute, and administrate with standard desk-top tools, I am not allowed to do it.

I think I finally found a combination of methods that satisfies me. I set the File Attribute to Readonly through Windows Explorer, thus suppressing all dialogs during the open sequence. Then implement the following code to save (obviously, I still have to build the part that checks to see if anyone else has already toggled the readonly attribute, to prevent multiple users from trying to save at the same instanr) See what you think...

Code: 
Const READ_ONLY = 1

Sub stuff4()

        Set fs = CreateObject("Scripting.FileSystemObject")
        Set fle = fs.getfile(ThisWorkbook.FullName)
        
        fle.Attributes = fle.Attributes - READ_ONLY
        
        ThisWorkbook.ChangeFileAccess xlReadWrite
        
        ThisWorkbook.Save
        
        ThisWorkbook.ChangeFileAccess xlReadOnly
        
        fle.Attributes = fle.Attributes + READ_ONLY
        
        Set fle = Nothing
        Set fs = Nothing

End Sub
 
Upvote 0
Code:
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
Yields a message box "Do you want to save changes before switching file status?"

Code:
ThisWorkbook.ChangeFileAccess Mode:=xlReadWrite

yields a message box to the effect of "The file should be opened read only, do you want to open as read only?" (probably because I saved the file to be opened readonly).
Click to expand...

Won't DisplayAlerts = False suppress those messages?

Smitty
 
Upvote 0
That's what I thought. But, no, for whatever reason, it didn't. Not sure why.

As I was driving home last night, I did some thinking about my code above, and some of the apparent oddities I have seen with these methods. Correct me if I am wrong: there are actually 2 separate switches for readonly. 1) The File Attribute, set through Explorer, SetAttr, or the Filesystem object, and 2) The Workbook Property, set through ChangeFileAccess, or SaveAs->Readonly. If this is correct, then this starts to make a little more sense to me, and explains some of the error messages I was getting.
 
Upvote 0
Deeper and Deeper.

Code: 
ThisWorkbook.ChangeFileAccess Mode:=xlReadWrite

Will determine if the file has changed on the disk (by another user) since this copy was opened. If yes, then Excel pops up and asks if you want to SaveAs, Discard your changes (reloading the latest file version from the disk), or cancel. Disabling the alerts will cause the default of Cancel to be selected, which causes problems, as the RIGHT answer is Discard. I can prevent this most easily by first doing a
Code: 
ThisWorkbook.UpdateFromFile

Problem is that as soon as the update occurs, you have effectively closed and opened the workbook, which causes the macro to halt in mid-execution. I figured I could get around this by making an entry in the registry prior to the above command, effectively recording which routine needs to be run when the file is re-opened... then simply plop some code in the Open event to see if a registry entry exists in that location, and run the code that's indicated. In this way, the user would see an effectively uninterrupted lauch into the routine they chose. Problem is that this method does NOT fire the open event.

This has become way more complicated than it should... and I am no closer to a viable solution. Your recommendation of gutting and rebuilding with a separate web-based front end is looking ever more attractive... :(
 
Upvote 0
Your recommendation of gutting and rebuilding with a separate web-based front end is looking ever more attractive...
Click to expand...

I'm not entirely thrilled with the learning curve on this one, but my wife's a web programmer, so she's pointing me in the right directions.

Smitty
 
Upvote 0
[quoteI'm not entirely thrilled with the learning curve on this one, but my wife's a web programmer, so she's pointing me in the right directions.

Smitty[/quote]

Yeah, that scares me. I have VERY little web programming experience or background, and I'm not certain that I can even get my GUI to work in a web-based environment.

I think I will take the middle ground: An Excel front end that consists of a Readonly Workbook containing only the macros and the GUI, with a second Excel Workbook that simply contains the data sheets. Then I can open the data book in readonly mode for data mining, and lock the file when I need to write or edit data. And like a database, the data should be pretty fresh for all users.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Hiding Sheets during the Before_Close Event
2
Replies
10
Views
534
Jaafar Tribak
Jaafar Tribak
S
  • Solved
Change export from .txt to workbook
Replies
1
Views
155
HighAndWilder
H
E
  • Question
hyperlinks path changes without notice in a excel workbook
Replies
3
Views
377
jkpieterse
jkpieterse
S
  • Question
VBA import of an IQY file without prompt
Replies
4
Views
619
John_w
J
E
  • Question
Old XLSM files crashing Excel; looking for help to troubleshoot, please
Replies
9
Views
1K
jkpieterse
jkpieterse

Forum statistics

Threads
1,217,391
Messages
6,136,324
Members
450,005
Latest member
BigPaws

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top