Access VBA - Excel process still running 7

[nalmond]

I've just spent a very frustrating 3 hrs trying to close down excel processes from after a little vba manipulation in access. After reading through many forum threads I suddenly remembered that I had beaten this problem before, and had written the answer on the front of my O'Reilly Excel Macro book!

Firstly close down the excel objects properly and then add the magic 'END'. e.g.

xlWkBk.Save
Set xlWkSht = Nothing
Set xlWkBk = Nothing
xlApp.Quit
Set xlApp = Nothing
DoCmd.Hourglass False ' Switch the hourglass off
MsgBox "The expenses spreadsheet has been formatted"
End
'This little baby is the key to shutting the excel process down

Give it a go, I hope this helps prevents someone from wasting as much time as I have on this!

 

[hdorner]

THANK YOU THANK YOU THANK YOU! I too have searched and read many articles on this problem...not a single one mentioned END. That did it!!

 

[softlover]

GREAT! GREAT! GREAT "END"!

 

[DCCoolBreeze]

Also. Be sure that you close the object and release memory if you get an error...

 

[ordendelfai]

You are the man!

 

[grmman]

I try the END and I cant get excel to unload form memory.
here is my code can someone look at it anf tell me what I did wrong.

Thank you .

Option Compare Database
Dim xlapp As Excel.Application
____________________________________________________________
Private Sub cmd_8_rpt_Click()
On Error GoTo Err_cmd_8_rpt_Click

Dim xlwb As Excel.workbook
Dim sheet As Excel.Worksheet

Dim Db As DAO.Database
Dim rs1 As DAO.Recordset
Dim currentvalue As Variant
Dim currentfield As Variant
Dim workbook As Object

Set Db = CurrentDb()
Set rs1 = Db.OpenRecordset("8tranposed", dbOpenDynaset)


Set xlapp = New Excel.Application

Set xlwb = xlapp.Workbooks.Open("book1.xls&quot

Set sheet = xlapp.ActiveWorkbook.Sheets(1)
' first clear the old values out.


Rows("14:23&quot.Select
Selection.ClearContents
Rows("25:26&quot.Select
Selection.ClearContents
Rows("28:31&quot.Select
Selection.ClearContents
Rows("33:35&quot.Select
Selection.ClearContents
Rows("6:12&quot.Select
Selection.ClearContents

j = 14
Do While Not rs1.EOF
For z = 0 To rs1.Fields.Count - 1
currentfield = rs1(z)

If currentfield = "project_number" Then
sheet.Cells(6, 1).Value = "Project Number: " & rs1.Fields(1)
End If
If currentfield = "project_name" Then
sheet.Cells(7, 1).Value = "Project Name: " & rs1.Fields(1)
End If
If currentfield = "location" Then
sheet.Cells(8, 1).Value = "Location: " & rs1.Fields(1)
End If
If currentfield = "prepared_by" Then
sheet.Cells(9, 1).Value = "Prepared By: " & rs1.Fields(1)
End If
If currentfield = "prepared_date" Then
sheet.Cells(10, 1).Value = "Prepared Date: " & rs1.Fields(1)
End If
If currentfield = "checked_by" Then
sheet.Cells(11, 1).Value = "Checked By:" & rs1.Fields(1)
End If
If currentfield = "checked_date" Then
sheet.Cells(12, 1).Value = "Checked By: " & rs1.Fields(1)
End If



Next z

rs1.MoveNext
j = j + 1
Loop

' this prints out the whole workbook.
' Sheets.Application.ActiveSheet.PrintOut

'Sheets.Application.ActiveSheet.PrintPreview
xlapp.Visible = True
xlwb.ActiveSheet.PrintOut , , , True

sheet.Application.ActiveWorkbook.Save
sheet.Application.ActiveWorkbook.Close


xlapp.Quit
Set xlapp = Nothing
Set xlwb = Nothing
Set sheet = Nothing
Set Db = Nothing
Set rs1 = Nothing
END

Exit_cmd_8_rpt_Click:
Exit Sub

Err_cmd_8_rpt_Click:
MsgBox Err.Description
Resume Exit_cmd_8_rpt_Click

End Sub

 

[nalmond]

Sorry for the late response, holidays & a funeral have kept me away from my desk.

I find that the END command can sometimes cause problems when called from inside a procedure. Try calling END once all the work, this and other procedures, has been done. If you don't you'll probably end the program prematurely.