• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

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

0
CascadeGeneral
Asked:
CascadeGeneral
  • 3
1 Solution
 
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:
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
 
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now