Open Multiple files with loop
Results 1 to 2 of 2

Thread: Open Multiple files with loop
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Open Multiple files with loop

    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 RoryA; Jul 11th, 2019 at 03:54 AM. Reason: CODE tags

  2. #2
    Board Regular
    Join Date
    Jul 2011
    Location
    Buffalo, NY
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Open Multiple files with loop

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •