Display alerts are not enabling in excel file from access vba

Question

I'm writing contents of tables from access to excel(Opening the excel file from access) After writing it i'm trying to save all workbooks in the appXL application. For that i'm making DispalyAlerts false before saving and trun on back after saving. After writing contents to excel i'm closing the access. After writing ,when i'm trying to close the excel, it is not giving any alerts like Do you want to save the contents?

My vba code

       Sub Writexl()

       Dim appXL As Excel.Application
       Dim wb As Excel.Workbook
       StrwbPath="C:\temp\sample.xls"

       Set appXL = CreateObject("Excel.Application")
       With appXL
       Set wb = .Workbooks.Open(StrwbPath)
       .Visible = True

       End With

       'here code for writing contents          


        'save workbook after writing

        appXL.Application.DisplayAlerts = False

       For Each w In appXL.Application.Workbooks
       w.Save
       Next w

        appXL.Application.DisplayAlerts = True

       DoCmd.Quit acQuitSaveAll
       Application.Quit
       End sub

Where are you closing Excel? I see an Application.Quit but that is not the Excel.Application. Also, I believe if ALL open workbooks are saved, then there is no alert to display. — David Zemens
I'm not closing excel, it still opens after writing as per requirement. Is there any way we can enable display alerts after saving the contents or writing the contents. — sam
Alerts are enabled, unless you're doing something else to turn them off. As currently described, there simply are no alerts to display when you close Excel. All the workbooks are saved. There is nothing to alert you about. — David Zemens
Did you at some point stop your code before it finished running? Or perhaps it erred after setting DisplayAlerts = False? — RubberDuck

Brad Brad · Accepted Answer · 2014-05-14T14:35:56

You are setting the DisplayAlerts to the Application's Applicaiton

appXL.Application.DisplayAlerts = True

is conceptually equivalent to

Excel.Application.Application.DisplayAlerts = True

So the property is being set on the parent application to Excel.

Try

appXL.DisplayAlerts = True

Display alerts are not enabling in excel file from access vba

2 Answers