Solved

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

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 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

11 Experts available now in Live!

Get 1:1 Help Now