Mac版Excel 2016 VBA导出工作表为CSV格式失败求助

Hey there! Let's work through this Mac-specific VBA CSV export problem you're dealing with. I'll break down the issues in your code and share reliable solutions that work consistently on macOS.

Key Problems in Your Original Code #

First, let's call out the main hurdles you hit:

• The GetSaveAsFilename dialog defaulted to XLSX because you didn't specify a proper CSV filter
• Your suffix replacement failed due to a typo (".xslx" instead of ".xlsx")
• Even with a filter, the save step wasn't enforcing the CSV format correctly

Solution 1: Correct GetSaveAsFilename with Proper Filter & Validation #

This fixed code ensures the dialog defaults to CSV, validates the filename suffix, and saves correctly:

#If Mac Then
    Dim s_fname As Variant
    ' Set explicit CSV filter to make it the default option
    s_fname = Application.GetSaveAsFilename( _
        InitialFileName:=ThisWorkbook.Path & Application.PathSeparator & sheetname & ".csv", _
        FileFilter:="CSV Files (*.csv), *.csv", _
        Title:="Save CSV File")
    
    If s_fname <> False Then
        ' Ensure filename ends with .csv (in case user manually removed it)
        If LCase(Right(s_fname, 4)) <> ".csv" Then
            s_fname = s_fname & ".csv"
        End If
        
        Application.DisplayAlerts = False
        ' Explicitly use xlCSV format (value 6) to override any default settings
        ActiveWorkbook.SaveAs Filename:=s_fname, FileFormat:=xlCSV, CreateBackup:=False
        Application.DisplayAlerts = True
    End If
#Else
    ' Keep your working Windows code as-is
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
        ThisWorkbook.Path & Application.PathSeparator & sheetname & ".csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = True
#End If

What's Fixed Here: #

1. Proper FileFilter: The filter "CSV Files (*.csv), *.csv" tells Excel to prioritize CSV as the default format in the save dialog.
2. Suffix Validation: Instead of relying on a replace (which failed due to a typo), we check if the filename ends with .csv and add it if missing—this is more reliable.
3. Explicit Format Enforcement: We explicitly set FileFormat:=xlCSV to make sure Excel doesn't fall back to XLSX.

Solution 2: Save CSV Directly (No User Dialog) #

If you don't need user input and just want to save straight to the workbook's directory, use this simplified Mac-compatible code:

#If Mac Then
    Dim savePath As String
    savePath = ThisWorkbook.Path & Application.PathSeparator & sheetname & ".csv"
    
    Application.DisplayAlerts = False
    ' Use xlCSVUTF8 (62) instead of xlCSV (6) if you need UTF-8 encoding (more cross-system compatible)
    ActiveWorkbook.SaveAs Filename:=savePath, FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = True
#Else
    ' Windows code remains unchanged
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
        ThisWorkbook.Path & Application.PathSeparator & sheetname & ".csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = True
#End If

Note: If you run into character encoding issues (like special characters not showing up), swap xlCSV with xlCSVUTF8 (value 62) for UTF-8 formatted CSV files.

Why Your Original Code Didn't Work #

• Missing Filter: Without FileFilter, Mac Excel defaults to XLSX as the save format.
• Typo in Replace: Your line replace(s_fname,".xslx",".csv") had a misspelling (xslx instead of xlsx), so it never matched the suffix to replace.
• Mac SaveAs Quirks: macOS Excel sometimes ignores the FileFormat parameter if the filename doesn't have the correct suffix, hence the need for explicit suffix validation.

内容的提问来源于stack exchange，提问作者user9828268