Solved

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

Posted on 2008-06-09
4
7,317 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

12 Experts available now in Live!

Get 1:1 Help Now