PDA

View Full Version : Macro to display who has Spreadsheet open in 'Read Write' mode?



Wonker
10-24-2007, 09:31 AM
Hello,

When you have a spreadsheet opened in 'Read Only' mode (because someone got there before you!) is it possible to display the name of the person (or machine) that has the spreadsheet open in Write mode?

I know the name appears when the spreadsheet first opens, before the user clicks Read-only or Notify but what code would I need so that they just could click a button at any time?

Many thanks in advance

Paul Williams

Oorang
10-24-2007, 04:00 PM
Hi Paul
Welcome to the board :) Let me preface my answer a little. Excel is not a database, so it's multi user features, while available are limited. Generally the best approach to a multiuser problem is to put a password to edit the file. Anyone can view but only a select few can edit. If you still need to allow multiple people to make an edit, you may condsider sharing the workbook (from the tools menu). This will allow several people to make edits at once and will enable tracking features. However you might still encounter conflict resolution issues when two users try to edit the same cell. All in all if you need to allow multiple users it is generally best to consider a database solution (Ala Access). With all that as a background you can only access some information. But there are a few cavets: First make sure you test this to understand if it is giving you the last user, or the last editor. Second it is only going to display the name Excel was registered to (Tools>Options>General>UserName). Lastly I have not tested this on 2007, so use at your own risk.
Overall it's my personal opinion that it is easier to control who can get into edit mode than to try and react after the fact. For what it's worth:)


Option Explicit
Sub Test()
MsgBox fnLastUser(ThisWorkbook.FullName)
End Sub
Function fnLastUser(path As String) As String
'Not written by me, found in post by ahammar
Dim text As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer

strFlag1 = Chr(0) & Chr(0)
strflag2 = Chr(32) & Chr(32)

Open path For Binary As #1
text = Space(LOF(1))
Get 1, , text
Close #1
j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
fnLastUser = Mid(text, i, j - i)
End Function

Wonker
10-25-2007, 03:21 AM
Many thanks for the reply but in my version all this does is display the name of the person who 'originally' had it open, not the last person to have it in 'Update' mode. I think I have convinced the users that this button is overkill as they should pay attention to the name that is displayed when they first open the spreadsheet.
To give you some background on what I am doing, the spreadsheet is only being used to upload and download data to/from an Oracle Database, the spreadsheet itself is never really saved with any data (even if they did it would be overwritten the next time it was opened as it simply gets the data from the database based on input parameters) but even though there is no problem with several users getting data from the Oracle database I could not have more than person 'Updating' the database at the same time so I have disabled the 'upload' button if the spreadsheet has been opened in Read-Only mode. This button then becomes active if the orignal user logs out and the 'Write' option is selected, I just thought it would be a nice 'feature' to have a button to show who has currently got the spreadsheet open in 'Write' mode and not the person who originally opened it.

Thanks anyway


Paul Williams

Oorang
10-25-2007, 07:34 AM
OK I thought of cheat. You will need to set a reference to the Microsoft Scripting Runtime for this code to work. What is does:Everytime the workbook is opened in write mode, it will store the user id to be accessed by anyone. When the file is closed it removes the info. Option Explicit

Private Sub Workbook_Open()
Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim strFilePath As String
If Not ThisWorkbook.ReadOnly Then
Set fso = New Scripting.FileSystemObject
strFilePath = fso.BuildPath(ThisWorkbook.Path, "info.dat")
If fso.FileExists(strFilePath) Then
VBA.SetAttr strFilePath, vbNormal
fso.DeleteFile strFilePath, True
End If
Set ts = fso.CreateTextFile(strFilePath, True, True)
ts.Write VBA.Environ$("USERNAME") & ": " & VBA.Now
ts.Close
VBA.SetAttr strFilePath, vbReadOnly Or vbSystem Or vbHidden
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim strFilePath As String
If Not ThisWorkbook.ReadOnly Then
Set fso = New Scripting.FileSystemObject
strFilePath = fso.BuildPath(ThisWorkbook.Path, "info.dat")
If fso.FileExists(strFilePath) Then
VBA.SetAttr strFilePath, vbNormal
fso.DeleteFile strFilePath, True
End If
End If
End Sub

Public Sub WhoHasIt()
Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim strFilePath As String
Set fso = New Scripting.FileSystemObject
strFilePath = fso.BuildPath(ThisWorkbook.Path, "info.dat")
If fso.FileExists(strFilePath) Then
Set ts = fso.OpenTextFile(strFilePath, ForReading, False, TristateUseDefault)
MsgBox ts.ReadAll
ts.Close
Else
MsgBox "Unknown"
End If
End Sub