473,320 Members | 2,180 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Excel.exe instance in task manager error

Dear All,

Below is the code from the net which I am modifying, but after the code is run there is always an instance of excel whcih is running and does not end.

Expand|Select|Wrap|Line Numbers
  1. Public Sub ModifyExcelFile(sPathFile As String)
  2.  
  3.     Dim xlapp As Excel.Application
  4.     Set xlapp = New Excel.Application
  5.  
  6.     With xlapp
  7.             .Visible = True
  8.             .Workbooks.Open (sPathFile)
  9.             .Application.Sheets("Query_ExtractDetails").Select
  10.             .Application.Sheets("Query_ExtractDetails").Name = "StandardizedFile"
  11.             .Application.Sheets("StandardizedFile").Cells.Select
  12.             .Application.Selection.ClearFormats
  13.             .Application.Selection.Font.Name = "Verdana"
  14.             .Application.Selection.Font.Size = 8
  15.             .Application.Sheets("StandardizedFile").Rows("1:1").Select
  16.             .Application.Selection.Font.Bold = True
  17.  
  18.             'Convert column A to number - TextToColumns
  19.             .Application.Sheets("StandardizedFile").Range("A2", Range("A65536").End(xlUp)).Select
  20.             Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
  21.             TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  22.             Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
  23.             :=Array(1, 1), TrailingMinusNumbers:=True
  24.  
  25.             'Convert column B to number - TextToColumns
  26.             .Application.Sheets("StandardizedFile").Range("B2", Range("B65536").End(xlUp)).Select
  27.             Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
  28.             TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  29.             Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
  30.             :=Array(1, 1), TrailingMinusNumbers:=True
  31.  
  32.             'Convert column J to number - TextToColumns
  33.             .Application.Sheets("StandardizedFile").Range("J2", Range("J65536").End(xlUp)).Select
  34.             Selection.TextToColumns Destination:=Range("J2"), DataType:=xlDelimited, _
  35.             TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  36.             Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
  37.             :=Array(1, 1), TrailingMinusNumbers:=True
  38.  
  39.             'Convert column L to number - TextToColumns
  40.             .Application.Sheets("StandardizedFile").Range("L2", Range("L65536").End(xlUp)).Select
  41.             Selection.TextToColumns Destination:=Range("L2"), DataType:=xlDelimited, _
  42.             TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  43.             Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
  44.             :=Array(1, 1), TrailingMinusNumbers:=True
  45.  
  46.             .Save
  47.             .Application.ActiveWorkbook.Close
  48.     .Quit
  49.     End With
  50.  
  51.     Set xlapp = Nothing
  52.  
  53. End Sub
  54.  
Please help ...
May 2 '11 #1
5 3606
Stewart Ross
2,545 Expert Mod 2GB
At line 47 you have closed the current Excel workbook. This does not close the Excel application itself. At line 50, just add

Expand|Select|Wrap|Line Numbers
  1. xlapp.Quit
You must do this before you set the xlapp variable to nothing in line 51.

Setting the object variable to nothing does not close Excel, it just leaves it running with no further contact with the instance of VBA that started it.

-Stewart
May 2 '11 #2
I have added the quit statement at line 50, but still the instance of Excel.exe exists in the Task Manager although there are no visible excel application open.

Note that after killing the instance from the Task Manager. It works the first time but on the second run it gives the following error:
Run time error 1004: Method ‘Range ‘ of object ‘_Global’ failed.
May 2 '11 #3
Stewart Ross
2,545 Expert Mod 2GB
If you kill the instance in task manager whilst the code is still running the error you mention occurs. This is because in killing the instance in task manager you are removing Excel before it has finished executing the code.

I have tested the sub you provided, and there is one flaw which leads to a problem when I run it. At line 46 in post 1 there is a .Save command. When I run this with Excel 2007 Excel asks if I want to save a file called Resume which already exists.

Interaction with users at the file save stage can be avoided by replacing the .Save with

Expand|Select|Wrap|Line Numbers
  1.       .DisplayAlerts = False
  2.       .Save
  3.       .DisplayAlerts = True
I have run this to completion without any instances being left behind in task manager, but I am working on a simple test file which may not be like yours in functionality.

Another potential issue is that as it stands there is no error trapping in the sub. Any problem at run-time when accessing the Excel file concerned will lead to the Excel instance throwing a run-time error and disconnecting from the VBA code. Excel will then be left running until closed manually.

You will only know if that is an issue by setting a breakpoint in your code and single-stepping through it line by line to see if the code runs to completion.

-Stewart
May 2 '11 #4
Have sloved this for the momement. I was not referring the the excel object in the range statement. Have corrected the code as under and the instrance does not remain.

Expand|Select|Wrap|Line Numbers
  1.  .Range("A2", xlapp.Range("A65536").End(xlUp)).Select
  2.             .Selection.TextToColumns Destination:=xlapp.Range("A2"), DataType:=xlDelimited, _
  3.             TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
  4.             Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
  5.             :=Array(1, 1), TrailingMinusNumbers:=True
  6.  
May 3 '11 #5
Dear Stewart,

Appreciate and thank you for your guidance, I will need more of this in the future.

-Vishnu
May 3 '11 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Lina | last post by:
I use Excel in asp.net to generate xls. After finish the file generation, I use gc.collect to clear object used in the system. But I still found EXCEL.EXE through the task manager. Please advise.
2
by: Powerguy | last post by:
Hi, I am trying to write a program that is able to track an log the usage of a particular process (such as Excel.EXE) from within the task manager. The basic information that I need is: 1....
0
by: Wesley | last post by:
Hi, I'm creating a report in Excel using asp.net, but I can't release the Excel instance from memory. Excel.exe stays in the Windows Task Manager. Below is an example of the code I'm using: ...
0
by: techspirit | last post by:
Hello everyone, I want to list all the Word Documents and Excel Books listed in the Applications tab in task manager. The problem is right now my vb.net form lists only the last Word document or...
1
by: nkoranda | last post by:
I have been running into an issue that I just cant figure out. I have created an ActiveX EXE and in the Project Properties I have the Thread Model set to Thread Pool = 1. If I create an instance of...
3
by: Inbox.Mirror.Orbisoft | last post by:
In the last 1-3 weeks, we have had numerous users of Task Manager 2007 (www.orbisoft.com) complain that they can no longer export certain reports (Task Manager 2007 uses a Sagekey.com Access 97...
0
by: Inbox.Mirror.Orbisoft | last post by:
In the last 1-3 weeks, we have had numerous users of Task Manager 2007 (www.orbisoft.com) complain that they can no longer export certain reports (Task Manager 2007 uses a Sagekey.com Access 97...
9
by: pic078 via AccessMonster.com | last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files) that remains stuck in task manager after exiting the application - you can't reopen database after exiting as a result...
2
by: sheenutech | last post by:
Hello, I am using Excel 2002 in my code. Where I open, load all the necessary data and then close Excel from VC++ code. I am using following piece of code to finally quiting Excel. ...
9
by: fahadqureshi | last post by:
I am running two visual basic modules in Access and keep coming across an annoying problem. Both the vb modules transfer an excel spreadsheet to an access database but for some reason after the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.