Solved: UserStatus,GetObject and Application UserName

[stanl]

I'm a little confused over the data displayed by the Userstatus property of a workbook. Assuming that in addition to knowing whether or not a workbook is in use, I would like to know if it is being shared, I use GetObject(path\workbookname). If in the general section of the Application properties the User Name is set to Tom, and I log in as Stan and perform

oWkb = GetObject("workbook on Tom's PC")

Then iterate the array returned by oWkb.UserStatus - whether or not the workbook is actually in use, I get two entries for Tom,[current date/time],indicator whether exclusive of shared]. I assumed that if the workbook was not in use, I would get a single entry for Stan. Unless I am making a fundamental error, UserStatus is not very helpful:banghead:

Stan

[malik641]

I guess you could test it if you get an error using this code (btw, haven't tested when sharing the workbook...I think it'll work):

Sub Test()
On Error Resume Next
ThisWorkbook.ExclusiveAccess
If Err Then
MsgBox "Not Shared"
Else
MsgBox "Shared"
End If
End Sub
I hope this helps. I'm not sure if its exactly what you need.

[stanl]

I hope this helps. I'm not sure if its exactly what you need.

Not really. GetObject() returns a workbook not an application object in my case, and ExclusiveAccess brings up a bunch of prompts and warnings that require displayalerts=False - could be referenced as oWkb.Parent... but that is a hassle for an error. However I did discover that the MultiUserEditing property gets what I need. Thanks.

Stan

[Ken Puls]

Hi Stan,

Are you just trying to get a list of who is using the file? You might try the following:

Sub WhoIsSharing()
Dim wbShared As Workbook
Dim lUsers As Long
Dim aryUsers() As Variant
Dim sUsers As String

'Set the workbook name here
Set wbShared = Workbooks("sharetest.xls")

With wbShared
aryUsers() = wbShared.UserStatus
For lUsers = 1 To UBound(aryUsers())
sUsers = sUsers & aryUsers(lUsers, 1) & vbNewLine
Next lUsers
MsgBox "The following people are using your workbook:" & vbNewLine & _
sUsers, vbInformation + vbOKOnly, "Active Users"
End With
End Sub

I'm not sure what you really meant by this:

If in the general section of the Application properties the User Name is set to Tom, and I log in as Stan and perform
I believe that the username is controlled by the Application.Username of the user who opens the file though...

[stanl]

I believe that the username is controlled by the Application.Username of the user who opens the file though...

Ken;

I have no problem displaying users, but hadn't really tested my script on a network drive with a shared workbook. What I was trying to ascertain is the username associated with GetObject(). It is clearer now.
Stan

[Ken Puls]

Hi Stan,

I can't say that I've ever tried using GetObject to return a username. :)

For the record, my test was done using a file on our network drive.

Cheers,

[stanl]

I can't say that I've ever tried using GetObject to return a username. :)


Neither do I, so I don't understand the comment. I return the following in my user function:



ArrayFilePutCsv(cOut,oWkb.Parent.WorksheetFunction.TransPose(oWkb.UserStatu s), ",",1,2)


which essentially gives me the user list I need. My initial question concerned dis-associating the username returned by the GetObject() workbook from a valid user that might have the workbook open, so I erroneously assumed that user might reflect my login name and not the username for the application object on the computer the script ran on.

better.....:friends: