Excel Automation Anybody??

[gauravmarwaha]

We are using OLE Automation. The Excel object does not get destroyed after the application has finished. This probably is happening due to the large amt of data being printed bcos if some less data is printed then everything wrks fine. Pls help us with some explanation or solution

Thanx

[mojoribbit]

This may seem to be a silly question, but are you closing the object in the code:

ExcelObject.close

If you are doing that, then I don't know what to suggest, I have writting programs and dlls that regularly create 5 to 10 MB excel files and have never had a problem with the object not closing...unless it errors for some reason.

[Roy Vinyard]

I've got a similar problem. I create an Excel workbook OK & close it with:
oBook.SaveAs ExcelFile, xlWorkbookNormal
oExcel.Quit
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing

but Excel still seems to be in memory & when I try to create another workbook, I get error messages. If I exit the program, Excel also disappears.

[mojoribbit]

I'm not sure what the difference between the close and the quit command is, but I would suggest using
excelobject.close
instead of excelobject.quit, just because I haven't had that problem before and it is what I use. It may not work, but it is worth a try

As previously said will only work when you close, not quit excel.

[Roy Vinyard]

Thanks for your response. I've tried replacing oExcel.Quit
with oExcel.Close but I get an error message:

438 Object doesn't support thisproperty or method.

so I guess I must be doing something else wrong. I'm declaring:

Dim oExcel As Object
Dim oBook As Object

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(SummaryFile)
' SummaryFile is a csv file

' Code here to change some column widths etc. then...

oBook.SaveAs ExcelFile, xlWorkbookNormal
oExcel.Quit
Set oBook = Nothing
Set oExcel = Nothing


Incidentally, I'm using Excel 97 & W98.

[Terence]

I also had that annoying experience before. With some research in MSDN, I got several possible reasons for out of process component, in this case Excel, not closing. If the component is still executing, it won't close even if you ask it. So, is your Excel still executing or preparing the print out when your program is issuing the request. This might not be related or true, but I am also interested to find out if huge printing will deter termination. What about let the program not ask Excel to print but simply ask it to close, would it work ?

Finally, 'close' method only close the workbook, and Excel will not end. 'Quit' will actually terminate it.

[Roy Vinyard]

I received a further message from Fiona sugesting declaring:
Dim xla As Excel.Application
Dim xlb As Excel.Workbook
Dim xls As Excel.Worksheet

then...
xlb.Close
xla.Quit
to end

This works & has solved my problem.
I'm not printing from within Excel, so I can't offer any advice on that.

Terence,
using the code I gave to Roy means the spreadsheet is saved so can be opened and printed at any time and the size of it is not a problem. If you want to print try inserting a print statement before closing then the print will finish before the application closes.

Fiona