Cannot Close Excell from VB Code 1

[valdosta]

Hi,

The Excel is not closed after calling the vb code below. Help, please!


Dim xlObject As Object

Set xlObject = CreateObject("excel.application")

xlObject.Application.Workbooks.Open "C:\Report\Report.xls"
Worksheets("report").Activate

......
Worksheets("report").Cells(1, 2).Value = strRptValue
......


xlObject.Application.ActiveWorkbook.Save
xlObject.Application.ActiveWorkbook.Close

xlObject.Application.Workbooks.Close

xlObject.Quit
Set xlObject = Nothing

 

[chiph]

There is a thread somewhere here about it (do a search). From what I can remember, you have to make sure you free all the objects that you access in the reverse order in which you accessed/created them.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[kmfna]

Hey Valdosta,

You may want to try: set xlObject=nothing

good luck,
Kevin

- "The truth hurts, maybe not as much as jumping on a bicycle with no seat, but it hurts.

 

[fredericofonseca]

kmfna,

He has that on his code.
Look at his last line.

as chiph mentioned it is possible that somewhere on the code that is missing another object reference has been made, and this will need to be freed also.

Apart from that the code looks fine.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[kmfna]

oooo.....DENIIIIEEED..my fault. Totally overlooked that. In this case, ignore my suggestion!

Kevin

- "The truth hurts, maybe not as much as jumping on a bicycle with no seat, but it hurts.

 

[bjd4jc]

Valdosta does do a

Set xlObject = Nothing

as you suggested. It is the last line of his/her post.

johnwm is on the right track. I have also experienced EXCEL and WINWORD remaining open (as show in the process list) even when I was not using any automation. I wonder if they have some sort of cahce built in like Internet Explorer does so it doesn't take so long to load subsequent times.... what you're seeing may very well be a (use the term loosely) *feature* and not a real problem.

 

[RoyVidar]

Stepping in from the VBA world

- you're using unqualified references to excel objects/properties:

Worksheets("report").Activate

Such implicit reference might create a new instance of Excel (check Task Manager) - all of those need to be fully qualified thru your object variables, for instance like this:

Dim xlObject As Object
dim wr as object
dim sh as object

Set xlObject = CreateObject("excel.application")

set wr = xlObject.Workbooks.Open "C:\Report\Report.xls"
set sh = wr.worksheets("report")
sh.activate

.....
sh.Cells(1, 2).Value = strRptValue

For more info Excel automation fails second time code runs

Roy-Vidar

 

[valdosta]

Hi,

Thanks everybody for your help! The example in the link provided by RoyVidar (A Star for you) closes the excel. But it does not update in my case. I use xlApp.Workbooks.Open instead of xlApp.Workbooks.Add. It updates when the line xlBook.Saved = True is changed to xlBook.Save. Thanks again indeed!