Function .Activate and .Close not working on Mac/Excel 2016 - workaround?

[Kruggie]

Hi team,

I have a Macro that runs like a dream on a PC, but on the Mac (which is the computer I use) it does not. I've traced it to the two lines highlighted in red. The macro runs when I remove them, but that means I have to manually close hundreds of files. Is there a workaround or fix for this? Any help is much appreciated

Sub ReadInFiles()'
' ReadInFiles Macro
'
Dim looper As Integer
Dim FileName As String


For looper = 1 To 200 
   Windows("USV_data-master_v0.1").Activate
    Sheets(1).Select
    FileName = Cells(looper, 1)


    Workbooks.Open FileName:= _
        "/Users/wilkinli/Documents/Experiments Data/USV/Raw data/" & FileName
    Columns("A:P").Select
    Selection.copy
    Windows("USV_data-master_v0.1").Activate
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    ActiveSheet.Name = Left(FileName, 9)
 
[COLOR=#ff0000]    Windows(FileName).Activate[/COLOR]
[COLOR=#ff0000]    ActiveWindow.Close[/COLOR]
Next looper


End Sub

 

[Fluff]

replace the two lines in red with

 Workbooks(FileName).Close

 

[Kruggie]

replace the two lines in red with

 Workbooks(FileName).Close

That works, wohoo! However, now I get the "Grant access" request caused by Apple's sandboxing, as well as the "There is a large amount of information on the clipboard. Do you want to be able to paste this information into another program later?" query from Excel. Is there a way to have code deal with that?

 

[Norie]

Don't know if it'll help with the 'sandbox' thing but it should stop the Excel message clipboard message.

Option Explicit

Sub ReadInFiles()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsDst As Worksheet
Dim rngSrc As Range
Dim looper As Long
Dim FileName As String

' ReadInFiles Macro
'

    Set wbDst = Workbooks("USV_data-master_v0.1")
    
    For looper = 1 To 200

        FileName = wbDst.Sheets(1).Cells(looper, 1)

        Set wbSrc = Workbooks.Open(FileName:="/Users/wilkinli/Documents/Experiments Data/USV/Raw data/" & FileName)
        Set rngSrc = wbSrc.ActiveSheet.Columns("A:P")
        Set wsDst = wbDst.Sheets.Add(After:=wbDst.ActiveSheet)
        
        rngSrc.Copy
        
        wsDst.Paste
        
        Application.CutCopyMode = False
        
        wsDst.Name = Left(FileName, 9)
 
        wbSrc.Close
    
    Next looper

End Sub

 

[Kruggie]

thanks, will try that