Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
820 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

721 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