PDA

View Full Version : Solved: Forcing a spreadsheet with a read only attribute to reopen as read/write



dragon
01-13-2007, 03:03 PM
Hi Guys

I'm designing an electronic filing system which creates an Excel spreadsheet (lets call it "List Directory") listing all the files in various folders and need to limit how long an individual user would have the List Directory open as read/write.
:think:
I am thinking of setting the attribute for the List Directory as read only, but need to establish if there is a way to force it to re-open as read/write in VBA so you can edit it if you are already in the spreadsheet.

If so, once you've finished editing it, can you then reset the read only attribute and how would you do it?

I would appreciate any efforts by members distilling their thoughts on the matter.

Thanks

gnod
01-13-2007, 07:17 PM
excel has a save option to provide a password to open or modify.. in the File -> Save As dialog box, there's a menu name "Tools".. click the drop down then General Options..

maybe it will help :think:

tstom
01-14-2007, 01:43 AM
I don't know if this will work for you or not....

Function SetReadOnlyFileAttributeAndFileAccess(ReadOnly As Boolean)
Dim f As Object
Application.DisplayAlerts = False
Set f = CreateObject("Scripting.FileSystemObject").GetFile(ThisWorkbook.FullName)
If ReadOnly Then
If f.Attributes And 1 Then f.Attributes = f.Attributes - 1
If Not ThisWorkbook.ReadOnly Then ThisWorkbook.ChangeFileAccess xlReadOnly
Else
If Not f.Attributes And 1 Then f.Attributes = f.Attributes + 1
If ThisWorkbook.ReadOnly Then ThisWorkbook.ChangeFileAccess xlReadWrite
End If
End Function

dragon
01-14-2007, 08:40 AM
gnod - Thanks but I know about the password to open/modify using File-> Save As dialog box.

Cannot use that method for what I am trying to achieve. I need the file attribute for list Directory to be set as read only. Once someone has opened this file, they can click a command button to edit the file, VBA would reset the file attribute to read/write, bring up a form to fill out, once form is filled and user clicks OK, spreadsheet would be updated & VBA would save the file and reset it's attribute back to read only before re-opening it.

Basically, List Directory will be the backbone of a multi-user networked filing system and need to control how is opened. I cannot trust the users judgement about whether they should open it as read only/modify, therefore cannot do it the way suggested.

tstom - Your suggestion seems along the right lines but need to wait till I get to work tomorrow to test it.

Will let you know what happens.

Cheers

dragon
01-15-2007, 04:07 AM
Hi Guys

Have tested tstom's code - and it works perfectly. However I may need to re-think my approach and come back with other questions.

Thanks a lot