+ Reply to Thread
Results 1 to 3 of 3

Determining number of users for Office 365 shared documents

  1. #1
    Registered User
    Join Date
    01-08-2016
    Location
    Poland
    MS-Off Ver
    2010, Office 365
    Posts
    11

    Determining number of users for Office 365 shared documents

    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.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,342

    Re: Determining number of users for Office 365 shared documents

    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.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-08-2016
    Location
    Poland
    MS-Off Ver
    2010, Office 365
    Posts
    11

    Re: Determining number of users for Office 365 shared documents

    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".
    Last edited by Szczesiu; 06-23-2022 at 11:54 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Shared Excel documents
    By elizad3711 in forum Excel General
    Replies: 1
    Last Post: 08-16-2017, 05:09 PM
  2. Replies: 3
    Last Post: 04-05-2016, 11:41 AM
  3. Documents and Settings All Users Documents - Access is denied
    By peterrc in forum Microsoft Windows Help
    Replies: 8
    Last Post: 01-26-2014, 06:30 AM
  4. [SOLVED] Determining frequency of particular cell color across multiple documents
    By mradoc in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-21-2014, 07:25 PM
  5. Shared Documents - General
    By Lanz Cabrera in forum Excel General
    Replies: 0
    Last Post: 09-28-2012, 02:23 PM
  6. Replies: 0
    Last Post: 01-12-2006, 03:35 PM
  7. Office 2003 documents readable by Office 2002 applications?
    By Captain Infinity in forum Excel General
    Replies: 2
    Last Post: 05-10-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1