[AccessD] Copy recordset to excel spreadsheet

Hi Chester --

Your code works here well - MS Access/Office 2003.

What code line does report Error 430 on your system? 

Thank you.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: 26 ?????? 2011 ?. 17:50
To: Access Developers discussion and problem solving
Subject: [AccessD] Copy recordset to excel spreadsheet

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

 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com