How to check what users are in a Workbook on a shared drive?

I've tried to use the following two portions of code to check what users are in an Excel 2010 workbook. In the first section of code, if I'm the only one in the workbook, it will return me as the user. However, when I'm the exclusive user and there are others in the workbook, the code returns me as the only person in the workbook. Also, when there are others in the workbook and I don't have exclusive access, when the code hits the, "Users = aDataWbk.UserStatus" section of code, I get the error: "Run time error '1004'. Cannot access the read-only document 'Z:\Reporting\IBE Scoreboard2.xlsm'.

In the second section of code, I'm always returned as being the exclusive user of the workbook, regardless of how many people are in the workbook and who has exclusive access. Can anyone tell me what's going on? Please note, I posted this question yesterday morning in the Microsoft Answers forum and have gotten no responses. The link to that post is: http://answers.microsoft.com/en-us/o...a-d5eb2f5ec05d.

Code section 1:

Please Login or Register  to view this content.

Code section 2:

Please Login or Register  to view this content.

Any help would be greatly appreciated!!

Brian

Anyone? I've been struggling with this a lot. I thought that maybe I was calling the UserStatus array wrong, but I've tried a bunch of different things and can't get it to work.

Brian

Kyle,

Thanks for your reply! I tried both the API and VBA versions and got the same results (shown below.) The "gobbledy-gook" that you see is what came up in the message boxes, it's not an error of the HTML that you're seeing. Any idea what's going on? I'm running Office 2010 on Windows XP Pro 2002 SP3.

API Test Result:
C:\Documents and Settings\btorrea\Desktop\IBE Scoreboard2.xlsm is already Open
By �3c–$‹“Z�OglN�A�`!����9M�1~��Ę�gŒ%�*f��*�œ���D8‰_ƒ���S֍��œ…�$f
M�}>��8?��v�Œˆ��bH���s`�€��� �� �'���‹N����Q� 4���?GN�o:��j��u�*�mW�~��Z?v�fǶ�N��Q;��r#sw��›_��=Mƒ���~�VӲZA�z��'ž�‰G�|�‡^�tfT-����Ѵ��I���d`�F\�v��‡Y��„ލ[g�‘

VBA Test Result:
C:\Documents and Settings\btorrea\Desktop\IBE Scoreboard2.xlsm is already Open
By �3c–$‹“Z�OglN�A�`!����9M�1~��Ę�gŒ%�*f��*�œ���D8‰_ƒ���S֍��œ…�$f
M�}>��8?��v�Œˆ��bH���s`�€��� �� �'���‹N����Q� 4���?GN�o:��j��u�*�mW�~��Z?v�fǶ�N��Q;��r#sw��›_��=Mƒ���~�VӲZA�z��'ž�‰G�|�‡^�tfT-����Ѵ��I���d`�F\�v��‡Y��„ލ[g�‘

Brian

In that case, I'm not sure I can help, this works fine for 2003 and I suspect is is to do with your Excel version. Unfortunately I only have '03 (which the code runs fine on) so I can't test 2010.

What is the goal for this code? There may be a work around

Kyle,

The goal of the code is just to find out who has read/write access to my data workbook. The reason that I need that information is so that when my manager logs-in to the charting notebook, she can know who has the read/write access to the data workbook. That way, if she needs to get read/write access, she knows who to call to tell them to get out of the data workbook.

Brian

romperstomper,

I get an 'invalid procedure call or argument, Run time error 5' at the following line when I run the code.

Please Login or Register  to view this content.

Brian

strFileName = "Z:\Reporting\IBE Scoreboard2.xlsm"

Brian

I have a similar objective, but I use this code instead after some long research. HTH.

Please Login or Register  to view this content.

You have to make sure you have "Active DS Type Library" selected under "Tools" --> "References"