Determining number of users for Office 365 shared documents

[Szczesiu]

Hi,

In our company we are using shared spreadsheets featuring a PowerQuery external DB connection.

Generally, spreadsheets and query work OK, but... sometimes when one of the users refreshes the query to get latest updates from the DB, all other users are informed, that changes made to the file that cannot be integrated into their shared copy and they need to reopen the workbook.

To avoid such situations I wanted to drop updating the query manually, and refresh it every time when the last user closes his / her workbook (so at the end of the day, effectively), by using sub:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
in connection with property:
ActiveWorkbook.UserStatus
to determine, if number of active users is still > 1.

The problem is that number of users is always reported as 1, no matter how many people are actually active inside the workbook in the same time.
Also ActiveWorkbook.MultiUserEditing property returns "False" - so it looks like the workbook is opened in exclusive mode.

I would be grateful if you could point me to any direction that would let me to resolve this problem.
In the meantime I have asked our IT department, if they are not blocking information like the number of users etc., but I was informed that no such information is kept secret.

[TMS]

I think you have misunderstood the .UserStatus property.

See: https://docs.microsoft.com/en-us/off...ook.userstatus

Workbook.UserStatus property (Excel)

Returns a 1-based, two-dimensional array that provides information about each user who has the workbook open as a shared list. Read-only Variant.

Syntax
expression.UserStatus
expression A variable that represents a Workbook object.

Remarks
The first element of the second dimension is the name of the user, the second element is the date and time when the user last opened the workbook, and the third element is a number indicating the type of list (1 indicates exclusive, and 2 indicates shared).

The UserStatus property doesn't return information about users who have the specified workbook open as read-only.

Example
This example creates a new workbook and inserts into it information about all users who have the active workbook open as a shared list.


users = ActiveWorkbook.UserStatus
With Workbooks.Add.Sheets(1)
For row = 1 To UBound(users, 1)
.Cells(row, 1) = users(row, 1)
.Cells(row, 2) = users(row, 2)
Select Case users(row, 3)
Case 1
.Cells(row, 3).Value = "Exclusive"
Case 2
.Cells(row, 3).Value = "Shared"
End Select
Next
End With

I think you need to check UBound(users, 1) for a value greater than 1.

[Szczesiu]

Thanks, sorry that I haven't attached a sample of code I have used, it could've been misleading.

Well in fact this - looking for UBound(users, 1) - is excactly what I did in the first time, using some variations of the above procedure.
I was also looking for a workbook status itself with a sub like the below one:

Please Login or Register  to view this content.

And of course the above sub shows "Exclusive" as the result.

In turn in the Microsoft tutorial for the Workbook.MultiUserEditing property:
https://docs.microsoft.com/en-us/off...ltiuserediting
in the "Example" section you can read that "This example determines whether the active workbook is open in exclusive mode. If it is, the example saves the workbook as a shared list."

Does it mean that workbook needs to be specially saved as shared one (for that property to be working correctly)? What I mean is if it needs any special "shared" trigger in "Options" to be active during saving? We are already sharing the file in Sharepoint and there is already more than one person actively co-editing it, I assumed that is what makes the document to be considered as "shared".