Solved

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

Posted on 2014-10-16
8
662 Views
Last Modified: 2014-11-01
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
Comment
Question by:bthouin
  • 5
  • 3
8 Comments
 
LVL 1

Author Comment

by:bthouin
ID: 40384704
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40385634
Can you post your entire SaveAndClose routine? There may be something in there that is causing the problem.
0
 
LVL 1

Author Comment

by:bthouin
ID: 40386277
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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40390979
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
 
LVL 1

Author Comment

by:bthouin
ID: 40391736
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40393250
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
 
LVL 1

Accepted Solution

by:
bthouin earned 0 total points
ID: 40394476
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
 
LVL 1

Author Closing Comment

by:bthouin
ID: 40416999
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question