Turn cell range into array for GrantAccessToMultip

Question

I am completely new to VBA (and to this forum), so my apologies if this is a rather basic question. 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) 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.

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 but to no avail.

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

EDIT (just for future reference):

This is the correct version:

Sub requestFileAccess()
   Dim fileAccessGranted As Boolean
   Dim filePermissionCandidates
   filePermissionCandidates = Application.Transpose(Worksheets("Sheet1").Range("AB7:AB10"))
   fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)
End Sub

Answer 1

I was able to generate array from range with the following code:

Option Explicit

Sub requestFileAccess()

Dim fileAccessGranted            As Boolean
Dim filePermissionCandidates     As Variant

filePermissionCandidates = Worksheets("Table1").Range("A1:A5")
'fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)

End Sub

In stead of "Table1", write the name of your sheet, or an index. Enjoy! :)