problem closing excel after entering data

[aine]

Okay I Know this is a problem already dealt with in a number of posts But I have Tried the solutions and Excel is still hanging when I try To close it.

If I open an Excel application and worksheet and then close with out doing anything to the sheet I have no problem and I can reopen and close as many times as I like without reseting my project.

However if I enter any data in the workbook, excel won't close properly. It looks like its closed but it is still open in taskmanager if I use Ctrl Alt del and I have To reset my project inorder to open excel again.

I have included two sets of code the first set is working, the second isn't. Someone please tell me why.
Thanks in advance for the advice.


Code:'code working
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook

Private Sub cmdClose_Click()

xlWkb.Close False
Set xlWkb = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub

Private Sub CmdTry_Click()

Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWkb = xlApp.Workbooks.Add

'xlApp.DisplayAlerts = False

End Sub


Code://code not working
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlSht As Excel.Worksheet

Private Sub cmdClose_Click()

xlWkb.Close False
Set xlSht = Nothing
Set xlWkb = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub

Private Sub CmdTry_Click()

Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWkb = xlApp.Workbooks.Add
Set xlSht = xlWkb.Worksheets("Sheet1&quot
Range("A1&quot = "You Did It"

'xlApp.DisplayAlerts = False

End Sub

 

[aine]

Okay I solved this one myself after hours of trial and error and i will tell you how incase anyone makes a similar mistake.

The Problem was that I had to reference every entry or change with the names I had given the Application, workbook and worksheet.

So wereas above I entered data into a cell using
Range("A1&quot = "Test"
I should have used
xlSht.Range("A1&quot = "Test"
Or
xlSht.Cells(1, 1) = "Test"

I couldn't use any instance of ActiveSheet or ActiveWorkbook either only my declaration names, xlApp, xlWkb and xlSht.

This Is probably an easy enough mistake to make if you are copyung code from a recorded macro as it seems to work okay and All the data is entered normally the only problem is an excel process is hanging after closing.

 

[meredithtx]

Aine,
You are wonderful! I've been searching to this answer FOREVER. I can finally get Excel closed!! Thank you!