Open Multiple files with loop

epaklt

New Member
Joined
Jul 10, 2019
Messages
2
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.

Thanks

Code:
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:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Apple won't let you do this any more. It's called "sandboxing." You'll get the stupid Grant Access dialog once for each directory. Once you have granted access, then you won't see the grant access dialog on subsequent attempts. Click here for a discussion about a work-around that turns sandboxing off.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top