none
SaveCopyAs Command Fails RRS feed

  • Question

  • Excel 2013. I have some code in the Workbook_BeforeClose event that runs a subroutine to save a copy of the file to my hard drive (in addition to the copy I am working on). I use the ThisWorkbook.SaveCopyAs command in the subroutine to do this. If I close the file from the File menu the Workbook_BeforeClose code runs and the SaveCopyAs command in the subroutine works just fine. But if I close the file from a VBA subroutine (ThisWorkbook.Close SaveChanges:=True) the Workbook_BeforeClose code runs but the SaveAsCopy command in the subroutine fails most of the time. Maybe 1 out of ten tests will the SaveCopyAs command actually work.

    I'm at the end of my limited VBA knowledge. Anyone else ever run into this problem? Any suggestions?


    phillfri


    Workaround:
    Application.CommandBars("File").Controls("Close").Execute


    • Edited by phillfri Tuesday, November 24, 2015 8:32 PM
    Tuesday, November 24, 2015 5:49 AM

Answers

  • Hi phillfri,

    >> But if I close the file from a VBA subroutine (ThisWorkbook.Close SaveChanges:=True) the Workbook_BeforeClose code runs but the SaveAsCopy command in the subroutine fails most of the time

    Since that way doesn't work smoothly, I suggest that you could save current workbook to other directory as a copy by using Workbook.SaveAs method before call Close method.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, November 25, 2015 6:04 AM
    Moderator

All replies

  • Any suggestions?

    Use the AfterSave event and make a copy of ThisWorkbook.FullName with VBA.FileCopy

    Andreas.

    Tuesday, November 24, 2015 10:25 AM
  • Hi phillfri,

    >> But if I close the file from a VBA subroutine (ThisWorkbook.Close SaveChanges:=True) the Workbook_BeforeClose code runs but the SaveAsCopy command in the subroutine fails most of the time

    Since that way doesn't work smoothly, I suggest that you could save current workbook to other directory as a copy by using Workbook.SaveAs method before call Close method.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, November 25, 2015 6:04 AM
    Moderator