• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Help converting Macro to be compatible with Mac|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Help converting Macro to be compatible with Mac|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosHelp converting Macro to be compati…
sp_PrintTopic sp_TopicIcon
Help converting Macro to be compatible with Mac
Avatar
Vincent Mackey

New Member
Members
Level 0
Forum Posts: 1
Member Since:
October 16, 2019
sp_UserOfflineSmall Offline
1
October 16, 2019 - 4:34 am
sp_Permalink sp_Print

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

 

Sub MergeExcelFiles()
    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 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
 
            Set wbkCurBook = ActiveWorkbook
 
            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"
        End If
 
    Else
        MsgBox "No files selected", Title:="Merge Excel files"
    End If
End Sub

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
October 18, 2019 - 8:44 pm
sp_Permalink sp_Print sp_EditHistory

Not easy.

I use different codes on windows, for mac there can be at least 2 separate cases.

The code is limiting the number of selected files to exactly 2, you will have to adjust this.

Sub ImportNewData()
Dim ArrFiles As Variant, Fname As Variant, i As Byte, ExitPoint As Label

#If Mac Then
If Val(Application.Version) >= 15 Then
'excel 2016 for Mac, use posix to get files
ArrFiles = GetMac2016Files
Else
'earlier versions of excel for Mac
ArrFiles = GetMacFiles
End If
#Else
'Windows
ArrFiles = GetWinCSVFiles
#End If

'TypeName returns Variant()in Win, just V() on Mac 2011, S() in Mac 2016. If the user cancelled the import, Win will return Boolean, Mac+Excel 2016 will return Empty...
If Not TypeName(ArrFiles) Like "*()*" Then GoTo ExitPoint

'loop through list of files:

For i = LBound(ArrFiles) To UBound(ArrFiles)
GetDataFromCSVFiles CStr(ArrFiles(i)) 'replace this with your function to process files
Next

end sub

 

Here are the codes I use to get files:

Function GetMac2016Files() As Variant
Dim Files As Variant
'Ron de Bruin, 20 March 2016
Dim MyPath As String, MyScript As String, MyFiles As String, FileFormat As String

GetMac2016Files = False

On Error Resume Next
MyPath = MacScript("return (path to desktop folder) as String")
FileFormat = "{""public.comma-separated-values-text""}"

MyScript = "set theFiles to (choose file of type" & _
" " & FileFormat & " " & _
"with prompt ""Please select both csv files, for Inquiries and Subscribers!"" default location alias """ & _
MyPath & """ with multiple selections allowed)" & vbNewLine & _
"set thePOSIXFiles to {}" & vbNewLine & _
"repeat with aFile in theFiles" & vbNewLine & _
"set end of thePOSIXFiles to POSIX path of aFile" & vbNewLine & _
"end repeat" & vbNewLine & _
"set {TID, text item delimiters} to {text item delimiters, ASCII character 10}" & vbNewLine & _
"set thePOSIXFiles to thePOSIXFiles as text" & vbNewLine & _
"set text item delimiters to TID" & vbNewLine & _
"return thePOSIXFiles"

MyFiles = MacScript(MyScript)
On Error GoTo 0
If MyFiles <> "" Then
Files = Split(MyFiles, Chr(10))
If UBound(Files) <> 1 Then
MsgBox "Please select 2 csv files, one for Inquiries and one for Subscribers!"
Files = False 'accept only 2 files
End If
End If
GetMac2016Files = Files
End Function
Function GetMacFiles() As Variant
Dim Files As Variant
'Ron de Bruin, 20 March 2016
Dim MyPath As String, MyScript As String, MyFiles As String, FileFormat As String

GetMacFiles = False

On Error Resume Next
MyPath = MacScript("return (path to desktop folder) as String")
FileFormat = "{""public.text""}"

MyScript = "set applescript's text item delimiters to {ASCII character 10} " & vbNewLine & _
"set theFiles to (choose file of type" & _
" " & FileFormat & " " & _
"with prompt ""Please select both csv files, for Inquiries and Subscribers!"" default location alias """ & _
MyPath & """ with multiple selections allowed) as string" & vbNewLine & _
"set applescript's text item delimiters to """" " & vbNewLine & _
"return theFiles"

MyFiles = MacScript(MyScript)
On Error GoTo 0

If MyFiles <> "" Then
Files = Split(MyFiles, Chr(10))
If UBound(Files) <> 1 Then
MsgBox "Please select 2 csv files, one for Inquiries and one for Subscribers!"
Files = False 'accept only 2 files
End If
'check if both are csv (if no filter applied)

End If
GetMacFiles = Files
End Function
Function GetWinCSVFiles() As Variant
Dim Files As Variant
GetWinCSVFiles = False
#If Not Mac Then
With Application.FileDialog(3)
.AllowMultiSelect = True
.Title = "Please select both csv files, for Inquiries and Subscribers!"
.Filters.Add "CSV Files", "*.csv"
If .Show = True Then
'accept 2 files only
If .SelectedItems.Count <> 2 Then MsgBox "Please select 2 csv files, one for Inquiries and one for Subscribers!": Exit Function
Files = Array(.SelectedItems(1), .SelectedItems(2))
Else
Files = False
End If
End With
#End If
GetWinCSVFiles = Files
End Function

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Dana Friedt, Shawn Barwick, Nada Perovic, Uwe von Gostomski
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Forum Stats:
Groups: 3
Forums: 24
Topics: 6218
Posts: 27266

 

Member Stats:
Guest Posters: 49
Members: 31901
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.