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

0 votes

How can one share an excel file with another office user using vba? I have a template that needs to be minimally modified and shared with hundreds of people as a separate workbook (same active directory).

Here is a table I have that details which people should have access to which files:

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 of 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.

Oct 28, 2022 in Others by Kithuzzz
• 38,010 points
945 views

1 answer to this question.

0 votes

PowerApps were referenced; if you have a basic O365 license and access to SharePoint Online, you can set access as necessary. Here is an example taken from a file in the SPO Documents library:

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/

answered Oct 28, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,420 points
6,652 views
0 votes
1 answer

How to insert a picture into Excel at a specified cell position with VBA

Try this: With xlApp.ActiveSheet.Pictures.Insert(PicPath) With ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
1,930 views
0 votes
1 answer

How to use goal seek function in Excel user function?

In the Microsoft Excel Object-Sheet1(Sheet1), we can ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,420 points
545 views
0 votes
1 answer
0 votes
1 answer

Tab creation failure using macro

Export By Name From Multiple Worksheets Option Explicit Sub ...READ MORE

answered Apr 2, 2023 in Others by Kithuzzz
• 38,010 points
160 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
876 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,183 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
481 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,420 points
819 views
+1 vote
1 answer

How to use VBA in Excel for Google Search?

Try this: Private Const LicenseRegistration As String = ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
1,267 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP