Hi All,

I am trying to create an excel file(see below for my code) through access and I get an error saying Run-time error 430: Class doesnt support automation

Can someone please help!

Private Sub cmdGetReport_Click()

Dim adoConn As New ADODB.Connection
Dim adoRec As New ADODB.Recordset
Dim xlApp As Excel.Application
Dim strsql As String
Dim xlWB As Excel.Workbook
Dim strfilename As String
Dim i As Long

Set xlApp = New Excel.Application

adoConn.CommandTimeout = 0
adoConn.Open "DSN=mays"

strsql = "select * from dw.maysoon_proc_test"
adoRec.Open strsql, adoConn


xlApp.Workbooks.Add
Set xlWB = xlApp.ActiveWorkbook

For i = 0 To adoRec.Fields.Count - 1
    Cells(1, i + 1).Value = adoRec.Fields(i).Name
Next

xlWB.Sheets(1).Range("A2").CopyFromRecordset adoRec<----ERROR ON THIS LINE<-------------

strfilename = "C:\TESTING2.xls"
 If Len(Dir(strfilename)) Then
    Kill strfilename
 End If

adoConn.Close

End Sub

Help says:

If the Recordset object contains fields with OLE objects in them, this method fails.

Does your recordset contain any OLE Object fields?


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

Hi John,

No, I the recordset does not contain OLE object. When a friend of mine runs this, he doesnt get the error ---he's using access and excel versions 10.0

I am using access and excel version 8.0---could this be a reason for my error?

Thanks for your quick reply!
Mays

Ayup. I don't think Excel 97 supports that method. I have Access 97 on some of my machines, but I no longer have Excel 97 installed anywhere, so I cannot test it for you. You'll have to navigate through the recordset yourself and "paste" the fields one at a time into the Excel worksheet cells.


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

I changed my code to the following: (I "pasted" each cell one at a time) and now the error I get is Run-time error '1004' Application-defined or object-defined error. Any suggestions would be greatly appreciated!

Thanks!!
Private Sub cmdGetReport_Click()

Dim adoConn As New ADODB.Connection
Dim adoRec As New ADODB.Recordset
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim strsql As String
Dim xlWB As Excel.Workbook
Dim strfilename As String
Dim i As Long

Set xlApp = New Excel.Application

adoConn.CommandTimeout = 0
adoConn.Open "DSN=dw"




strsql = "select * from dw_play.mays_proc_test"
adoRec.Open strsql, adoConn


Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Add
Set xlSheet = xlWB.Worksheets("Sheet1")

For i = 0 To adoRec.Fields.Count - 1
   xlSheet.Cells(1, i + 1).Value = adoRec.Fields(i).Name
Next


Dim rownum As Long
adoRec.MoveFirst
Do Until adoRec.EOF
      *******ERRORS HERE***********************
      xlSheet.Cells(rownum, 1).Value = adoRec!tran_start_date
      xlSheet.Cells(rownum, 2).Value = adoRec!tran_end_date
      xlSheet.Cells(rownum, 3).Value = adoRec!total_transactions
      xlSheet.Cells(rownum, 4).Value = adoRec!orig_less_curr
      xlSheet.Cells(rownum, 5).Value = adoRec!percent_orig_less_curr
      xlSheet.Cells(rownum, 6).Value = adoRec!orig_less_sell
      xlSheet.Cells(rownum, 7).Value = adoRec!percent_orig_less_sell
      xlSheet.Cells(rownum, 8).Value = adoRec!curr_less_sell
      xlSheet.Cells(rownum, 9).Value = adoRec!percent_curr_less_sell
      adoRec.MoveNext
      rownum = rownum + 1
Loop


strfilename = "C:\TESTING2.xls"
If Len(Dir(strfilename)) Then
    Kill strfilename
End If

adoConn.Close

End Sub

Well, you're starting with Rownum = 0, and an Excel worksheet does not have a Row zero. Because you pasted the field names in Row 1, don't you want to start with Rownum = 2?



John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

That did it! Thanks so much for all your help!