Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 1st, 2004, 04:29 PM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default Class doesn't support automation error

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

  #2 (permalink)  
Old November 1st, 2004, 04:45 PM
Authorized User
 
Join Date: Oct 2004
Location: Austin, TX, USA.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"
  #3 (permalink)  
Old November 1st, 2004, 05:17 PM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #4 (permalink)  
Old November 1st, 2004, 05:57 PM
Authorized User
 
Join Date: Oct 2004
Location: Austin, TX, USA.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"
  #5 (permalink)  
Old November 2nd, 2004, 10:41 AM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


  #6 (permalink)  
Old November 2nd, 2004, 12:07 PM
Authorized User
 
Join Date: Oct 2004
Location: Austin, TX, USA.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"
  #7 (permalink)  
Old November 2nd, 2004, 12:41 PM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Similar Threads
Thread Thread Starter Forum Replies Last Post
Error-Object does not support doesn't support this bootsy Classic ASP Basics 1 May 25th, 2008 07:14 PM
Automation Error nagarajan123 Access 0 April 30th, 2008 09:22 AM
Error 430: Class does not support Automation pagates Excel VBA 0 August 22nd, 2005 02:09 PM
Outlook Automation Error www2004 Pro VB 6 4 August 12th, 2004 09:09 AM
Run-time eror 430 Class does not support Automatio patricksantos Access 1 October 3rd, 2003 04:16 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.