ActiveX component can't create object or return reference t

[sll810]

I am trying to use VBA in access to open excel and copy a recordset in. I am using the code below.

Sub CopyRecordSet(InRecSet As Recordset)

Dim xlApp As Excel.Application, xlNewBook As Excel.Workbook, xlDestSheet As Excel.Worksheet
Dim fname As String
Dim fld As DAO.Field

Dim i As Integer

Set xlApp = GetObject(, "excel.application")
Set xlNewBook = xlApp.Workbooks.Add
Set xlDestSheet = xlNewBook.Worksheets.Add

i = 1
For Each fld In InRecSet.Fields
xlDestSheet.Cells(1, i).Value = fld.Name
i = i + 1
Next fld
xlDestSheet.Range("A2").CopyFromRecordset InRecSet

End Sub


I keep getting an error at the line :
Set xlApp = GetObject(, "excel.application")

The error states "ActiveX component can't crete object or return reference to this object (Error 429)"

I have enabled references, including Active X controls 2.1. Does anyone have any suggestion about why I can't get excel to open. THe first time I ran this code it worked, now it won't anymore....

 

[sll810]

I found a solution.

Sub CopyRecordSet(InRecSet As Recordset)

Dim xlApp As Excel.Application, xlNewBook As Excel.Workbook, xlDestSheet As Excel.Worksheet
Dim fld As DAO.Field

Dim i As Integer
Set xlApp = New Excel.Application
xlApp.Visible = True


Set xlNewBook = xlApp.Workbooks.Add
Set xlDestSheet = xlNewBook.Worksheets.Add

i = 1
For Each fld In InRecSet.Fields
xlDestSheet.Cells(1, i).Value = fld.Name
i = i + 1
Next fld
xlDestSheet.Range("A2").CopyFromRecordset InRecSet


For i = xlNewBook.Sheets.Count To 2 Step -1
xlNewBook.Sheets(i).Delete
Next i



End Sub

Note I used :
Set xlApp = New Excel.Application
xlApp.Visible = True

instead of GetObject()

 

[Norie]

Why not just use TransferSpreadsheet?