I wrote a code which exports an access table to excel and then manipulates it.
it works OK, but leaves an EXCEL.EXE line in the task bar...
what am I doing wrong? -
Sub PrepareExcel(FileNme As String)
-
-
Dim xlWb As Excel.Workbook
-
Dim xlSh As Excel.Worksheet
-
Dim Rng As Range
-
Dim LastRow As Long
-
Dim LastCol As String
-
Dim LastCell As String
-
-
Set xlWb = Workbooks.Open(CurrentProject.Path & "\Exports\" & FileNme & ".xls")
-
Set xlSh = xlWb.Worksheets(FileNme)
-
Set Rng = xlSh.Cells
-
-
code
-
.
-
.
-
.
-
-
Finish:
-
xlWb.Application.DisplayAlerts = False
-
xlWb.Save
-
xlWb.Application.DisplayAlerts = True
-
xlWb.Close ' doesn't really close anything...
-
-
Set xlSh = Nothing
-
Set Rng = Nothing
-
Set xlWb = Nothing
-
End Sub
-
Thanks in advance,
Izhar
9 20896
after reading many pages of people with the same problem I realized that the code is relevant to the closing proccess so here's the whole thing -
Sub PrepareExcel(FileNme As String)
-
-
Dim xlWb As Excel.Workbook
-
Dim xlSh As Excel.Worksheet
-
Dim Rng As Range
-
Dim LastRow As Long
-
Dim LastCol As String
-
Dim LastCell As String
-
-
Set xlWb = Workbooks.Open(CurrentProject.Path & "\Exports\" & FileNme & ".xls")
-
Set xlSh = xlWb.Worksheets(FileNme)
-
Set Rng = xlSh.Cells
-
-
' Find the last occupied row, column and cell using another function
-
LastRow = Last(1, Rng)
-
LastCol = Chr(Last(2, Rng) + 64)
-
LastCell = Last(3, Rng)
-
With xlSh
-
' Change the format of the date column to "dd/mm/yyyy hh:mm:ss"
-
.Range("A2:A" & LastRow & " ").NumberFormat = "dd/mm/yyyy hh:mm:ss"
-
-
'Sort the date column
-
.Range("A2:" & LastCell & "").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
-
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
-
-
'set color for the top row
-
.Range("A1:" & LastCol & "1").Interior.Color = RGB(172, 137, 243)
-
.Rows("2").Select ' Freeze the Top Row
-
ActiveWindow.FreezePanes = True
-
.Columns("A:B").EntireColumn.AutoFit 'set column width
-
End With
-
-
Finish:
-
Set Rng = Nothing
-
Set xlSh = Nothing
-
xlWb.Application.DisplayAlerts = False ' save as T_Average.xls
-
xlWb.Save
-
xlWb.Application.DisplayAlerts = True
-
xlWb.Close
-
Set xlWb = Nothing
-
End Sub
-
Thanks again,
Izhar
Is this code running from within Access or from within another instance of excel?
I dont see you creating an Excel appplication in your code, thats why I am wondering.
Have you tried:
as it is the application you want to close, not the workbook(well maybe its both :O)
Smiley is right that when you close the current workbook you are not closing the application - it is similar to when you close all active worksheets from within Excel itself, as the very last worksheet closed also closes the current active workbook but leaves Excel running until it is exited from the menu.
You can use the Quit method of the application to close the application and exit Excel (there is no Close method of the Excel Application object, unfortunately):
-Stewart
Yes, of course it should be .Quit. My bad, thank your for pointing that out, Delarna.
As previously indicated, you need to incorporate the Excel Application Object into your Logic, then properly dispose of it, as in: - Dim xlApp As Excel.Application
-
Dim xlWb As Excel.Workbook
-
Dim xlSh As Excel.Worksheet
-
-
Set xlApp = New Excel.Application
-
-
Set xlWb = xlApp.Workbooks.Open("C:\Test\Test.xls")
-
-
'...
-
-
xlApp.DisplayAlerts = False
-
xlWb.Save
-
xlApp.DisplayAlerts = True
-
-
xlWb.Close
-
xlApp.Quit
-
-
Set xlWb = Nothing
-
Set xlApp = Nothing
P.S. - Reference Code Lines 1, 5, 7, 11, 13, 16, 19.
Hi everyone,
Thank you very much for the help,
I tried everything that you told me.
While debugging I saw that xlWb.Close closes the excel rectangle from the task bar but leaves the instance in the task manager.
xlApp.quit doesn't seem to do anything... (that's why I left it out in the first place).
Any thoughts? -
Sub PrepareExcel(FileNme As String)
-
-
Dim xlApp As Excel.Application
-
Dim xlWb As Excel.Workbook
-
Dim xlSh As Excel.Worksheet
-
-
Set xlApp = New Excel.Application
-
Set xlWb = Workbooks.Open(CurrentProject.Path & "\Exports\" & FileNme & ".xls")
-
xlWb.Application.Visible = True
-
Set xlSh = xlWb.Worksheets(FileNme)
-
Set Rng = xlSh.Cells
-
-
...
-
-
Finish:
-
Set Rng = Nothing
-
Set xlSh = Nothing
-
xlApp.DisplayAlerts = False
-
xlWb.Save
-
xlApp.DisplayAlerts = True
-
xlWb.Close
-
Set xlWb = Nothing
-
xlApp.Quit
-
Set xlApp = Nothing
-
-
End Sub
-
If you code fails, it will not properly close the excel instance. Sometimes while debuffing my code, I can end up with 5+ open (and hidden) excel applications. Are you sure the excel application is not a "left-over" from previous attempts?
What I did was open the task manager while working "step by step" through my code.
The excel instance only appears when it is opened in this function.
The code reaches the end without any breaks, so i assume all the rest is OK...
hi,
the workbook object wb.Close takes arguments. try wb.Close(false,Type.Missing,Type.Missing);
This will work.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: lasmit42 |
last post by:
Guys
I am writing a program which reads the first cell of many Excel
spreadsheets and then takes some actions. The problem that I am
experiencing is that each Excel instance I create persists...
|
by: Tomek |
last post by:
Hi,
My next question is about excel instance in VB.NET
I have following code:
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
oXL =...
|
by: Atchoum |
last post by:
I have a DLL that opens an Excel workbook and add-in.
If I close the app that calls the DLL before closing Excel, when I close
Excel everything is fine.
But if I close Excel while the app is still...
|
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: Ian Dunn |
last post by:
I'm simply trying to access an instance of Excel that has been opened
manually by the user in order to put a few values in the existing sheet.
Here's the code I've tried:
Dim oXL As...
|
by: Jono |
last post by:
Hello,
I've been getting this message when closing excel (not necessarily when closing the workbook by itself, but when closing Excel and the workbook at the same time):
...
|
by: alexia.bee |
last post by:
Hi all,
In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.
here is a snipcode
public...
|
by: pleaseexplaintome_2 |
last post by:
Help please. The excel instance is removed from task manager when I
run the code below. If I uncomment the lines pertaining to a
workbook, the instance of excel is not removed from task manager. ...
|
by: =?Utf-8?B?QmFyZW4=?= |
last post by:
Hi! All,
I am using a ASP.NEt application, wherein i am automating Execl object. I
am not able to destroy the instance, eve if I write this to destroy the
instance of Excel. Please find the ...
|
by: Ronin85 |
last post by:
Hi , I recently have much pain working with excel application especially closing excel . I try several method but with no success i try to urge garbage collector to dispose excel application object,...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |