 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / March 2008I cannot get Excel to quit
When running code in Access I open an excel workbook to eventually be exported to a pdf file. Upon completion of this, I wnat to close the excel workbook and it indeed appears to be closed and I am returned to Access application. However, when I attempt to repeat the process I get a 'global' error and I look in my running processes and find that Excel is still running even though I have set the variables to zero, quit the application and closed the workbook.. What more can I do other than throw a bomb at it? HELP Mike Hi Mike, Make sure you have released all the other object variables as well -- for instance, your sheet and workbook variables when you are cleaning up your variables, did you do this: set obvar = nothing if this does not resolve the problem, can you post the code you are using to 1. dimension your variables 2. clean them up ? Have an awesome day Warm Regards, Crystal MVP Microsoft Access strive4peace2006@yahoo.com > When running code in Access I open an excel workbook to eventually be > exported to a pdf file. Upon completion of this, I wnat to close the excel [quoted text clipped - 6 lines] > HELP > Mike Hi Crystal, I have the same problem that Mike has. My code quits Excel on the screen but leaves Excel running in the Task Manager. My project is to transfer an Access Table to Excel and convert it to a Pivot Table. To do this each time I have to delete the file and exit Excel. Here is part of my code referring to this issue: Dim MySheetPath As String MySheetPath = "E:\LA Snr Ctr\Activity Log.xls" Dim XL As Object Dim XLBook As Object Dim XLSheet As Object Set XL = CreateObject("Excel.Application") DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Activity Log", _ "E:\LA Snr Ctr\Activity Log.xls", True Set XLBook = GetObject(MySheetPath) XL.Visible = True XLBook.Windows(1).Visible = True Set XLSheet = XLBook.Worksheets(1) ...... Fin: XLBook.Close False XL.Quit Kill "E:\LA Snr Ctr\Activity Log.xls" Set XLSheet = Nothing Set XLBook = Nothing Set XL = Nothing Any suggestions on where the error is would be greatly appreciated John134 > Hi Mike, > [quoted text clipped - 29 lines] > > HELP > > Mike What, exactly is it you are wanting to do? Is there any code where the ........ is? In other words, are you manipulating the worksheet at all, or are you just using this method to open the spreadsheet for the user to view? If so, I wouldn't create it as a object, rather I would use the Shell command to open it after creating it with the TransferSpreadsheet.  SignatureDave Hargis, Microsoft Access MVP > Hi Crystal, > [quoted text clipped - 65 lines] > > > HELP > > > Mike There is code there to convert the spreadsheet to a pivot table. John134 > What, exactly is it you are wanting to do? > Is there any code where the ........ is? In other words, are you [quoted text clipped - 73 lines] > > > > HELP > > > > Mike Any reason you don't use a pivot table query instead? SignatureDave Hargis, Microsoft Access MVP > There is code there to convert the spreadsheet to a pivot table. > [quoted text clipped - 77 lines] > > > > > HELP > > > > > Mike The pivot table query is very limited in its formatting. The Excel pivot table is far superior in clarity. John134 > Any reason you don't use a pivot table query instead? > [quoted text clipped - 79 lines] > > > > > > HELP > > > > > > Mike True enough. Well, then since you didn't post all the code, it is difficult to tell if there is an unseen problem. I do see one place the error could happen. Set XLBook = GetObject(MySheetPath) There is no reference to the XL object you created. This line really should be: Set xlBook = XL.Workbooks.Open(MySheetPath, 0, True) Also, you dont' really need to do the TransferSpreadsheet. That may also be an issue. Instead, you could use the CopyFromRecordset method to load the data into your spreadsheet. The most usual cause of an instance of Excel being left behind is incomplete object referencing. You have to be very careful to associate all the Excel objects all the time. What can happen is if Access can't determine which Excel object a reference belongs to, it will instansiate another instance of Excel on its own. Then when you Quit the instance you created, the one Access created will be left open. SignatureDave Hargis, Microsoft Access MVP > The pivot table query is very limited in its formatting. The Excel pivot > table is far superior in clarity. [quoted text clipped - 84 lines] > > > > > > > HELP > > > > > > > Mike Thanks, Mike, for your replies. I have finally solved the problem through lots of trial and error with each line of code. I didn't realize, since this is my first time with this application, that each Acccess coded statement operating on the Excel worksheet must have a reference object. With these in the proper places, I no longer have Excel running in the Task Manager after completing the subroutine. I hope this reply may be of help to others. Thanks again, John134 > True enough. > [quoted text clipped - 108 lines] > > > > > > > > HELP > > > > > > > > Mike Who's Mike? SignatureDave Hargis, Microsoft Access MVP > Thanks, Mike, for your replies. I have finally solved the problem through > lots of trial and error with each line of code. I didn't realize, since this [quoted text clipped - 120 lines] > > > > > > > > > HELP > > > > > > > > > Mike Some guy that looks just like you. He was here a minute ago ... >Who's Mike? Darn, my evil twin is using another alias. SignatureDave Hargis, Microsoft Access MVP > Some guy that looks just like you. He was here a minute ago ... > > >Who's Mike? My apologies. I meant Dave. John134 > Who's Mike? > [quoted text clipped - 122 lines] > > > > > > > > > > HELP > > > > > > > > > > Mike No apology necessary. I was just having some fun. SignatureDave Hargis, Microsoft Access MVP > My apologies. I meant Dave. > [quoted text clipped - 126 lines] > > > > > > > > > > > HELP > > > > > > > > > > > Mike This problem is most often caused by insufficient object referencing. When you establish the Excel object, you must make sure that all references to the Excel object are fully qualified. If you establish another object you think is attached to your Excel Application object and it is not fully qualified, Access tries to figure out where to assign it. If it cannot, it will actually create another instance of Excel without your knowing it is happening. Then, at least two problems arise. One you have seen. That is that when you issue your xlApp.Quit, You are quitting the instance you created, but Access doesn't know to quit the one it created, so you will see it in the Processes tab of Task Manager. The other thing that can happen is that Access will get confused on which object it is suppossed to act on when you address a method or property of your Excel object. So, the bottom line is be sure you fully qualify all your references to the Excel Application object. Be sure when you are shutting it down, you do it in the correct order. 1.Close the workbook 2.Quit the application 3.Set all your object references to Nothing. > When running code in Access I open an excel workbook to eventually be > exported to a pdf file. Upon completion of this, I wnat to close the excel [quoted text clipped - 6 lines] > HELP > Mike |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.