I have just upgraded my excel on my mac and have problems with my code and the GrantAccessToMultipleFiles and looping through the excel files in my directory. When the Set wb line executes, I get a Run-time error 1004. Additionally, I want to access a large number of files (>100) in my directory and want to know how I can get round individually listing the files in the GrantAccessToMultipleFiles line.
Sub Regression_1() Dim FilePath As String Dim folderPath As String Dim filename As String Dim wb As Workbook Dim fileAccessGranted As Boolean Dim filePermissionCandidates Dim wkb As Excel.Workbook 'External workbook Master results Dim wks As Excel.Worksheet 'Worksheet in External workbook Set wkb = Excel.Workbooks("Master Gaze.xlsm") Set wks = wkb.Worksheets("Data") 'combine to ref external workbook/worksheet Dim MediaID As Integer 'MediaID 1- letter and 4 Number output_row = 5 'starting row in Master sheet folderPath = "Macintosh HD:Users:kerijustice:Documents:Jessica:Test3:" filename = Dir(folderPath) Do While filename <> "" If Right(filename, 5) = ".xlsx" Then 'change to suit file type e.g., .xls/.txt files Application.ScreenUpdating = False 'Create an array with file paths for the permissions that are needed. filePermissionCandidates = Array("/Users/kerijustice/Documents/Jessica/Test3/P038 - JP_short.xlsx", _ "/Users//kerijustice/Documents/Jessica/Test3/WH18068 JP.xlsx") 'Request access from user. fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates) 'Returns true if access is granted; otherwise, false. Set wb = Workbooks.Open(folderPath & filename) ' Calculations 'Save Workbook ActiveWorkbook.Save 'Close file ActiveWorkbook.Close Loop
Last edited by a moderator: