Override Read Only

[John Wilson]

Am wondering if it's possible to do this.
First let me give a reason why.
I have a master copy of an spreadsheet on my C: drive that no one can
get to but me. I keep a second copy on another drive shared by many.
There are times when it's necessary to update/overwrite the shared file.
It can become a task finding out who has it open and getting each
instance of the file shut down.

Is there a way to shut down all instances of a file when my access is
Read Only?
Is there a way to embed some code within the file that will allow me to
do it even if I have accessed it as Read Only?

Thanx,
John

[Vasant Nanavati]

Hi John:

The Toggle Read Only command will enable you to switch the ReadOnly status
of the file by prompting you for the password.

I'm not sure if this command is on the default File menu. If not, you will
need to go to View / Toolbars / Customize and then drag the command to the
menu of your choice.
--
Regards,

Vasant.

"John Wilson" <jwi...@optonline.net> wrote in message
news:3C339E56...@optonline.net...

[John Wilson]

Vasant,

I guess I didn't explain myself clearly enough.

I have a file on a LAN/shared drive/whatever.
You already have it open.
I can open it too, but my only choice is "Read Only"
Once I do open it (as "Read Only"), I want to be able to force your "instance"
of it to close (either by a command or something (macro) embedded in the file
that I can call.

From a development standpoint...
I want all "instances" of the file closed so that I can overwrite it.

From a user standpoint....
You'll discover that you no longer have the file open, but when you try to
access it again, you'll have my updated version.

You mentioned "Toggle/Read Only". Is there a VBA command that can do this?
i.e. You already have the file open. I access it also (as "Read Only"). Can I
toggle my "instance" of it as "Read/Write" so that I can overwrite it?

Thanx,
John

[Vasant Nanavati]

Hi John:

Sorry for misunderstanding your question. I don't know of a way to shut down
other users' files, or to change them while they are open.

You may want to look at the ChangeFileAccess method in VBA to see if that is
at all helpful, but it's not going to allow you to do what you want.
--
Regards,

Vasant.

"John Wilson" <jwi...@optonline.net> wrote in message

news:3C33E199...@optonline.net...

[Robbie Armstrong]

John Wilson <jwi...@optonline.net> wrote in message news:<3C339E56...@optonline.net>...

John

I do not know how, but could you put a macro in your shared file that
reads a value in a second file every minute, and then shut down the
open shared file if the value is changed. Then you could change the
value in the second file to shut down everyone else. This is just a
thought.

Robbie

[Dave Peterson]

Another option would be to keep track of who has the file open. When the
workbook is opened, it writes a records to a common log file (network drive???)
and when they close the file, it writes another record that says the file is
closed.

Now your job of finding the current users just means look at the log file for
the people who have the workbook open.

(Snipped from a few previous posts. watchout for line wrap.)

Option Explicit

Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
Public Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA"
_
(ByVal lpBuffer As String, nSize As Long) As Long

Function OSUserName() As String
Dim Buffer As String * 256
Dim BuffLen As Long
BuffLen = 256
If GetUserName(Buffer, BuffLen) Then _
OSUserName = Left(Buffer, BuffLen - 1)
End Function

Function OSMachineName() As String
Dim Buffer As String * 256
Dim BuffLen As Long
Dim lngX As Long
Dim strCompName As String
BuffLen = 255
If GetComputerName(Buffer, BuffLen) Then _
OSMachineName = Left(Buffer, BuffLen)
End Function

In the ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
Dim FileNum As Long

FileNum = FreeFile
Open ThisWorkbook.Path & "\log.txt" For Append As #FileNum

Print #FileNum, ThisWorkbook.FullName & " OPENED: " & _
Format(Now, "yyyy-mm-dd hh:mm:ss") & " User: " & _
OSUserName & " from computer: " & OSMachineName
Close #FileNum
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim FileNum As Long

FileNum = FreeFile
Open ThisWorkbook.Path & "\log.txt" For Append As #FileNum

Print #FileNum, ThisWorkbook.FullName & " CLOSED: " & _
Format(Now, "yyyy-mm-dd hh:mm:ss") & " User: " & _
OSUserName & " from computer: " & OSMachineName
Close #FileNum

Cancel = False

End Sub

--

Dave Peterson
ec3...@msn.com

[John Wilson]

Vasant, Robbie, & Dave,

Thanks for the replies. Had been thinking along the lines of just shutting everyone
down at a particular time of day but I don't want to sacrifice performance in doing
so.
Dave's way looks like the easiest (and the hardest) to do, but probably the right
direction to go in. When I get some time, I'll try it. At the very least, it looks
like it may give me a way to find out just who has left it open.

Thanks again all,
John