Help with CopyFromRecordset

edp1959

Registered User.
Local time
Today, 12:04
Joined
Aug 24, 2003
Messages
23
Sub yourname()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim dbs As DAO.database
Dim rst As DAO.recordset

I got this code from a previous post here to be used for transfering data to a cell range in excel. I get a "run-time error 430" Class does not support automation or does not support expected interface. The code line identified is
xlBook.Sheets(1).Range("A1").CopyFromRecordset rst

Any ideas on why I'm getting this error. I have Microsoft Excel 9.0 Object Library selected in my references. I'm using Access 2000.

*****Code*******
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("query1", dbOpenDynaset) 'name of your query = query1
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Book1.xls") 'location and name of your file = C:\Book1.xls

xlApp.Windows(1).Visible = True
xlBook.Sheets(1).Range("A1").CopyFromRecordset rst 'sheet 1, starting at a1 - copies down and accross to fit record set
xlBook.Close savechanges:=True
xlApp.Quit

Set rst = Nothing
Set dbs = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Sub
 
ok, I believe I see what your problem is. You have declared your variables using Early Binding and you have set your variables using late binding. Try:
Code:
Set xlApp=New Excel.Application
instead of
Code:
Set xlApp = CreateObject("Excel.Application")
 

Users who are viewing this thread

Back
Top Bottom