[AccessD] Copy recordset to excel spreadsheet

Kaup, Chester Chester_Kaup at kindermorgan.com
Tue Apr 26 08:49:32 CDT 2011


I am using the following code to copy a recordset to an excel spreadsheet. Just to be safe I check for missing references and found none.It generates an error
Run time error 430
Class does not support Automation or does not support expected interface.
What might I be doing wrong?

Function CopytoSpreadsheet()

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset
Dim fld As Field

Dim strExcelFile As String


Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Add
Set xlWS = xlWB.Worksheets(1)
xlWS.Name = "Worksheet1"

Set rst = CurrentDb.OpenRecordset("tbl DirectoryName")

rst.MoveFirst
xlWS.Range("A2").CopyFromRecordset rst

xlWB.SaveAs "C:\files\Excel\SSTest.xls"
rst.Close

Set rst = Nothing

End Function

Chester Kaup
Engineering Technician
Kinder Morgan CO2 Company, LLP
Office (432) 688-3797
FAX (432) 688-3799

 





More information about the AccessD mailing list