Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 858
  • Last Modified:

Has anyone experienced an Excel VBA ActiveWorkbook.SaveAs which does NOT save and does NOT report an error ?

I have an ActiveWorkbook.SaveAs in Excel 2013 in a subroutine which is called by the SaveAndClose routine called in the following way:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.EnableEvents = False
    Call SaveAndClose
    Application.EnableEvents = True
End Sub

The ActiveWorkbook.SaveAs fllows a fileSaveName = Application.GetSaveAsFilename(InitialName, "Excel Files (*.xlsm), *.xlsm") which works perfectly well

The file name is completely correct, but NOTHING happens on the SaveAs. No save, no error, it just continues to the next statement.

Any reason why it would do that ?

Thanks for help
Bernard
0
bthouin
Asked:
bthouin
  • 5
  • 3
1 Solution
 
bthouinAuthor Commented:
Sorry, forgot to add the SaveAs itself. I tried 2 versions, no difference in the result, which was no result.

I tried originally:
    ActiveWorkbook.SaveAs fileSaveName, xlOpenXMLWorkbookMacroEnabled

I changed it to:
    ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=52

In both cases, no joy.

I also tried ThisWorkbook instead of ActiveWorkbook. Didn't change anything.
0
 
Wayne Taylor (webtubbs)Commented:
Can you post your entire SaveAndClose routine? There may be something in there that is causing the problem.
0
 
bthouinAuthor Commented:
Hi webtubbs

Here is how it works:
Button on the main sheet (says "Save and exit")  has code:
Private Sub cmdSaveAndExit_Click()
    ThisWorkbook.Close
End Sub

Open in new window

BeforeClose and BeforeSave events look like this
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.EnableEvents = False
    Call SaveAndClose
    Application.EnableEvents = True
End Sub

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

Open in new window


And SaveAndClose:
Public Sub SaveAndClose()
    Dim myrange As Range
    Dim nAnswer As Integer
    Dim nColOlyAcc As Integer
    Dim nRowOlyAcc As Integer
    Dim nCount As Integer
    Dim jnl As Long
    Dim sTemp As String
    Dim sDefaultSaveDir As String
    Dim InitialName As String
    Dim fileSaveName As Variant
    Dim sArr As Variant
    Dim bSavedInProcessedDir As Boolean
    Dim bOK As Boolean
    Dim fs As Object
    
    On Error Resume Next
    
    ' Set no-reaction-on-changes flag
    bNoReaction = True
    
    ' Check that all mandatory fields are set
    bExitOK = True
    
... 
code checking that all mandatory fileds are filled, otherwise setting bExitOK to False
...

    ' Ask them where they want to save the Excel and under which name
    ' Get root directory 
    On Error Resume Next
    rsSQLS.Close
    On Error GoTo EH
    sTemp = "SELECT EntryValue FROM tbApplicationParameters " & _
            " WHERE ApplicationName = 'MyApp' AND Section = 'Directories' AND EntryName = 'RootDir'"
    rsSQLS.Open sTemp, connSQLS
    If Not rsSQLS.EOF Then
        sDefaultSaveDir = rsSQLS!EntryValue & "\ProcessedDataFiles\20" & Left(Worksheets("Journal").Range("JournalNr"), 2)
        InitialName = sDefaultSaveDir & "\JNL " & jnl & ".xlsm"
    Else
        InitialName = "H:\JNL " & jnl & ".xlsm"
    End If
    rsSQLS.Close
    On Error GoTo exitShutDown
    fileSaveName = Application.GetSaveAsFilename(InitialName, "Excel Files (*.xlsm), *.xlsm")
    On Error GoTo EH
    If CStr(fileSaveName) = "False" Then
        MsgBox "Your Excel will NOT be saved", , "Cancel of the Save operation"
        GoTo shutDown
    End If
    ' Save Excel under the name chosen  ***** Next statement does nothing and no error message *****
    ActiveWorkbook.SaveAs fileSaveName, xlOpenXMLWorkbookMacroEnabled
    
shutDown:
    ' Close connections and reset the corresponding fields
    bNoReaction = True
    On Error Resume Next
    Call CloseSQLServerConnection
    Set myrange = Worksheets("Journal").Range("SQLServerConnection")
    myrange.Value = "Not connected to main DB"
    myrange.Interior.Color = RGB(255, 255, 255)
    bNoReaction = False
    ' Prevent Excel to ask again about saving, which results in creating a second Excel file named "False",
    ThisWorkbook.Saved = True
    
XH:
    Exit Sub
    
EH:
    MsgBox "Error: " & Err.Description & " in BeforeClose of workbook", , "Internal error"
    Resume XH
    
exitShutDown:
    Resume shutDown

End Sub

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Wayne Taylor (webtubbs)Commented:
Try stepping through your project. In the VB Editor, place the cursor in cmdSaveAndExit_Click() then press the F8 key. Continue pressing the F8 key and see where if it even makes it to the SaveAs command.
0
 
bthouinAuthor Commented:
hi webtubbs

:-). FYI, I'm not a newbie, I'm developing in VBA mainly in Access but also in Excel since years. All the code shown in my post is mine.

I have done all the stepping x times, of course. As mentioned in my e-mail, it "does" the SaveAs command with F8, but actually nothing happens (the Excel is not saved , neither where the FileSaveName points at, nor anywhere else) and no error is reported. It just moves to the next statement as if it had done the SaveAs.

I must add that, if instead of clicking on my pushbutton, I click on the closing "button" ("X") at the top right of the screen, it goes through the SAME subroutine but it SAVES the file !

So maybe I should just ditch my specific button and just leave users to close Excel through the standard click on the "X" ...
0
 
Wayne Taylor (webtubbs)Commented:
When stepping through, does it go to "exitShutDown" after the SaveAs? That would indicate that it's not actually saving, but throwing an error.
0
 
bthouinAuthor Commented:
No, as mentioned, it just goes peacefully to the next statement, as if if had done the SaveAs ! It does NOT go to the exitShutDown, that's exactly what is puzzling. So it doesn'tencounter any problems, which of course would be explainable as it does nothing. But why it does nothing, that's the mystery. And I checked: the ThisWorkbook.Saved flag at this point is False, so it should save.

Anyway, I think I'll give up on this one, because closing by using the close "button" ("X") works. I have already removed the "Save and exit" button and the corresponding code:

Private Sub cmdSaveAndExit_Click()
    ThisWorkbook.Close
End Sub

so that the users have to click on the "X" to close. Although Excel goes exactly through the same code, that code works properly and the SaveAs is done.

So, thanks for trying to help, but I think we're stuck on a weird Excel behaviour. As I have an alternate solution, let's not break our backs on that puzzle.

Regards
Bernard
0
 
bthouinAuthor Commented:
Reason for accepting my own comments is that nobody has been able to find an answer to the problem, and I have found an alternative solution which works perfectly.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now