my colleagues need to collect regularly and aggregate data coming from 20 organizations. They are not very familiar with Excel Power Query and other Excel manipulations. In order to help them, I'm writing an Excel file with embedded macros, which will allow them to get a consolidated file and 20 files to be shared with their relevant contacts within the 20 organizations. They will have just to design the format of an input file. Then the macro will duplicate this file for the 20 organizations in a dedicated folder they have chosen and which is accessible in Teams or SharePoint. This first part work already.
I want now to address some confidentiality issues :
I want my colleagues to select one or two emails for each of the 20 organizations.
Then the VBA macro will hide all input files in the dedicated folder. Only the selected end-user will see in this folder the file authorized for his organization and will be able to complete the data.
I would like to keep this last part in then VBA macro. How can I reach that with VBA?
I've seen ways to share Excel files within Excel features. But I prefer to hide and protect the files in Teams/sharePoint.
Thanks a lot
Pierre-Yves