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.

• Automate File sharing
• Share and unshare file
• Permission sharing

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]");

Answer 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:

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/