If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel automation: excel.exe still exists in task manager
Hi
I have some code which manipulates an Excel workbook. I am new to this and do not think I have done it correctly as I can still see an Excel.exe process in the task manager after I shut the workbook down and quit excel. This process disappears once I shut my Access dbase down so its clearly my code. My code is below. A (new) Excel window pops up as the code executes and this window disappears again once it is finished - which is exactly what I want. However clearly some "excel" is still floating around as its in the task manager. However the main idea here is to (1) open an excel workbook and make it visible so we can see it, (2) do some stuff, (3) shut down the workbook AND the excel app we opened once we are done. Note that initially I coded it only using wbDataWorkbook and no myExcelApp, but this left the Excel application window open (and focused) even when I closed the workbook, which is why I moved to using myExcelApp in the first place. Im certain I have screwed up. All help appreciated Andy C Dim myExcelApp As Excel.Application Dim wbDataWorkbook As Excel.Workbook 'Open the data file in Excel, and make it visible Set myExcelApp = Excel.Application myExcelApp.Workbooks.Open filename:=strDataFile, UpdateLinks:=0 Set wbDataWorkbook = myExcelApp.ActiveWorkbook myExcelApp.Application.Visible = True 'Turn off calculations, large datasets can be really slow if recalc is on myExcelApp.Calculation = xlCalculationManual 'DOES SOME STUFF, this seems to work fine 'Clean up myExcelApp.Calculation = xlCalculationAutomatic wbDataWorkbook.Close SaveChanges:=False myExcelApp.Quit |
#2
|
|||
|
|||
Excel automation: excel.exe still exists in task manager
Hi AC,
myExcelApp.Quit Set myExcelApp = Nothing ---add this line of code Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "AC" wrote: Hi I have some code which manipulates an Excel workbook. I am new to this and do not think I have done it correctly as I can still see an Excel.exe process in the task manager after I shut the workbook down and quit excel. This process disappears once I shut my Access dbase down so its clearly my code. My code is below. A (new) Excel window pops up as the code executes and this window disappears again once it is finished - which is exactly what I want. However clearly some "excel" is still floating around as its in the task manager. However the main idea here is to (1) open an excel workbook and make it visible so we can see it, (2) do some stuff, (3) shut down the workbook AND the excel app we opened once we are done. Note that initially I coded it only using wbDataWorkbook and no myExcelApp, but this left the Excel application window open (and focused) even when I closed the workbook, which is why I moved to using myExcelApp in the first place. Im certain I have screwed up. All help appreciated Andy C Dim myExcelApp As Excel.Application Dim wbDataWorkbook As Excel.Workbook 'Open the data file in Excel, and make it visible Set myExcelApp = Excel.Application myExcelApp.Workbooks.Open filename:=strDataFile, UpdateLinks:=0 Set wbDataWorkbook = myExcelApp.ActiveWorkbook myExcelApp.Application.Visible = True 'Turn off calculations, large datasets can be really slow if recalc is on myExcelApp.Calculation = xlCalculationManual 'DOES SOME STUFF, this seems to work fine 'Clean up myExcelApp.Calculation = xlCalculationAutomatic wbDataWorkbook.Close SaveChanges:=False myExcelApp.Quit |
#3
|
|||
|
|||
Excel automation: excel.exe still exists in task manager
On Apr 9, 6:09*pm, Tom Wickerath AOS168b AT comcast DOT net wrote:
Hi AC, * * myExcelApp.Quit * * Set myExcelApp = Nothing * ---add this line of code Tom Wickerath Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/ __________________________________________ "AC" wrote: Hi I have some code which manipulates an Excel workbook. *I am new to this and do not think I have done it correctly as I can still see an Excel.exe process in the task manager after I shut the workbook down and quit excel. *This process disappears once I shut my Access dbase down so its clearly my code. My code is below. * *A (new) Excel window pops up as the code executes and this window disappears again once it is finished - which is exactly what I want. *However clearly some "excel" is still floating around as its in the task manager. *However the main idea here is to (1) open an excel workbook and make it visible so we can see it, (2) do some stuff, (3) shut down the workbook AND the excel app we opened once we are done. Note that initially I coded it only using wbDataWorkbook and no myExcelApp, but this left the Excel application window open (and focused) even when I closed the workbook, which is why I moved to using myExcelApp in the first place. Im certain I have screwed up. All help appreciated Andy C Dim myExcelApp As Excel.Application Dim wbDataWorkbook As Excel.Workbook * * 'Open the data file in Excel, and make it visible * * Set myExcelApp = Excel.Application * * myExcelApp.Workbooks.Open filename:=strDataFile, UpdateLinks:=0 * * Set wbDataWorkbook = myExcelApp.ActiveWorkbook * * myExcelApp.Application.Visible = True * * 'Turn off calculations, large datasets can be really slow if recalc is on * * myExcelApp.Calculation = xlCalculationManual * * 'DOES SOME STUFF, this seems to work fine * * 'Clean up * * myExcelApp.Calculation = xlCalculationAutomatic * * wbDataWorkbook.Close SaveChanges:=False * * myExcelApp.Quit- Hide quoted text - - Show quoted text - Thanks Tom, I appreciated it. Can you give me a bit of insight into what this line of code does, im a bit of a noob at vba but trying to learn. Cheers Andy C |
#4
|
|||
|
|||
Excel automation: excel.exe still exists in task manager
Hi Andy,
It destroys the object variable that you set at the beginning of the procedure, by using these lines of code: Dim myExcelApp As Excel.Application Set myExcelApp = Excel.Application Here are some resources that you may want to download. The first one is a compiled Help file, so if you are using Vista, I believe you will need to download some software that allows you to open .chm files: The Office XP Automation Help file is available for download http://support.microsoft.com/?id=302460 OFFAUTMN.exe discusses Office 97 and 2000 Automation and provides sample code http://support.microsoft.com/?id=253235 Also, the code you used is what is known as early bound (aka early binding). This is fine for writing and debugging VBA automation code, because it gives you Intellisence. However, it also requires a checked reference to a version specific object library. After your code is working the way you want it to, try converting it to use late binding, and deselect the checked reference to the Excel Object library. I have a sample that uses late binding that you can download: Automation with Late Binding (Calling Excel's NormInv function) http://www.accessmvp.com/TWickerath/...Statistics.zip This particular sample is designed to keep a global object variable open whenever the database is open, so you should see the same behavior that you reported, ie. an instance of Excel.exe in Task Manager until you close the Access app. However, it would have been just as easy to include code to Quit and set the object variable = Nothing when the form was closed. I just didn't implement such a method. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "AC" wrote: Thanks Tom, I appreciated it. Can you give me a bit of insight into what this line of code does, im a bit of a noob at vba but trying to learn. Cheers Andy C |
#5
|
|||
|
|||
Excel automation: excel.exe still exists in task manager
Also, the code you used is what is known as early bound (aka early binding). This is fine for writing and debugging VBA automation code, because it gives you Intellisence. However, it also requires a checked reference to a version specific object library. After your code is working the way you want it to, try converting it to use late binding, and deselect the checked reference to the Excel Object library. I have a sample that uses late binding that you can download: * * * Automation with Late Binding (Calling Excel's NormInv function) * * *http://www.accessmvp.com/TWickerath/...Statistics.zip This particular sample is designed to keep a global object variable open whenever the database is open, so you should see the same behavior that you reported, ie. an instance of Excel.exe in Task Manager until you close the Access app. However, it would have been just as easy to include code to Quit and set the object variable = Nothing when the form was closed. I just didn't implement such a method. Tom Wickerath Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/ __________________________________________ Thanks Tom I had never heard of early/late binding before, so I will look into this. It may well answer a question I was postponing and going to deal with further down the track... I intended to compile all this up into an mde file and distribute to other people to use. Would doing so still require the user to have to tick the Excel library reference on in their version of Access, or does this all get included when making the mde compiled version? If it still requires reference ticking at their end maybe I need to move towards this "late binding " concept you discussed, although to be honest I get a bit nervous about implementing using methods (late binding in this case) I am not that familiar with in case I screw it up Andy C |
#6
|
|||
|
|||
Excel automation: excel.exe still exists in task manager
On Apr 9, 6:09*pm, Tom Wickerath AOS168b AT comcast DOT net wrote:
Hi AC, * * myExcelApp.Quit * * Set myExcelApp = Nothing * ---add this line of code Tom Wickerath Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/ __________________________________________ I have just had a chance to test this, and it does not seem to do what I am after. The Excel instance stays in the task manager. I actually added 2 new lines of code: Set wbDataWorkbook = Nothing Set myExcelApp = Nothing When executing these lines of code I keep my eye on the task manager and the EXCEL.EXE simply stays there. Am I missing something? Thanks Andy C |
#7
|
|||
|
|||
Excel automation: excel.exe still exists in task manager
On Apr 14, 11:57*am, AC wrote:
On Apr 9, 6:09*pm, Tom Wickerath AOS168b AT comcast DOT net wrote: Hi AC, * * myExcelApp.Quit * * Set myExcelApp = Nothing * ---add this line of code Tom Wickerath Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/ __________________________________________ I have just had a chance to test this, and it does not seem to do what I am after. *The Excel instance stays in the task manager. I actually added 2 new lines of code: * * Set wbDataWorkbook = Nothing * * Set myExcelApp = Nothing When executing these lines of code I keep my eye on the task manager and the EXCEL.EXE simply stays there. Am I missing something? Thanks Andy C I screwed up, I was missing the NEW command on the line: Set myExcelApp = Excel.Application it should be set myExcelApp = NEW Excel.Application Once I added that the cleanup code seems to work fine. |
#8
|
|||
|
|||
Excel automation: excel.exe still exists in task manager
AC wrote:
I intended to compile all this up into an mde file and distribute to other people to use. Would doing so still require the user to have to tick the Excel library reference on in their version of Access, or does this all get included when making the mde compiled version? 1) Late binding means you or the users do not need the reference. 2) The users can't change references in an MDE. INFO: How to guarantee that references will work in your applications http://trigeminal.com/usenet/usenet026.asp?1033 (The title is misleading. smile) although to be honest I get a bit nervous about implementing using methods (late binding in this case) I am not that familiar with in case I screw it up Late binding requires changing about three lines of code usually although maybe a few more if you are using some Excel constants. These lines of code are usually just the Dim statements so it's relatively painless and easily tested. And it works! Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
Thread Tools | |
Display Modes | |
|
|