OWC Spreadsheet

Posted on 2009-04-23
Last Modified: 2012-05-06
I have an Office Spreadsheet Component 11.0 embedded in a form in MS Access 2003.  I use the following code to populate the Spreadsheet Component with data from Table1:

Option Compare Database

Private Sub Form_Load()
Dim rs As Object, sht As Object
Dim iCol As Integer, iRow As Integer

Set rs = CurrentDb.OpenRecordset("Table1")
Set sht = Me.Spreadsheet0

For iCol = 0 To rs.Fields.Count - 1
    sht.Cells(1, iCol + 1) = rs.Fields(iCol).Name
Next iCol

iRow = 3

Do While Not rs.EOF
    For iCol = 0 To rs.Fields.Count - 1
        sht.Cells(iRow, iCol + 1) = rs.Fields(iCol).Value
    Next iCol
    iRow = iRow + 1

Set rs = Nothing

End Sub

I cannot figure out how to do the reverse, and copy the data from the Spreasheet back to a table titled Table2.   I've come up with the following code as a start to at least copy the Spreadsheet data into a recordset, but if does not work:

Private Sub Command_Click()
Dim rs As Object
Dim owcWbook As OWC11.Workbook
Dim owcWsheet As OWC11.Worksheet

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

rs.Open = CurrentDb.OpenRecordset("SELECT * FROM [owcWsheet]")

Thanks in advance for your help.

Question by:CascadeGeneral
    LVL 13

    Accepted Solution

    What if you were to loop through your rows and columns in your OWC and instead of opening a record set, simply do a SQL insert statement?

    For example as a reference:

    Dim row as long, col as long, maxCol as long
    dim i as integer

    row = 1
    col = 1
    maxCol = 10
    do until owcWsheet.cells(row,col) = ""
       for i = 1 to maxcol  
         currentdb.execute "insert into your table owcwsheet.cells(row,col)"
         col = col +1
       next i
       row = row +1

    Author Closing Comment

    Thanks lucas911

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now