Save workbook fails-CodePudding

This code works sometimes does not work sometimes. my system is synced with onedrive is this the reason. Path here is a local path.

Dim wb As Workbook
Set wb = Workbooks.Add

wb.SaveAs Filename:=Path & name
wb.Close True

runtime error

method save as object _workbook failed

I also tried wb.SaveAs Filename:=Path & name, FileFormat:=1 as suggested by Microsoft in this article.

CodePudding user response：

Looks like it has do do with OneDrive and not with your code (at least if your file path/name is correct). You can use error handling to get an error message and check if the path/file is the correct one in case of an error.

On Error Resume Next
wb.SaveAs Filename:=Path & name
If Err.Number Then
    MsgBox "File """ & Path & name & """ could not be saved.".
    Debug.Print Path & name
    Exit Sub
End If
On Error Goto 0  ' re-activate error reporting!

wb.Close False

Now you can check if the file path and file name is correct.

Also note that wb.Close True makes your file beeing saved twice in a row! First with .SaveAs and second with the SaveChanges:=True parameter of .Close. So it will take double the time and is usless to save a file twice in a row in the same file.

CodePudding user response：

It should be something like this

Dim wb as Workbook
Dim Path as String

Set wb = Workbooks.Add

Path = ActiveWorkbook.Path & "\" & custom_name & ".xlsm"

ActiveWorkbook.SaveAs fileName:=Path, FileFormat:=51

fileName = custom_name