closing excel - tried everything

[Michel Peeters]

Below is my code to paste a recordset into an existing XL spreadsheet.
Problem is Excel remains open in the task bar.
I have been reading all day long the discussion forms, tried everything, but
nothing works.
How can I close Excel?



Option Compare Database
Option Explicit
Dim strPad As String
Dim dbD As DAO.Database
Dim rsD As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim j As Integer
Dim i As Integer
Dim strLabel As String
Dim strSQL As String
Sub StartCloseXL()
Set xlApp = CreateObject("Excel.Application")
strPad = "C:\Documents and Settings\Michel\Desktop\KPProductie.xls"
Set xlBook = xlApp.Workbooks.Open(strPad)
Set dbD = DBEngine(0)(0)
strSQL = .... 'follows a correct SQL string
Set rsD = dbD.OpenRecordset(strSQL)
xlBook.Worksheets("Artikel").Activate
xlBook.Worksheets("Artikel").Columns("A:C").Select
xlBook.Worksheets("Artikel").Columns("A:C").ClearContents
' create the Excel labels.
j = 1
For i = 0 To rsD.Fields.Count - 1
strLabel = rsD.Fields(i).Name
xlBook.Worksheets("Artikel").Cells(j, i + 1).Value =
strLabel
Next i
'paste recordset
xlBook.Worksheets("Artikel").Range("A2").CopyFromRecordset
rsD
'save and close
xlBook.Close SaveChanges:=False
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
rsD.Close
dbD.Close
End Sub

 

[Michel Peeters]

Anybody who can help me?
Or is there something wrong with my question?
tks

 

[Terry Kreft]

Well this works here:-

Notes
1) I've moved the declarations into the proedure, if there was some
reason they were outside then move them back.
2) I've changed the variables where you assigned constants to actual
constants. Again if there was some reason for the way you were doing it
change it back.

Sub StartCloseXL()
Dim dbD As DAO.Database
Dim rsD As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim i As Integer
Dim strLabel As String

Set xlApp = CreateObject("Excel.Application")

Const strPad$ = "C:\Documents and
Settings\Michel\Desktop\KPProductie.xls"
Const WORK_BOOK = "Artikel"

Const strSQL$ = "SELECT * FROM tblControl"
Const j% = 1

xlApp.Visible = True

Set xlBook = xlApp.Workbooks.Open(strPad)
Set dbD = DBEngine(0)(0)
Set rsD = dbD.OpenRecordset(strSQL)
With xlBook
With .Worksheets(WORK_BOOK)
.Activate
.Columns("A:C").Select
.Columns("A:C").ClearContents
For i = 0 To rsD.Fields.Count - 1
strLabel = rsD.Fields(i).Name
.Cells(j, i + 1).Value = strLabel
Next i
.Range("A2").CopyFromRecordset rsD
End With
.Close SaveChanges:=False
End With
Set xlBook = Nothing

rsD.Close
dbD.Close
Set rsD = Nothing
Set dbD = Nothing

xlApp.Quit
Set xlApp = Nothing

End Sub

 

[Michel Peeters]

tks Thierry, but it does not help.
Do you know a good way to remove an Excel instance from the taskpane?
Michel