[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1326
  • Last Modified:

OWC Spreadsheet

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

'ADD SPREADSHEET COLUMN HEADERS
For iCol = 0 To rs.Fields.Count - 1
    sht.Cells(1, iCol + 1) = rs.Fields(iCol).Name
Next iCol

'ADD SPREASHEET DATA
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
    rs.MoveNext
Loop

rs.Close
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.
Greg

0
CascadeGeneral
Asked:
CascadeGeneral
1 Solution
 
LucasCommented:
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
loop
 
0
 
CascadeGeneralAuthor Commented:
Thanks lucas911
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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