469,268 Members | 921 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,268 developers. It's quick & easy.

Closing an Excel instance

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?
Expand|Select|Wrap|Line Numbers
  1. Sub PrepareExcel(FileNme As String)
  2.  
  3. Dim xlWb As Excel.Workbook
  4. Dim xlSh As Excel.Worksheet
  5. Dim Rng As Range
  6. Dim LastRow As Long
  7. Dim LastCol As String
  8. Dim LastCell As String
  9.  
  10.     Set xlWb = Workbooks.Open(CurrentProject.Path & "\Exports\" & FileNme & ".xls")
  11.     Set xlSh = xlWb.Worksheets(FileNme)
  12.     Set Rng = xlSh.Cells
  13.  
  14.    code
  15. .
  16. .
  17. .
  18.  
  19. Finish:
  20.     xlWb.Application.DisplayAlerts = False     
  21.     xlWb.Save
  22.     xlWb.Application.DisplayAlerts = True
  23.     xlWb.Close        ' doesn't really close anything...
  24.  
  25.     Set xlSh = Nothing
  26.     Set Rng = Nothing
  27.     Set xlWb = Nothing
  28. End Sub
  29.  
Thanks in advance,

Izhar
Aug 31 '10 #1
9 19705
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
Expand|Select|Wrap|Line Numbers
  1. Sub PrepareExcel(FileNme As String)
  2.  
  3. Dim xlWb As Excel.Workbook
  4. Dim xlSh As Excel.Worksheet
  5. Dim Rng As Range
  6. Dim LastRow As Long
  7. Dim LastCol As String
  8. Dim LastCell As String
  9.  
  10.     Set xlWb = Workbooks.Open(CurrentProject.Path & "\Exports\" & FileNme & ".xls")
  11.     Set xlSh = xlWb.Worksheets(FileNme)
  12.     Set Rng = xlSh.Cells
  13.  
  14.     ' Find the last occupied row, column and cell using another function
  15.     LastRow = Last(1, Rng)
  16.     LastCol = Chr(Last(2, Rng) + 64)
  17.     LastCell = Last(3, Rng)
  18.     With xlSh
  19.         ' Change the format of the date column to "dd/mm/yyyy hh:mm:ss"
  20.         .Range("A2:A" & LastRow & " ").NumberFormat = "dd/mm/yyyy hh:mm:ss"
  21.  
  22.         'Sort the date column
  23.         .Range("A2:" & LastCell & "").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
  24.             OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
  25.  
  26.         'set color for the top row
  27.         .Range("A1:" & LastCol & "1").Interior.Color = RGB(172, 137, 243)
  28.         .Rows("2").Select       ' Freeze the Top Row
  29.         ActiveWindow.FreezePanes = True
  30.         .Columns("A:B").EntireColumn.AutoFit     'set column width
  31.     End With
  32.  
  33. Finish:
  34.     Set Rng = Nothing
  35.     Set xlSh = Nothing
  36.     xlWb.Application.DisplayAlerts = False     ' save as T_Average.xls
  37.     xlWb.Save
  38.     xlWb.Application.DisplayAlerts = True
  39.     xlWb.Close
  40.     Set xlWb = Nothing
  41. End Sub
  42.  
Thanks again,
Izhar
Aug 31 '10 #2
TheSmileyCoder
2,321 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. xlWb.Application.Close
as it is the application you want to close, not the workbook(well maybe its both :O)
Sep 1 '10 #3
Stewart Ross
2,545 Expert Mod 2GB
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):

Expand|Select|Wrap|Line Numbers
  1. xlWb.Application.Quit
-Stewart
Sep 1 '10 #4
TheSmileyCoder
2,321 Expert Mod 2GB
Yes, of course it should be .Quit. My bad, thank your for pointing that out, Delarna.
Sep 1 '10 #5
ADezii
8,800 Expert 8TB
As previously indicated, you need to incorporate the Excel Application Object into your Logic, then properly dispose of it, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim xlApp As Excel.Application
  2. Dim xlWb As Excel.Workbook
  3. Dim xlSh As Excel.Worksheet
  4.  
  5. Set xlApp = New Excel.Application
  6.  
  7. Set xlWb = xlApp.Workbooks.Open("C:\Test\Test.xls")
  8.  
  9. '...
  10.  
  11. xlApp.DisplayAlerts = False
  12. xlWb.Save
  13. xlApp.DisplayAlerts = True
  14.  
  15. xlWb.Close
  16. xlApp.Quit
  17.  
  18. Set xlWb = Nothing
  19. Set xlApp = Nothing
P.S. - Reference Code Lines 1, 5, 7, 11, 13, 16, 19.
Sep 1 '10 #6
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?
Expand|Select|Wrap|Line Numbers
  1. Sub PrepareExcel(FileNme As String)
  2.  
  3.     Dim xlApp As Excel.Application
  4.     Dim xlWb As Excel.Workbook
  5.     Dim xlSh As Excel.Worksheet
  6.  
  7.     Set xlApp = New Excel.Application
  8.     Set xlWb = Workbooks.Open(CurrentProject.Path & "\Exports\" & FileNme & ".xls")
  9.     xlWb.Application.Visible = True
  10.     Set xlSh = xlWb.Worksheets(FileNme)
  11.     Set Rng = xlSh.Cells
  12.  
  13.     ...
  14.  
  15. Finish:
  16.     Set Rng = Nothing
  17.     Set xlSh = Nothing
  18.     xlApp.DisplayAlerts = False     
  19.     xlWb.Save
  20.     xlApp.DisplayAlerts = True
  21.     xlWb.Close
  22.     Set xlWb = Nothing
  23.     xlApp.Quit
  24.     Set xlApp = Nothing
  25.  
  26. End Sub
  27.  
Sep 14 '10 #7
TheSmileyCoder
2,321 Expert Mod 2GB
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?
Sep 14 '10 #8
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...
Sep 14 '10 #9
hi,

the workbook object wb.Close takes arguments. try wb.Close(false,Type.Missing,Type.Missing);

This will work.
Feb 7 '12 #10

Post your reply

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

Similar topics

2 posts views Thread by lasmit42 | last post: by
8 posts views Thread by Tomek | last post: by
20 posts views Thread by Atchoum | last post: by
3 posts views Thread by Ian Dunn | last post: by
6 posts views Thread by pleaseexplaintome_2 | last post: by
5 posts views Thread by =?Utf-8?B?QmFyZW4=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.