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
578 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
Comment Utility
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)
Comment Utility
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
Comment Utility
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:bthouin
Comment Utility
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)
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now