Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-06-09
4
Medium Priority
?
7,960 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
[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
  • 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 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

598 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