SaveAs操作出现‘Object Workbook Failed’报错的排查求助

我在使用VBA的SavePath时遇到问题：鼠标悬停查看路径显示正确，但执行SaveAs操作时持续报错：

Object 'Workbook Failed'
我已经尝试过重写路径，但问题依旧，相关VBA代码如下，请问下一步该如何排查？

Sub UpdateData()
'This macro will activate on click of update button and will take the information from the Plaq. Spot Master turnover log and insert it into the rail turnover database'


Dim wb1 As Workbook

Dim SpotLog1 As Worksheet

Dim i As Integer
Dim j As Integer

Dim Dates As Date

If ThisWorkbook.Sheets("SPOT LOG").Range("A4") = "" Then
    MsgBox "Please Enter Date in Cell A4", vbCritical, "No Date Entered"
    Exit Sub
End If

Application.ScreenUpdating = False

Set wb1 = ThisWorkbook

Set SpotLog1 = wb1.Sheets("Spot Log")

Dates = SpotLog1.Range("A4")

Dim Month As String
Dim Year As String

Month = Format(Dates, "mmmm")
Year = Format(Dates, "yyyy")

Application.DisplayAlerts = False
Dim SavePath
Dim Temp
Temp = "A:\Turnover Logs\Temp"
SavePath = "A:\Turnover Logs\ " & Year & "\" & Month & "\ Turnover " & Format(Dates, "mm-dd-yyyy") & ".xlsm"

On Error Resume Next
wb1.SaveAs Filename:=Temp
On Error GoTo 0
wb1.SaveAs Filename:=SavePath

排查步骤： #

• 清理路径中的多余空格：你的SavePath里有两处无效空格——"A:\Turnover Logs\ "（Logs后多了空格）和"\ Turnover "（Turnover前多了空格），系统会将这些空格视为路径的一部分，直接删除后重试：

  SavePath = "A:\Turnover Logs\" & Year & "\" & Month & "\Turnover " & Format(Dates, "mm-dd-yyyy") & ".xlsm"
  

• 确保目标目录存在：SaveAs无法自动创建多级目录，如果A:\Turnover Logs\年份\月份不存在，会直接报错。添加目录检查与创建代码：

  Dim FolderPath As String
  FolderPath = "A:\Turnover Logs\" & Year & "\" & Month & "\"
  If Dir(FolderPath, vbDirectory) = "" Then
      MkDir FolderPath
  End If
  

• 修正临时保存的错误处理：你用On Error Resume Next屏蔽了临时保存的错误，但Temp = "A:\Turnover Logs\Temp"是目录而非完整文件名，执行SaveAs到目录本身就会报错，导致后续保存状态异常。改成指定临时文件的完整路径：

  Temp = "A:\Turnover Logs\Temp\temp_file.xlsm"
  If Dir("A:\Turnover Logs\Temp\", vbDirectory) = "" Then
      MkDir "A:\Turnover Logs\Temp\"
  End If
  

• 检查权限与文件锁定：

  • 确认当前用户对A盘目标目录有写入权限
  • 检查目标路径下是否存在同名文件，且该文件处于打开状态（被其他进程锁定）

• 添加详细错误捕获：替换现有错误处理，获取具体错误信息定位问题：

  On Error GoTo SaveError
  wb1.SaveAs Filename:=SavePath
  On Error GoTo 0
  
  Exit Sub
  

SaveError:
MsgBox "保存失败：" & Err.Description & " 错误代码：" & Err.Number, vbCritical
Resume Next

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