Error 430 Can't automate between Access and Excel

[Shamous]

This doesn't work and it should. Any idea why?

xlApp.Range("A2").CopyFromRecordset (rs)

It tells me that the object is not automatable?

Thanks

Rusty

PS: here is all of the code

Public Sub ExportToExcel(strSQL As String)
On Error GoTo HandleErrors

Dim response As String

response = _
MsgBox("This action may take several minutes." & _
vbCrLf & "Do you wish to continue...?", _
vbQuestion + vbOKCancel)

If response = vbOK Then
DoCmd.Hourglass True

Dim rs As DAO.Recordset
Dim xlApp As New Excel.Application
Dim intCol As Integer
Dim strFileName As String

Set rs = CurrentDb.OpenRecordset(strSQL)
Set xlApp = Excel.Application
xlApp.Workbooks.Add

'Add header row to spreadsheet
For intCol = 0 To rs.Fields.Count - 1
xlApp.Cells(1, intCol + 1).Value = _
rs.Fields(intCol).NAME
'Debug.Print rs.Fields(intCol).NAME
Next

'Move data from the local recordset to Excell
xlApp.Range("A2").CopyFromRecordset rs

strFileName = xlApp.GetSaveAsFilename( _
"ExcelOutput", _
fileFilter:="MS Excel Files (*.xls), *.xls")
If Len(strFileName) > 0 Then
xlApp.ActiveWorkbook.SaveAs _
Filename:=strFileName
MsgBox "File save complete!", vbInformation
Else
MsgBox "File save aborted by user!", vbCritical
xlApp.ActiveWorkbook.Close False
End If

xlApp.Quit

Set xlApp = Nothing
Set rs = Nothing

End If

ExitHere:
DoCmd.Hourglass False
Exit Sub

HandleErrors:
MsgBox Err.Description & " " & Err.Number

DoCmd.Hourglass False
Resume ExitHere

End Sub

 

[PHV]

Does your recordset contains OLE fields ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Shamous]

I don't think so, please explain further.

 

[PHV]

please explain further
The help file clearly state that the Range.CopyFromRecordset don't work if the Recordset contains OLE fields.
Feel free to play with the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Shamous]

The fields are only numeric or text, and there are no OLE fields present.

 

[RoyVidar]

Then let's try another angle...

Though the errormessage isn't familiar, one guess would be your way of declaring and referencing Excel objects. Would you by any chance have an extra instance of Excel in memory (check with Task Manager)?

It's recommended to avoid declaring with the New keyword, and perhaps use createobject when creating an instance of Excel. Also often recommended is to declare objects for workbook and worksheet.

[tt]Dim xlApp As Excel.Application
dim wr as excel.workbook
dim sh as excel.worksheet
Set xlApp = createobject("Excel.Application")
set wr=xlApp.Workbooks.Add
set sh=wr.worksheets("sheet1")
...[/tt]

Some info Excel automation fails second time code runs, INFO: Troubleshooting Error 429 When Automating Office Applications

Again, I'm not sure this is the cause, but...

Roy-Vidar