ActiveWorkbook.SaveAs代码执行正常但工作簿无法保存问题

运行VBA代码时，系统弹出保存询问对话框，点击“是”后代码执行结束，但目标文件夹中未生成保存的文件。该代码后续将绑定至工作表命令按钮，“否”选项会触发多工作表部分内容转PDF并打印的流程。

原代码片段 #

Sub mac_CreateProposalFolder()

    Dim varFilePathDir As Range
    Set varFilePathDir = Range("cell_FilePathDrive")

    Dim varFilePathProjYear As Range
    Set varFilePathProjYear = Range("cell_FilePathYear")

    Dim varFilePathProjYNum As Range
    Set varFilePathProjYNum = Range("cell_FilePathYearSuffix")

    Dim varFilePathProjName As Range
    Set varFilePathProjName = Range("cell_DDGJobName")

    Dim varFilePathSubFold1 As Range
    Set varFilePathSubFold1 = Range("cell_FilePathSubFolder1")

    Dim varBusinessUnit As Range
    Set varBusinessUnit = Range("cell_BusinessUnit")

    Dim varDDGJobNumber As Range
    Set varDDGJobNumber = Range("cell_DDGJobNumber")

    Dim varProposalDate As Range
    Set varProposalDate = Range("cell_ProposalDate")

    Dim varFilePathSaveAs1Survey As String
    Dim varFilePath1Survey As String

    Dim varAnswer5Survey As VbMsgBoxResult

    varFilePath1Survey = varFilePathDir & varFilePathProjYear & "-000\\" & varFilePathProjYear & varFilePathProjYNum & " " & varFilePathProjName
    
    varFilePathSaveAs1Survey = varFilePath1Survey & "\\" & varFilePathSubFold1 & "\\" & varBusinessUnit & " Proposal for " & varDDGJobNumber & " " & varFilePathProjName & " on " & varProposalDate
    
        'Checks if the proposal file has been created for this project and asks if the user wishes to save the file if not, or cancel the operation.
        If VBA.FileSystem.Dir(varFilePathSaveAs1Survey) = vbNullString Then
            varAnswer5Survey = MsgBox("Excel file for this business unit and proposal date has not been saved to this folder yet. Do you wish to save this excel file to the folder?", vbYesNoCancel, "Save Excel Workbook?")
            Select Case varAnswer5Survey
                Case vbYes
                    ActiveWorkbook.SaveAs Filename:=varFilePathSaveAs1Survey & ".xlsm"
                Case vbNo
                    'saved spot for future code options.
                    Exit Sub
                Case vbCancel
                    Exit Sub
            End Select
        End If
        On Error Resume Next
End Sub

已尝试的解决方法 #

• 替换保存对象：将ActiveWorkbook.SaveAs替换为ThisWorkbook.SaveAs（偏好前者，因工作簿为通用模板，需保存为当前项目的活动工作簿）
• 指定文件格式参数：使用带FileFormat:=xlOpenXMLWorkbookMacroEnabled的多种写法，示例：

  ActiveWorkbook.SaveAs ([varFilePathSaveAs1Survey & ".xlsm", xlOpenXMLWorkbookMacroEnabled]); 
  ActiveWorkbook.SaveAs Filename:= varFilePathSaveAs1Survey & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled; ActiveWorkbook.SaveAs ([Filename:= varFilePathSaveAs1Survey & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled])
  

• 录制宏修改路径：通过录制保存宏并调整路径，示例：

  ChDir "C:\\Users\\******\\OneDrive - **********, PC\\Desktop"
      ActiveWorkbook.SaveAs Filename:= _
          "https://******-my.sharepoint.com/personal/******/Documents/Desktop/PROJECT%20BUDGET-Rough%20Draft%20V1.xlsm" _
          , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
  

  注：*****为脱敏的内部链接信息

运行环境 #

• 系统：Windows 11（Dell Precision电脑）
• Excel版本：Microsoft® Excel® for Microsoft 365 MSO（版本2302，64位）

问题解决 #

最终排查出核心原因是日期格式不匹配：单元格设置的格式为“YYYY-MM-DD”，但代码读取时自动转换为“mm\dd\yyyy”格式，导致路径中包含斜杠（\）这类非法字符，系统无法识别有效路径。修改日期格式后，保存功能正常执行。

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