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
Solved

How to resolve runtime error '50290': Method 'Quit' of object '_Application' failed ?

Posted on 2008-06-09
4
7,462 Views
Last Modified: 2013-12-05
I am developing an Access 2003 application using Access and Excel as the front end and SQL Server as the back end database.

I am trying to give the user the option to either SAVE or DISCARD an Excel file that is generated.

In the Attached Code Snippet, when the compiler reaches the following line of code as a result of
answering the MsgBox question: Do you wish to keep a copy of the workbook?"
-----------------------------------------------------------------------------------------------------------------
app.Quit          

An Excel file is opened that has no grid lines and is empty. There is a message box on the Excel file:

Microsoft Office Excel is waiting for another application to complete an OLE action.
OK button.

after the line app.Quit is executed, the following Run time error occurs:

Runtime error '50290':
Method 'Quit' of object '_Application' failed

Do you know how I can close the Excel file programatically and resolve this error ?


type ClassModule  [CExcelMonitor (Code)]
----------------------------------------------------------------------
Option Compare Database
Private wbk As Excel.Workbook
Private WithEvents app As Excel.Application
Private mstrFilePath As String
Private blnKillFile As Boolean
 
Public Property Set MonitoredWorkbook(wbkIn As Excel.Workbook)
   Set wbk = wbkIn
   Set app = wbk.Application
   mstrFilePath = wbkIn.FullName
End Property
 
Private Sub app_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
   If Wb.FullName = wbk.FullName Then
      app.EnableEvents = False
      app.Visible = False
      AppActivate "Microsoft Access"
      If MsgBox("Do you wish to keep a copy of the workbook?", vbQuestion + vbYesNo, _
                           "Keep workbook?") = vbNo Then
         blnKillFile = True
         wbk.Close False
         Kill mstrFilePath
      Else
         wbk.Close True
      End If
      app.Visible = True
      app.EnableEvents = True
   End If
   app.Quit         <----------------- Breakpoint 
End Sub
------------------------------------------------------------------------
 
Dim clsMonitor As CExcelMonitor
 
Private Sub UDLSummaryByBranch()
                  
    str_sql1 = "If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblUDLSummary' AND TYPE = 'U') DELETE FROM tblUDLSummary"
    DoCmd.RunSQL (str_sql1)
                   
    Set com = New ADODB.Command
    With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procUDLSummaryByBranch"
       .ActiveConnection = CurrentProject.Connection
       Set rstQueryFS = .Execute
    End With
        
    ExportedFile = strAccessPath0 & strAccessPath7 & "SUMMARY_BY_BRANCH" & "_" & intYearSP & "_" & Format(Now, "mmddhhnnss") & ".XLS"
         
    DoCmd.TransferSpreadsheet acExport, 8, "tblUDLSummary", ExportedFile, True, ""
    Beep
    MsgBox "Summary By Branch records have been exported to Excel", vbOKOnly, ""
    If isFileExist(ExportedFile) Then StartDocACS ExportedFile
    DoCmd.Hourglass False
End Sub
 
Private Sub StartDocACS(filename)
 Dim xlApp As Excel.Application
 Dim xlWB As Excel.Workbook
 Dim xlWS As Excel.Worksheet
 'open excel template
 Set xlApp = New Excel.Application
 xlApp.Visible = True
 Set xlWB = xlApp.Workbooks.Open(filename)
 Set clsMonitor = New CExcelMonitor
 Set clsMonitor.MonitoredWorkbook = xlWB
 Set xlWS = xlWB.Worksheets(1)
 xlWS.Columns.AutoFit
 xlApp.ScreenUpdating = True
End Sub

Open in new window

0
Comment
Question by:zimmer9
  • 2
4 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21748304
zimmer9,

What are you doing with an Excel "front end" that you cannot do with one Access front end?

(Especially if the Excel part is giving you trouble.)

JeffCoachman
0
 

Author Comment

by:zimmer9
ID: 21748404
My users expect to see their reports in Excel because they are accountants and they are comfortable with Excel. Therefore, I use stored procedures to generate a result set and transfer the result set to an Excel file. However, I would like to have the user explicitly deteremine whether or not the Excel file is saved to minimize disk space on the Network drive.

That is why I want the user to answer the question "Yes" or "No" as to whether or not to save the Excel file that is created as a result of the DoCmd.TransferSpreadsheet command. I want a mechanism in place whereby the Excel file will not be created and saved automatically everytime the user generates a report. The user may not take the time to delete unwanted Excel files.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21748606
OK,

Thanks
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 21750424
If you're calling it from a form, then you can change the class to the below and the Excel instance will remain running invisibly in the background until you close the form:

Option Compare Database
Option Explicit
 
Private wbk As Excel.Workbook
Private WithEvents app As Excel.Application
Private mstrFilePath As String
Private blnKillFile As Boolean
 
Public Property Set MonitoredWorkbook(wbkIn As Excel.Workbook)
   Set wbk = wbkIn
   Set app = wbk.Application
   mstrFilePath = wbkIn.FullName
End Property
 
Private Sub app_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
   If Wb.FullName = wbk.FullName Then
      app.EnableEvents = False
      app.Visible = False
      AppActivate "Microsoft Access"
      If MsgBox("Do you wish to keep a copy of the workbook?", vbQuestion + vbYesNo, _
                           "Keep workbook?") = vbNo Then
         blnKillFile = True
         Wb.Close False
         Kill mstrFilePath
      Else
         Wb.Close True
      End If
      app.EnableEvents = True
   End If
   Set wbk = Nothing
End Sub
Private Sub Class_Terminate()
   On Error Resume Next
   Set wbk = Nothing
   app.Quit
   Set app = Nothing
End Sub

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

860 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