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