none
VBA SaveAs Doesn't Save - No Error RRS feed

  • Question

  • Hi All,

    I haven't needed to ask a question here for a few years - generally finding what I need by searching the various forums. However, this one has me stumped. I found numerous similarly titled questions and solutions on the web but none solve my problem. The following code runs as expected, in that I can step through the code and it gets to the .SaveAs command with a legitimate string in vFile but the file simply isn't saved.

    'In the ThisWorkbook module


    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        ThisWorkbook_BeforeSave SaveAsUI:=SaveAsUI, Cancel:=Cancel
    End Sub

    'In a standard module

    Public Sub ThisWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim vFile As Variant If ThisWorkbook.Path = "" Then If Not AIPlist Is Nothing Then AIPlist.SaveIdList Application.EnableEvents = False vFile = Application.GetSaveAsFilename(InitialFileName:=GetFileName & ".xlsb", _ FileFilter:="Excel Binary files (*.xlsb), *.xlsb, All files (*.*), *.*") If vFile <> False Then ThisWorkbook.SaveAs Filename:=vFile, FileFormat:=50 Application.EnableEvents = True Cancel = True Else MsgBox ("Nothing to save yet.") Cancel = True End If Else ' Let Excel do it's thing Cancel = False End If End Sub

    In case it makes any difference, I should point out that the file itself is the product of a .xltm template.

    And in case anyone's tempted to ask, i always keep code in the ThisWorkbook, Worksheet and Form modules to an absolute minimum as they can't be 'cleaned'. Hence, the call to a standard module.

    Thursday, May 11, 2017 4:30 PM

All replies

  • The following code runs as expected, in that I can step through the code and it gets to the .SaveAs command with a legitimate string in vFile but the file simply isn't saved.

    In case it makes any difference, I should point out that the file itself is the product of a .xltm template.

    I've copied the code and have to modified it, because of unknown variables.

    Saved a XLTM file. Excel closed, double-click the XLTM, CTRL-S and enter "test", click Save and the file is saved. No issue.

    Andreas.

    Public Sub ThisWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      Dim vFile As Variant
      Dim GetFileName
    
      If ThisWorkbook.Path = "" Then
        If True Then
    '    If Not AIPlist Is Nothing Then
    '      AIPlist.SaveIdList
    
          Application.EnableEvents = False
          vFile = Application.GetSaveAsFilename(InitialFileName:=GetFileName & ".xlsb", _
            FileFilter:="Excel Binary files (*.xlsb), *.xlsb, All files (*.*), *.*")
    
          If vFile <> False Then ThisWorkbook.SaveAs Filename:=vFile, FileFormat:=50
    
          Application.EnableEvents = True
          Cancel = True
        Else
          MsgBox ("Nothing to save yet.")
          Cancel = True
        End If
      Else
        ' Let Excel do it's thing
        Cancel = False
      End If
    End Sub
    

    Thursday, May 11, 2017 5:07 PM
  • Hi Andreas,

    Thanks for taking the time to do the above. This at least tells me there's nothing wrong with the code or methodology and I need to look elsewhere for the cause. I'll post back if I work it out.

    Nick.

    Thursday, May 11, 2017 6:09 PM
  • Hello Nick,

    The code works for me too. I would suggest you manually save a new workbook using the template to check if your code causes the issue. You could also create a new template to test the BeforeSave event only. Please disable all add-ins when testing.

    You may try to reproduce the issue in other computer. If you find the steps to reproduce the issue, please let us know and share here.

    Besides, please visit How to troubleshoot errors when you save Excel workbooks to see possible cause.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 12, 2017 3:27 AM
    Moderator
  • Thank you Celeste,

    That's a potentially useful link for future reference. I have managed to resolve the problem though...

    This may be a version specific thing (I'm using Office 2010, Windows 7, running on a VMWare session) but it appears VBA didn't like the fact that my 'BeforeSave' routine was being called from within the ThisWorkbook module's BeforeSave routine. I've basically bypassed this now and put the code directly in the ribbon button action sub (this is a dictator app with customised ribbon).

    Sub rxbtnSave_Click(control As IRibbonControl)
        Dim vFile As Variant
    
        If ThisWorkbook.Path = "" Then
    '            If Not AIPlist Is Nothing Then
    '            AIPlist.SaveIdList
    
                Application.EnableEvents = False
                    vFile = Application.GetSaveAsFilename(InitialFileName:=GetFileName & ".xlsb", _
                                                          FileFilter:="Excel Binary files (*.xlsb), *.xlsb, All files (*.*), *.*")
    
                    If vFile <> False Then ThisWorkbook.SaveAs Filename:=vFile, FileFormat:=50
    
                Application.EnableEvents = True
    '            Else
    '                MsgBox ("Nothing to save yet.")
    '                Cancel = True
    '            End If
        Else
            ' Let Excel do it's thing
            ThisWorkbook.Save
        End If
    End Sub
    

    I'll still have to catch other 'Save' attempts such as Ctrl+S or closing with the X button but this works for me.

    Thanks for taking the time.

    Nick.

    Friday, May 12, 2017 9:06 AM
  • Hello Nick,

    We are glad that you resolved the issue and thanks for sharing the solution here. I suggest you mark your post as answer to close the thread.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 15, 2017 2:36 AM
    Moderator