Trying to get rid of stubborn, baked-on Excel buildup 3

[DBSSP]

I know this is gonna seem kinda..well, dumb, but I'm trying to figure out how to get Excel to close from Access. Currently, I have Access opening text files in Excel, saving the text files as Excel workbooks, and then it closes Excel, Deletes existing tables and imports the workbooks as a new set of tables. Everything works wonderfully except, xl.application.quit, isn't closing the instance of Excel. Any ideas? Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[oharab]

Try just XL.Quit it works for me!

Ben ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[DBSSP]

No go on that one. It was worth the try, though. Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[EliseFreedman]

Have a look at this link. It may solve your problem

http://www.mvps.org/access/general/gen0017.htm

 

[DBSSP]

That was interesting, as I had not thought to do that. Still didn't work though...The only other thing I can think of is to make the instance visible and let the user close the window manually. Kinda defeats the purpose of automation though.... Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[oharab]

Are you getting any error messages?
It could be that your object XL is going out of scope before you've finished with it.
Paste your code here & let us have a look through it.

Cheers

Ben ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[combo]

If you assigned a variable named xl to the excel instance, then xl.Quit, as Ben pointed, should work (if not, this may be the reason why it does not work). The only problem should be with open workbooks - save them first or use xl.displayalerts=false to avoid prompting to save (changes will not be saved in that case).

combo

 

[DBSSP]

I've shortened it down as there are about 15 more instances of opening, saving, and closing.

******Begin Code*********

Function Grab()

Dim intResponse As Integer
Dim XL As Object
Set XL = CreateObject("Excel.Application&quot


XL.Workbooks.OpenText Filename:= _
"V:\CMS\State of the Center\HR Project\Imports\bt1.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array _
(3, 1), Array(4, 1))
Rows("1:8&quot.Select
Selection.Delete Shift:=xlUp
Range("A1&quot.Select
ChDir "V:\CMS\State of the Center\HR Project\Import Convert"
XL.ActiveWorkbook.SaveAs Filename:= _
"V:\CMS\State of the Center\HR Project\Import Convert\bt1.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
XL.ActiveWindow.Close

MsgBox "Transfer completed successfully. Closing Excel", vbInformation, "Success"

XL.Quit
Set XL = Nothing

*****End Code***** Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[DBSSP]

Does anybody have any idea how to kill this thing?! I've tried everything I can think of. Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[proxide]

I had something like this the other day. I had my excel application object set up:-
Dim xlApp As Excel.Application
Dim xlWBook As Excel.Workbook

and I then went and created a worksheet using the the line:-

xlWBook = Excel.Application.WorkBooks.new

which should have read:-

xlWBook = xlApp.Application.WorkBooks.new

As I had been using the first option it created a separate excel object which the closing lines at the end:-

xlWBook.Close
xlApp.Quit
Set xlWBook = Nothing
Set xlApp = Nothing

couldn't release and caused no end of problems for me.

My suggestion, look back through your code and check for any Excel.Application objects not declaired at the beginning.

 

[TLady]

I can assure everyone if Excel will visibly close itself - open Task Manager and you will see - it is still running(Processes Tab).
It will close when Access.mdb is close.
I've been fighting it for month, conversing with MS about it - no luck. When Access Quit - Excel fully Quit, that is !
TIA

P.S xlWBook.Close
xlApp.Quit
Set xlWBook = Nothing
Set xlApp = Nothing

is OK if you don't have to open another instance of Excel right away.

 

[oharab]

Have you tried doing some mickey mouse code?

Dim XL as Object
Dim xlWB as Object
Set XL = CreateObject("Excel.Application&quot
Set xlWB=XL.Workbooks.Open("C:\test.xls&quot
xlWb.close false
xl.quit

set xlwb=nothing
set XL=nothing

Will that work?

Make it as simple as possible, get it working, then add on the extra stuff until you find the bit of code causing the problem.

hth

Ben ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------