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 in Others by Kithuzzz
• 20,660 points
47 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 by narikkadan
• 37,660 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 in Others by narikkadan
• 37,660 points
354 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 in Others by narikkadan
• 37,660 points
149 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 in Others by narikkadan
• 37,660 points
54 views
0 votes
1 answer
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,720 points
451 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
2,218 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 in Others by gaurav
• 22,040 points
69 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
205 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 in Others by narikkadan
• 37,660 points
119 views
0 votes
1 answer

How to use VBA in Excel for Google Search?

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

answered Oct 7 in Others by narikkadan
• 37,660 points
132 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