Closing Excel from Access leaves image running in background? 2

[CptCrusty1]

I'm doing some data manipulation in Excel from an Access Form (VBA in a command button) When I'm done, I tell Access to close the spreadsheets, however, if I hit CTRL+ALT+DEL and look at Task Manager, There will still be in image(?) of Excel running, sometimes more than one. This causes all kinds of havoc in trying to manually check the spreadsheet for accuracy.

A colleague says I can tell Access to take care of the, she just doesn't remember how.... Any ideas?

 

[PHV]

When you manipulate ANY excel object from another app always qualify it:
myXLobj.Range

Why not posting your code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CptCrusty1]

PHV, my code is rather arduious and long... so I didn't post it. Actually, my code works fine, I just can't get the instances of Excel to close, even though I'll close the particluar spreadsheet. For example....;

After declaring my variables:


Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open("s:\MTD_Snapshot.xls", False, False)
xlApp.Visible = True


This is what I used to open one of the two spreadsheets I'm manipulating.

I'll copy and past some records......

xlapp02.Range("C2:E49").Select
xlapp02.Selection.Copy

xlApp.Range("B6").Select
xlApp.ActiveSheet.Paste

And then I'll close the spreadsheets like this:

xlApp.quit
xlApp02.quit

Then just before the code is to end:

xlApp = Nothing
xlApp02 = Nothing

I thought this last piece was to ensure that the Excel.exe application clears from the running processes. BUT, I still have them floating around.

I hope this is a better explanation... my fingers are tired now... as well, I haven't had my coffee yet...

Thanks
Richard.

 

[CptCrusty1]

**CORRECTION**

I'm using


SET xlApp = Nothing
SET xlApp02 = Nothing


I forgot to put the SET in my last post, however, it IS in my code.

Thx.

 

[PHV]

No wb.Close nor Set wb = Nothing ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CptCrusty1]

OK... I put that in there... completely forgot about it. I'm still having the same issue tho. It seems, that in order for me to run the code, I have to have

Call SHELL (path to Excel.exe)

Then open my spreadsheets. If I don't have an instance of Excel open, a Data-Sort in Excel fails everytime.... I think....

but then, after everything is done, I still can't get it to close completely out of memory.

RK

 

[PHV]

Double check you have NO implicit instantiation of the Excel.Application object, the most likely is an unqualified reference to Selection or Range.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

No, you should definately NOT Shell Excel for this to run, or Shell Excel when running this.

Since you insist on being secretive about your code (perhaps a good choice, if it is of some length, to spare us...), do a search in this forum. I'd suggets trying the terms CreateObject, Excel and either my signature or part of PHV's signature (PH) as criterion. Among some threads that are perhaps not very relevant, you should find both explicit advice, links and explanations...

BTW - how is the progress on thread705-1021836?

Roy-Vidar

 

[CptCrusty1]

Hi Roy. You've helped me before...many times... thanks in advance.

Actually, My earlier post was pretty much the guts of it. If you want.... I can post the entire code, although, its over 100 lines. I'll remove the Call Shell... but it seems to like it.

The other thread is involving the same chunk of code that is being contankerous and evil. The range that I'm copying is not empty, rather, completely filled with data (which is why I'm copying it). I Save, then close the Spreadsheet, then open the next spreadsheet and paste. That took care of the problem...


I've double checked every Range Selection to ensure it was followed by a copy or delete instead of just a select range.

Next I've removed the Call Shell.
Call Shell("c:\program files\microsoft office\office\excel.exe")

When I remove the Call shell, the app fails as soon as it gets to the the Data-Sort

xlapp02.Range("B1:F96").Select

xlapp02.Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom



But when I put the Call Shell back in, the app runs, as long as there are no other instances of Excel in memory. Below is my entire chunk of code:

Private Sub cmdOpenExcel_Click()

Call Shell("c:\program files\microsoft office\office\excel.exe")


Open_CustSvcRpt 'run sub which opens the CustSvcRpt.xls spreadsheet.


' OPEN AND MANIPULATE CustSvcRpt.xls

xlapp02.Windows("CustSvcRpt.xls").Activate
'ActiveWindow.WindowState = xlNormal
'ActiveWindow.WindowState = xlNormal
xlapp02.Sheets("Sheet1").Select
xlapp02.Sheets.Add
xlapp02.Sheets("Sheet1").Select
xlapp02.Cells.Select
xlapp02.Selection.Copy
xlapp02.Sheets("Sheet2").Select
xlapp02.Range("A1").Select
xlapp02.Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

xlapp02.Columns("C:F").Select
xlapp02.Selection.Delete Shift:=xlToLeft

xlapp02.Columns("D:G").Select
xlapp02.Selection.Delete Shift:=xlToLeft

xlapp02.Columns("E:H").Select
xlapp02.Selection.Delete Shift:=xlToLeft

xlapp02.Columns("E:Z").Select
xlapp02.Selection.Delete Shift:=xlToLeft

xlapp02.Columns("F").Select
xlapp02.Selection.Delete Shift:=xlToLeft

xlapp02.Columns("G:IV").Select
xlapp02.Selection.Delete Shift:=xlToLeft

xlapp02.Rows("1:57").Select
xlapp02.Selection.Delete Shift:=xlUp

xlapp02.ActiveWindow.SmallScroll Down:=9
xlapp02.Rows("18:36").Select

xlapp02.Selection.Delete Shift:=xlUp
xlapp02.ActiveWindow.SmallScroll Down:=66

xlapp02.Rows("82:100").Select
xlapp02.Selection.Delete Shift:=xlUp

xlapp02.ActiveWindow.SmallScroll Down:=15
xlapp02.Rows("98:130").Select

xlapp02.Selection.Delete Shift:=xlUp
xlapp02.ActiveWindow.SmallScroll Down:=-129

xlapp02.Range("B1").Select
xlapp02.ActiveCell.FormulaR1C1 = "1"

xlapp02.Range("C1").Select
xlapp02.ActiveCell.FormulaR1C1 = "2"

xlapp02.Range("D1").Select
xlapp02.ActiveCell.FormulaR1C1 = "3"

xlapp02.Range("E1").Select
xlapp02.ActiveCell.FormulaR1C1 = "4"

xlapp02.Range("F1").Select
xlapp02.ActiveCell.FormulaR1C1 = "5"
'xlapp02.Range("B1").Select
'xlapp02.Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
'OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

xlapp02.Range("B1:F96").Select

xlapp02.Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

xlapp02.Rows("50:6705").Select

xlapp02.Selection.Delete Shift:=xlUp

xlapp02.ActiveWorkbook.Save

'Close_CustSvcRpt 'close CustSvcRpt.xls spreadsheet
'Windows Clipboard has copied materials for pasting.

open_MTD_Snapshot


xlApp.Worksheets("Daily").Activate
xlApp.Range("B6:B53").Select
xlApp.Selection.ClearContents
xlApp.Range("C6:C53").Select
xlApp.Selection.ClearContents

xlApp.Range("D653").Select
xlApp.Selection.ClearContents

xlApp.Range("F6:F53").Select
xlApp.Selection.ClearContents

xlApp.ActiveWorkbook.Save


xlapp02.Range("C2:E49").Select
xlapp02.Selection.Copy


xlApp.Range("B6").Select
xlApp.ActiveSheet.Paste
xlApp.ActiveWorkbook.Save




xlapp02.Range("F2:F49").Select
xlapp02.Selection.Copy



xlApp.Range("f6").Select
xlApp.ActiveSheet.Paste
xlApp.ActiveWorkbook.Save


'COPY THE DATA TO THE "DailyData" worksheet for import into Access
xlApp.Range("A6:G53").Select
xlApp.Selection.Copy
xlApp.Worksheets("DailyData").Select
xlApp.ActiveWindow.SmallScroll Down:=-11
xlApp.Range("A2").Select
xlApp.Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
xlApp.ActiveWorkbook.Save


wb.Close
wb02.Close

Set ws = Nothing
Set ws02 = Nothing

Set wb = Nothing
Set wb02 = Nothing


xlApp.Application.Quit
xlapp02.Application.Quit

Set xlApp = Nothing
Set xlapp02 = Nothing
'Run Append query with links to the MTD xls Report and appends to the DATA table.


DoCmd.SetWarnings False

DoCmd.OpenQuery "qryAPP_Append_CustInfo_Input_Date", acViewNormal, acEdit
'Data area in Excel is linked to Access, via append query
'data is appended to Access table.
DoCmd.SetWarnings True




'DoCmd.Quit acQuitSaveNone


End Sub

Public Sub Open_CustSvcRpt()

Set xlapp02 = CreateObject("Excel.Application")
Set wb02 = xlapp02.Workbooks.Open("s:\0-Defa~1\Symposium\CustSvcRpt.xls", False, False)
xlapp02.Visible = True



End Sub

Public Sub open_MTD_Snapshot()

Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open("s:\0-Defa~1\Symposium\Cust_Serv_MTD_Snapshot.xls", False, False)
xlApp.Visible = True


End Sub


I'm stumped.....

 

[PHV]

At least one culprit here:
Key1:=Range("B2"),

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CptCrusty1]

Thats part of the Data Sort from within Excel. The "B2" is the start of the sort range. What can you suggest to fix this?

 

[PHV]

Key1:=xlapp02.Range("B2"),

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dmksas]

I have not seen how you are declaring your instance of Excel within this code..

is it Dim xlApp As Object

or is it Dim xlApp As Excel.Application

 

[CptCrusty1]

Hmmm... I posted a reply saying.....

WOOHOOOOO IT WORKED!!!!!!!!! WOOOHOOOO.... however.. I don't see that thread here.... SO....

PHV and RoyVider

WOOHOOOOOO , IT WORKED!!!!! It was that stupid Key1: yadda yadda.... WOOOHOOOO!!! I gave both of you stars for your help.


Oh... DMK... DIM xlApp as Object I have it in a global module so I can call them from anywhere....

THanks all
Rich.