Office Spreadsheet Component

I have and MS Access MDB with a form named Form1.  I have an Office Spreadsheet Component 11.0 named Spreadsheet0 inserted in Form1.

On Form1_Load, I want to read records from MS Access table Table1, and write them into Spreadsheet0.  

I activated the following refrences:
1) Microsoft DAO 3.6 Object Library
2) Microsoft Office Web Components 11.0
3) Microsoft ActiveX Data Objects 2.1 Libarary

I am using the following code:
Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim owcWbook As OWC11.Workbook
Dim owcWsheet As OWC11.Worksheet

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)

Set owcWbook = Me.Spreadsheet0.ActiveWorkbook
Set owcWsheet = owcWbook.ActiveSheet

owcWsheet.Cells.CopyFromRecordset rst

End Sub

When Form1 loads, I get an error which reads "Runtime Error 430, Class Does Not Support Automation....".  Is this because CopyFromRecordset can only be used within Excel, or with Excel Automation?  If I cannot use CopyFromRecordset, is there any other method I can use to write the Table1 records to Spreadsheet0 without looping through all of the records?

Thanks,
Greg

CascadeGeneralAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
Here is your code, note always close objects!

Im assuming you do not have adodb added so I created it as an object
otherwise you can add adodb data objects 2.8 in and define type of ADODB.Recordset



Private Sub Form_Load()
    
    Dim rst As Object
    Dim owcWbook As OWC11.Workbook
    Dim owcWsheet As OWC11.Worksheet
    
    Set rst = CreateObject("ADODB.Recordset")
    rst.Open "SELECT * FROM Table1", CurrentProject.Connection
    
    Set owcWbook = Me.Spreadsheet0.ActiveWorkbook
    Set owcWsheet = owcWbook.ActiveSheet
    
    Me.Spreadsheet0.Cells.CopyFromRecordset rst
 
    rst.Close
    Set rs = Nothing
End Sub

Open in new window

0
 
tbsgadiCommented:
Hi Greg,

The CopyFromRecordset is only available in Excel.
To see what you can do with the Office Spreadsheet Component have a look at
http://www.microsoft.com/downloads/details.aspx?FamilyID=090AC128-1CDE-4BAE-9EF5-B01B6A5E31E2&displaylang=en


Good Luck!

Gary
0
 
rockiroadsCommented:
You have to use ADO recordsets in order to populate

0
 
rockiroadsCommented:
I forgot the remove redundant lines

    Dim owcWbook As OWC11.Workbook
    Dim owcWsheet As OWC11.Worksheet


    Set owcWbook = Me.Spreadsheet0.ActiveWorkbook
    Set owcWsheet = owcWbook.ActiveSheet


0
 
CascadeGeneralAuthor Commented:
Thanks for your help Rockiroads
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.