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
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