6

How does one use vba to share an excel file with another office user? I have a template that needs to be customized slightly and shared as a separate workbook with hundreds of users (same active directory).

I have a table mapping out what files should be shared with what users as shown here: enter image description here

I thought I could use a sharing method to set the permissions using MSOPermission. I've tried quite a few approaches which have all failed, but these were the ones I was most optimistic would work:

wkBk.Permission.Add "bill_User@company.com",msoPermissionEdit
wkBk.Permission.Add "SallyCEO@company.com",msoPermissionRead
wkBk.Permission.Add "billy_companyID",msoPermissionEdit

The specific error I receive (shown here) indicates something is wrong with the Permission Object. I can't find much documentation on this (a common pet-peeve of mine with Microsoft).

I've seen a couple posts shown below, but none address my question.

I'm sure I'm in the wrong area or maybe I need to enable a library. I'm embarrassed to say that I even attempted to use the macro recorder, but no code was logged when I interacted with the sharing menu shown here.

Bonus Question

It's possible that VBA is not the best tool for handling my use-case situation. If there's a better automation method for my situation such as TypeScript or Power Apps, I will upvote any suggestions that include basic instructions or a reference with specific terms/procedures that I could use to hunt down an overall solution. Thanks.

Note to Microsoft: Executing this comparable task in Google Sheets is easy and well documented:

ss.addEditor("billyTheUser@gmail.com");
ss.addViewer("sallyCEO@gmail.com");
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • 1
    Greetings! Have you tried ["Use of the Permission object raises an error when the Windows Rights Management client is not installed."](https://learn.microsoft.com/en-us/office/vba/api/Office.Permission) – Sgdva Oct 02 '20 at 20:06
  • Hi @Sgdva, i did see that article and tried it, but it throws an error on the line `Set irmPermission = ActiveWorkbook.Permission`. I wasn't sure if that was true VBA or VB (it says both). That doesn't mean it's the wrong approach though... – pgSystemTester Oct 02 '20 at 21:59
  • I was unable to successfully invoke `.Permission.Add` until I set `.Permission.Enabled = True`. This assignment took a while to complete (about 5 seconds). From then on, `.Permission.Add` worked, even after closing Excel and creating a new workbook, not calling `.Permission.Enabled` ever after. It's as if Excel, Office or Windows had installed or activated some capability for subsequent usage (the WRM client mentioned by @Sgdva?). – Excelosaurus Oct 06 '20 at 02:49

1 Answers1

1

PowerApps was mentioned - assuming you have a standard O365 license and SharePoint Online available then it's possible to set access as needed here's an illustration from an SPO Documents library file:

enter image description here

Microsoft Documentation: https://support.microsoft.com/en-us/office/customize-permissions-for-a-sharepoint-list-or-library-02d770f3-59eb-4910-a608-5f84cc297782

Option 4 from this article: https://sharepointmaven.com/6-locations-can-set-security-files-sharepoint-office-365/

kshkarin
  • 574
  • 4
  • 9
  • thank you @kshkarin. There doesn't appear to be a VBA solution, so I appreciate the tip for a workaround. Upvoted. – pgSystemTester Oct 13 '20 at 08:24
  • @PGSystemTester this video seems to be in the right direction https://www.youtube.com/watch?v=0ahYoy5L3ec&t=1929s, you'd need to query LDAP in VBA to get the user identities, and even then it's not a guaranteed solution since VBA (or password protected VBA) can still be circumvented. – kshkarin Oct 13 '20 at 09:32
  • 1
    The simple and robust solution is using SharePoint and managing permissions in the file library. – kshkarin Oct 13 '20 at 09:33
  • Thanks. That plain-text password isn't going to cut it. I can rip through a VBA editor protection without even minimizing Excel. I appreciate the Sharepoint approach though. I still just find it hard to believe that a task so simple cannot be automated. Again Google Sheets has this available in the same basic procedures as opening a workbook (I am complaining to Microsoft... not you ) – pgSystemTester Oct 13 '20 at 19:08