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:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Jim Gordon Mac MVP

Board Regular
Joined
Jul 22, 2011
Messages
240
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,892
Messages
5,483,543
Members
407,397
Latest member
HerbA

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top