Excel Worksheet Problems

[Mikey]

I have seen a lot of code on how to open up an Excel worksheet with a click event on a form, but how do I save the worksheet then close Excel by exiting the Excel application through Excel and not another click event on my form.

I have a form, with a command button, that when clicked opens Excel and brings up a templete worksheet. When I'm done filling in the worksheet I close it by File-Exit or X-Close. This leaves the object, Excel, active.

[Tryster]

If I understand your question correctly, it sounds like you are not freeing the object.

When you are done with the object set it to Nothing.

ie Set <XL Object Name> = Nothing

Is this what you're asking?

[Mikey]

I understand the need to free the object with set object=nothing but my code reads

On Error Resume Next
Set appExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set appExcel = CreateObject("Excel.Application")
End If
Error.Clear
On Error GoTo 0
Set wbSheet = appExcel.Workbooks.Open(App.Path & "\Sheet2.xls")
wbSheet.Application.Visible = True
wbSheet.Application.Caption = "Current Cardholder's Lab Sheet"
wbSheet.ActiveSheet.SaveAs (App.Path & "\LabSheet.xls")

At this point the object (excel) is started, the worksheet is open. I then modify the worksheet and close it, from the worksheet. How do I then free the object?

[vbLeah]

Try putting:

appExcel.application.quit

before you set it to nothing.

This is what I do, and it works fine:

Dim xlApp As Object
Set xlApp = CreateObject("Excel.sheet")

' put stuff in spreadsheet

xlApp.saveas "C:\My Documents\Myfile"

' Close Excel with the Quit method on the Application object
xlApp.application.quit

' Release the object variable.
Set xlApp = Nothing

[Mikey]

I'm sorry I'm not making myself clear. Perhaps I'll get the hang of this if I keep at it.

My click event sets the object and opens a worksheet.
I can not put the code in this same event that would quit the object and set it to nothing.

I have to find a way to quit and set the object when I close the worksheet (from the worksheet close event?) because I can't expect the user of this application to do another click event, that would quit and set the object after they closed the worksheet.

Perhaps this is not pratical and the reason I've not seen any code for this situation.

Thanks,
Mikey

[vbLeah]

Maybe there's some kind of query_unload you could use? Other than that I don't know, but good luck!