|
Subject:
|
Class doesn't support automation error
|
|
Posted By:
|
clueless_may
|
Post Date:
|
11/1/2004 3:29:25 PM
|
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
|
|
Reply By:
|
JLovell
|
Reply Date:
|
11/1/2004 3:45:21 PM
|
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"
|
|
Reply By:
|
clueless_may
|
Reply Date:
|
11/1/2004 4:17:46 PM
|
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
|
|
Reply By:
|
JLovell
|
Reply Date:
|
11/1/2004 4:57:54 PM
|
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"
|
|
Reply By:
|
clueless_may
|
Reply Date:
|
11/2/2004 9:41:23 AM
|
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
|
|
Reply By:
|
JLovell
|
Reply Date:
|
11/2/2004 11:07:36 AM
|
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"
|
|
Reply By:
|
clueless_may
|
Reply Date:
|
11/2/2004 11:41:12 AM
|
That did it! Thanks so much for all your help!
|
