How to use VBA Excel on Mac for automating file imports?

Question

I’m trying to figure out how to use VBA in Excel on macOS to automate a simple task for my coursework: importing multiple CSV files into one workbook.

Here’s the Windows version that fails on macOS:

Sub ImportCSV()

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)



If fd.Show = -1 Then

    Workbooks.Open fd.SelectedItems(1)

End If

End Sub

Answer 1

Hi @Tommie Bowman
Thank you for reaching out to the Microsoft Q&A Forum.

I understand how confusing this issue can be when your VBA macro works normally on Excel for Windows but fails on Excel for macOS.

Based on your description, I tested the behavior on my side. I tried the same approach using both FileDialog and the Application.GetOpenFilename method, but I also wasn’t able to get it working on my side. When running GetOpenFilename on macOS, it immediately returns the following message:

Run-time error '1004': Method 'GetOpenFilename' of object '_Application' failed.

From what I can see, this might be related to how Excel for Mac handles the file picker functions. On macOS, Excel may not be able to open the native file selection dialog, even with simple code, so the behavior can differ from Windows. Because of this, the same VBA code you use on Windows might not run on the Mac version.

I know this may not be the answer you were hoping for, and I truly appreciate your patience and understanding while working through these platform-specific differences.

If you need any further assistance, please feel free to let me know.

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To automate file imports in VBA for Excel on macOS, you can use the Application.GetOpenFilename method instead of the FileDialog object, which is not fully supported on macOS. Here’s how you can modify your existing code to work on macOS:

Sub ImportCSV()
    Dim filePath As Variant
    
    ' Prompt the user to select a CSV file
    filePath = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv), *.csv", Title:="Select a CSV File")
    
    ' Check if the user selected a file
    If filePath <> False Then
        Workbooks.Open filePath
    End If
End Sub

This code will open a dialog box allowing the user to select a CSV file, and if a file is selected, it will open that file in Excel. You can further enhance this code to loop through multiple files if needed for your coursework.