VBA: Set permission to read Excel file to whole organization

Question

So, I've run into a VBA problem. Due to IT policies I can't control read/write access to my Excel files on folder level, but have to do it for each individual file. I have a folder with 15 files, that everyone having access to the folder can access and write in. On a certain date I want to lock these files down, so that only a specific group can write, but the rest can still read.

I'm aware it's realtively easy to do this manually (File->Info->Protect Workbook->Restrict Access), but since I already run a macro for other reasons when we want the files locked down, I'd like for it to do this part too. This is where I run into my problem:

I've figured out how to set the permission for a specific user, using this code

Sub set_access()

Dim UserPerm As Office.UserPermission

Workbooks("workbook name").Activate
   Set UserPerm = ActiveWorkbook.Permission.Add("name@company", msoPermissionFullControl)

I can loop through this for each person I want to have writing/full access, no problem. There's like fifteen of us and I know who we are.

The thing is I also want everyone in my organization to retain their reading rights. This has it's own button if I do it manually (in the Permission menu that is accessed as mentioned above), but I can't understand how to trigger that setting via vba. I've tried to record a macro of me doing it manually, to see the code, but nothing is recorded. I've also read just about everthing I could find online like four or five times, and what I need simply doesn't seem to be covered.

Does anyone know if this is poosible? Or if it isn't; can I reach the same results some other way?

---

tldr: I can set permission by individual, but in addition to this I want to give a general read-only access to my Excel files for anyone in my organization. Can this be done with vba? If so; how?

Answer 1

I had the same problem and was looking into it. After all, by using "Everyone" instead of an email address, I was able to set permissions for everyone in my organization. try it.