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