How To Use VBA To Share Excel File With Fellow Office User?

Question

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:

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 "[email protected]",msoPermissionEdit
wkBk.Permission.Add "[email protected]",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("[email protected]");
ss.addViewer("[email protected]");

Greetings! Have you tried "Use of the Permission object raises an error when the Windows Rights Management client is not installed." — Sgdva
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
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

kshkarin kshkarin · Accepted Answer · 2020-10-08T10:15:46

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:

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/

How To Use VBA To Share Excel File With Fellow Office User?

Bonus Question

1 Answers