Mac版Excel VBA复制数据到当日CSV文件运行报错如何解决

核心报错原因 #

• 工作表引用缺失：所有Range调用未绑定指定工作表，默认读取当前活动工作表，既可能取错Sheet2的数据源，也会导致CSV文件中的空列查找逻辑错位
• 变量未赋值直接调用：
  • 代码中ColNum变量从未定义、赋值，CellAddress = Cells(1, ColNum).Address直接触发运行时错误
  • 列号转列字母后未赋值给colstart变量，后续拼接单元格地址时使用空值报错
  • Else分支未给myCSVFileName赋值，保存CSV时找不到目标路径
• 错误捕获无有效信息：仅弹出通用失败提示，无法定位具体错误原因

修复后代码 #

Option Explicit
Sub BackUpScriptData()
    Dim strFileName As String
    Dim strFileExists As String
    Dim oneCell As Range
    Dim csvOpened As Workbook
    Dim myWB As Workbook
    Dim tempWB As Workbook
    Dim rngToSave As Range
    Dim CellAddress As String
    Dim TestChar As String
    Dim colstart As String
    Dim i As Integer
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    On Error GoTo err
    
    ' 替换为自己的实际路径
    strFileName = "/Users/XXXXXXXX/Library/Group Containers/XXXXXXXX.Office/User Content.localized/Startup.localized/Excel/" & "Telesales-Leads-" & VBA.Format(VBA.Now, "mm-dd-yyyy") & ".csv"
    strFileExists = Dir(strFileName)
    
    Set myWB = ThisWorkbook
    ' 明确指定取Sheet2的非连续区域
    Set rngToSave = myWB.Worksheets("Sheet2").Range("A1:B69,H1:J69")
    
    If strFileExists = "" Then
        MsgBox strFileName & " 不存在，新建文件"
        rngToSave.Copy
        Set tempWB = Application.Workbooks.Add(1)
        With tempWB
            .Sheets(1).Range("A1").PasteSpecial xlPasteValues
            .SaveAs Filename:=strFileName, FileFormat:=xlCSV, CreateBackup:=False
            .Close
        End With
    Else
        rngToSave.Copy
        Set csvOpened = Workbooks.Open(Filename:=strFileName)
        ' 明确在CSV的第一个工作表找空列
        With csvOpened.Sheets(1)
            Set oneCell = .Range("A1")
            Do While WorksheetFunction.CountA(oneCell.EntireColumn) > 0
                Set oneCell = oneCell.Offset(0, 1)
            Loop
            ' 列号转列字母
            CellAddress = oneCell.Address
            colstart = ""
            For i = 2 To Len(CellAddress)
                TestChar = Mid(CellAddress, i, 1)
                If TestChar = "$" Then Exit For
                colstart = colstart & TestChar
            Next i
            .Range(colstart & "1").PasteSpecial xlPasteValues
        End With
        ' 直接用已定义的strFileName保存，避免重复定义变量
        csvOpened.SaveAs Filename:=strFileName, FileFormat:=xlCSV, CreateBackup:=False
        csvOpened.Close
    End If
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "备份成功"
    Exit Sub
    
err:
    ' 弹出具体错误原因方便调试
    MsgBox "备份失败，错误原因：" & Err.Description
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

补充说明 #

• 请将代码中路径的XXXXXXXX占位符替换为你自己的实际路径值
• 若Mac环境下仍提示路径错误，可尝试将POSIX路径转换为Mac格式路径：将strFileName = 你的路径替换为strFileName = MacScript("return POSIX file """ & 你的路径 & """ as string")

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