populate table from excel..???

okay, if i have a spreadsheet and i want to have a button in my vb6 project so that when i push it the spreadsheet populates a table in access... right now i am doing it this way.....

************************************************
Dim oAccess As access.Application
Set oAccess = New access.Application

oAccess.OpenCurrentDatabase "c:\Database"

oAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "query", "C:\Spreadsheet.xls", True
oAccess.Quit
************************************************

Is there another way to do it through code... or any other way...
thanks for the help
shawn

well, I have done some work with Excel and Access automation. Here is another potential technique to try...

This example is actually a macro you could use from EXCEL OR Access. In this example, it is running from Excel and taking data from the active worksheet and populating an Access Database. THe same logic works the other way around...open an Excel Object from Access and bring in the row and column data. See below: you need to tweak this for your individual needs, but it shows a procedure for communicating between the two applications.



' exports data from the active worksheet to Access
' re-write the logic to go from an empty table to Excel via the Excel
'Object library

Public Sub getFromExcel()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long

' connect to the Access database and open your recordset

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\myTest.mdb;"

Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM myTable", cn, adOpenKeyset, adLockOptimistic, adCmdTable

'the starting row in the worksheet set to the r variable
'will we bind this with the column to get the actual data spot
r = 3
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell is found in column A
'you need to tweak this as needed
With rs
.AddNew
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
'continue as needed
.Update
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

neat approach..ill mess with it some.. thanks...

The main benefit to this method (the coding is somewhat longer than using the native Access Import commands) is you have more flexibility about what records you bring in and you can limit results. Also text can be re-formatted before bringing it into your table...etc.

This is raw, but if you tweak it to meet your conditions, you should have success with it.

tried it and get this error...

compile error: sub or function not defined

and it goes to range .... any ideas

This is where you need to play with the sample code and get the parameters you need :).

You are trying this from the Excel worksheet correct? Basically the Range() method takes two parameters:

Range(Cell1, Cell2)

so in the code sample I posted, if the range value doesn't exist, it will throw an error..look at the active worksheet you are using..and see where the data is (row and column) you need to coordinate this information into your algorithm. If you look above, in my example, I start at row 3 and I iterate down and across within my loop. You just need to get your rows and columns correct.

well, I have done some work with Excel and Access automation. Here is another potential technique to try...

This example is actually a macro you could use from EXCEL OR Access. In this example, it is running from Excel and taking data from the active worksheet and populating an Access Database. THe same logic works the other way around...open an Excel Object from Access and bring in the row and column data. See below: you need to tweak this for your individual needs, but it shows a procedure for communicating between the two applications.



' exports data from the active worksheet to Access
' re-write the logic to go from an empty table to Excel via the Excel
'Object library

Public Sub getFromExcel()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long

' connect to the Access database and open your recordset

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\myTest.mdb;"

Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM myTable", cn, adOpenKeyset, adLockOptimistic, adCmdTable

'the starting row in the worksheet set to the r variable
'will we bind this with the column to get the actual data spot
r = 3
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell is found in column A
'you need to tweak this as needed
With rs
.AddNew
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
'continue as needed
.Update
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub



hI, MKOSLOF

I am a newbie....
But during the import from access is possible to inserta a controll on a column of Excel Sheet?

Example

I import today a table into sheet. OK!

The next day i import the same table, is possible to jump to the nex record of table if record existis alreday into sheet importaed in the last day?

For example on this line
.Fields("FieldName2") = Range("B" & r).Value
Tks.