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. -
Public Sub ModifyExcelFile(sPathFile As String)
-
-
Dim xlapp As Excel.Application
-
Set xlapp = New Excel.Application
-
-
With xlapp
-
.Visible = True
-
.Workbooks.Open (sPathFile)
-
.Application.Sheets("Query_ExtractDetails").Select
-
.Application.Sheets("Query_ExtractDetails").Name = "StandardizedFile"
-
.Application.Sheets("StandardizedFile").Cells.Select
-
.Application.Selection.ClearFormats
-
.Application.Selection.Font.Name = "Verdana"
-
.Application.Selection.Font.Size = 8
-
.Application.Sheets("StandardizedFile").Rows("1:1").Select
-
.Application.Selection.Font.Bold = True
-
-
'Convert column A to number - TextToColumns
-
.Application.Sheets("StandardizedFile").Range("A2", Range("A65536").End(xlUp)).Select
-
Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
-
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
-
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
-
:=Array(1, 1), TrailingMinusNumbers:=True
-
-
'Convert column B to number - TextToColumns
-
.Application.Sheets("StandardizedFile").Range("B2", Range("B65536").End(xlUp)).Select
-
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
-
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
-
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
-
:=Array(1, 1), TrailingMinusNumbers:=True
-
-
'Convert column J to number - TextToColumns
-
.Application.Sheets("StandardizedFile").Range("J2", Range("J65536").End(xlUp)).Select
-
Selection.TextToColumns Destination:=Range("J2"), DataType:=xlDelimited, _
-
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
-
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
-
:=Array(1, 1), TrailingMinusNumbers:=True
-
-
'Convert column L to number - TextToColumns
-
.Application.Sheets("StandardizedFile").Range("L2", Range("L65536").End(xlUp)).Select
-
Selection.TextToColumns Destination:=Range("L2"), DataType:=xlDelimited, _
-
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
-
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
-
:=Array(1, 1), TrailingMinusNumbers:=True
-
-
.Save
-
.Application.ActiveWorkbook.Close
-
.Quit
-
End With
-
-
Set xlapp = Nothing
-
-
End Sub
-
Please help ...
5 3606
At line 47 you have closed the current Excel workbook. This does not close the Excel application itself. At line 50, just add
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
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.
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 - .DisplayAlerts = False
-
.Save
-
.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
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. -
.Range("A2", xlapp.Range("A65536").End(xlUp)).Select
-
.Selection.TextToColumns Destination:=xlapp.Range("A2"), DataType:=xlDelimited, _
-
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
-
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
-
:=Array(1, 1), TrailingMinusNumbers:=True
-
Dear Stewart,
Appreciate and thank you for your guidance, I will need more of this in the future.
-Vishnu
Sign in to post your reply or Sign up for a free account.
Similar topics
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.
|
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....
|
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:
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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.
...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
| |