File paths from cells to array for Macro with GrantAccessToMultipleFiles function

[jan_d]

Hello,

I am completely new to VBA (and to this forum), so my apologies if this is extremely basic. I need to get filepaths from a column in an Excel sheet and turn them into an Array to be used in a Macro to GrantAccessToMultipleFiles.

I am creating a database in a workbook that checks for the existence of files on an external harddrive (about 10.000 files total) with a Macro, and I want to grant excel permission to access these files in advance in order to avoid to have to click thousands of times the annoying Grant Access dialog in Excel 2016 for Mac.

I have dedicated an extra column in the Excel sheet to calculate the filepaths for the files and turn them into text before setting up the macro to check for file existence, and I want to first run the GrantAccessToMultipleFiles command in order to get the access before entering the formula.

I created a Macro with this code from: https://dev.office.com/blogs/VBA-improvements-in-Office-2016

Sub requestFileAccess()
       Dim fileAccessGranted As Boolean
       Dim filePermissionCandidates
       filePermissionCandidates = Array("/Users/xyz/Desktop/test1.txt", "/Users/xyz/Desktop/test2.txt")
       fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)
    End Sub

When I test it by substituting the example paths with the paths from the file it does work, but I have no idea how to automatically extract the paths from the column and turn them into an array to be used in the macro.

I tried with

     filePermissionCandidates = Array(Range("V5:V100"))

and some other combinations based on things found on this and other forums but to no avail. I assume I have to define the Array as a variable beforehand but I have no clue how to go about doing it.

If anyone could point me in the right direction that would be much appreciated.

Thank,

Jan

 

[Andrew Poulsom]

Does this work for you with filePermissionCandidates declared as Variant?

filePermissionCandidates = Application.Transpose(Range("V5:V100").Value)

 

[jan_d]

Thank you Andrew for such a quick reply!

And YES! That does the trick! Works like a charm.

I have two more questions: It works as long as the data is in one column only. How would I need to modify it if the data was distributed over a few more columns (as in V6:Z200)

And then, in order not to have to modify the Macro if it happens more times, how would I need to modify it if I wanted a user to be able to select the input range through a dialog box?

Again, thanks a lot for your quick and competent reply.

 

[Andrew Poulsom]

You would need to loop through the columns, passing them to the variable one at a time.

You can use Excel's InputBox method to prompt the user for a range.

 

[jan_d]

Thank you I do appreciate your help.
(Is there a way to mark an answer as correc on this forum?)

 

[tahasuria]

Hello.

I am trying to write a VBA program in Excel on a mac and the issue I have is that when I'm trying to open many new files even with the GrantAccessToMultipleFiles excel still gives me the dialog box 'Additional Permissions are Required'. Let me know if there is an additional function out there to disable this dialog box.

Thanks in advance.

Taha Suria