Help converting Macro to be compatible with Mac

[Vmackey]

Hello! I have a client that is using Macs to run the macro below and I am having trouble converting it. At solutions?

SubMergeExcelFiles()
    Dim fnameList, fnameCurFile As Variant
    Dim countFiles, countSheets As Integer
    Dim wksCurSheet As Worksheet
    Dim wbkCurBook, wbkSrcBook As Workbook
    fnameList =Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks(*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excelfiles to merge", MultiSelect:=True)
    If (vbBoolean <> VarType(fnameList)) Then
        If (UBound(fnameList) > 0) Then
            countFiles =0
            countSheets= 0
            Application.ScreenUpdating = False
            Application.Calculation = xlCalculationManual
            SetwbkCurBook = ActiveWorkbook
            For EachfnameCurFile In fnameList
                countFiles = countFiles + 1
                Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
                For Each wksCurSheet In wbkSrcBook.Sheets
                    countSheets = countSheets + 1
                    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                Next
                wbkSrcBook.Close SaveChanges:=False
            Next
            Application.ScreenUpdating = True
            Application.Calculation = xlCalculationAutomatic
           MsgBox"Processed " & countFiles & " files" & vbCrLf& "Merged " & countSheets & " worksheets",Title:="Merge Excel files"
       End If
    Else
        MsgBox "No files selected", Title:="Merge Excel files"
    End If
End Sub

[Aussiebear]

Your code should generally work on a Mac system with Microsoft Excel for Mac, but there are a few potential issues and best practices to ensure its reliability:

File path separators. Windows uses backslashes (\) in file paths, while macOS uses forward slashes (/). VBA on Mac should handle this automatically in most cases, but if you're working with hardcoded paths, be aware of the difference.The Application.GetOpenFilename should return a path compatible with the Mac file system.
File Format Compatibility. Ensure all the Excel files you're trying to merge are in a compatible format (.xls, .xlsx, or .xlsm). Older .xls files might have compatibility issues in newer Excel versions. The filter in Application.GetOpenFilename looks correct.
Reference Library. Verify that the required Microsoft Excel Object Library is properly referenced in the VBA editor. Go to Tools -> References in the VBA editor and make sure "Microsoft Excel [Version] Object Library" is checked. In most cases, this should be automatically set.


ActiveW1. File Path Separators:workbook. Ensure that an active workbook is open before running the SubMergeExcelFiles() macro. If no workbook is open, then Set wbkCurBook = ActiveWorkbook will result in an error. It is good practice to create a new workbook, and then merge the files to that new workbook.
Screen Updating and Calculation. Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual are good practices for performance, but ensure they are properly reset to True and xlCalculationAutomatic at the end of the macro.
Error Handling. Adding error handling will make the macro more robust.

This version should work on a mac system.

Sub MergeExcelFiles()
    Dim fnameList, fnameCurFile As Variant
    Dim countFiles, countSheets As Integer
    Dim wksCurSheet As Worksheet
    Dim wbkCurBook, wbkSrcBook As Workbook
    Dim errNum As Long
    On Error GoTo ErrorHandler
    fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks(*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
    If (vbBoolean <> VarType(fnameList)) Then
        If (UBound(fnameList) > 0) Then
            countFiles = 0
            countSheets = 0
            Application.ScreenUpdating = False
            Application.Calculation = xlCalculationManual
            ' Create a new workbook to merge into
            Set wbkCurBook = Workbooks.Add
            For Each fnameCurFile In fnameList
                countFiles = countFiles + 1
                Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
                For Each wksCurSheet In wbkSrcBook.Sheets
                    countSheets = countSheets + 1
                    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count) 
                Next
                wbkSrcBook.Close 
                SaveChanges:=False
                Next            Application.ScreenUpdating = True
                Application.Calculation = xlCalculationAutomatic
                MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
           Next            
        End If
    Else
        MsgBox "No files selected", Title:="Merge Excel files"
    End If
    Exit Sub
    ErrorHandler:    
    errNum = Err.Number
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "An error occurred: " & Err.Description, Title:="Error", vbCritical    
    Err.Clear
End Sub