Using GrantAccessToMultipleFiles to avoid multiple demands for permissions

[Colin_in_Belgium]

Hello,

I have a VBA macro which consolidates data from multiple files.
Each time I run the macro, the input files are different.
So, I am annoyed by the "Grant Permissions" request that appears for every file, during each execution of my macro.

I believe it is possible to grant permissions for all files at once using GrantAccessToMultipleFiles.
However, after including GrantAccessToMultipleFiles at the start of my macro, it appears to do nothing and I still have to grant access to every file individually, during the execution of the macro.

Here is an example of what I am trying (or course, there are a lot more files in the actual code):

Please Login or Register  to view this content.

Any ideas why it seems to do nothing?
If I ever get this working, I would also like to have it work with a wild card for all files in a certain folder, for example:

Please Login or Register  to view this content.

Any thoughts if that is the best approach?

Thanks.

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I did it for you this time. Please read forum rules. Thanks

[Colin_in_Belgium]

Thanks for modifying.

[Winon]

Hello,

Please Login or Register  to view this content.

Any ideas why it seems to do nothing?
If I ever get this working, I would also like to have it work with a wild card for all files in a certain folder, for example:

Please Login or Register  to view this content.

You could try:

Please Login or Register  to view this content.

[Colin_in_Belgium]

Thanks for your suggestion Winon.
Unfortunately, I am still being asked to grant access for each file I open.

(By the way, there was a typo in my original post ...
I am using the correct file extension .xlsx in the macro and not .xlxs as I had previously put.
I have now corrected this in my original post.)

[Colin_in_Belgium]

Oh dear ... can nobody share a successful way to do this on Excel 2019 for Mac - to grant access to multiple files in one go?
It would seem that the sandboxing approach of Excel has caused several related problems.

[Colin_in_Belgium]

After further experimentation, I notice that if I specify a list of exact filenames in quotes for the file array, then the command works as expected.

However, if I include a wildcard (*) in the filename, it does nothing.
Has anyone got this working with a wildcard on Excel for Mac? Thanks.

This works:

Please Login or Register  to view this content.

This doesn't work:

Please Login or Register  to view this content.

I have managed to find a workaround that specifies the current date prefix I need included in the filenames, but it's not ideal:

Please Login or Register  to view this content.

This is what I would like to get working:

Please Login or Register  to view this content.