transfer data from excel to access using coding..

[dawn77]

help anyone ?

[beasty1711]

run a loop in excel that passes the data into a multi-dimension array.

then insert the data into Access using sql

hope this helps

[dawn77]

Originally posted by beasty1711
run a loop in excel that passes the data into a multi-dimension array.

then insert the data into Access using sql

hope this helps

i c star heee got any examples?

[tpiano]

dim objexcel as new excel.application

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open ("c:\temp.xls")
With objExcel.Worksheets(1)
For r = 1 To .UsedRange.Rows.Count
name = .Cells(r, 1).Value
address = .cells(r,2).value
xsql = "INSERT INTO table1 VALUES ('" & name & "','" &
address & "')"
adodb.execute xsql
next

note : adodb is your database connection

[dawn77]

Originally posted by tpiano
dim objexcel as new excel.application

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open ("c:\temp.xls")
With objExcel.Worksheets(1)
For r = 1 To .UsedRange.Rows.Count
name = .Cells(r, 1).Value
address = .cells(r,2).value
xsql = "INSERT INTO table1 VALUES ('" & name & "','" &
address & "')"
adodb.execute xsql
next

note : adodb is your database connection

wat if i dont know the columns name at the first place or i dont know how many columns are there

[tpiano]

with this approach it is assume the corresponding col. to take matching with your table columns. with the number of columns you can determine by usedrange.cols.count.

another simpler approached is to link the excel files in your database either in design time or in runtime..

i can help you more if you clarify further what your requirement is ?

[dawn77]

Originally posted by tpiano
with this approach it is assume the corresponding col. to take matching with your table columns. with the number of columns you can determine by usedrange.cols.count.

another simpler approached is to link the excel files in your database either in design time or in runtime..

i can help you more if you clarify further what your requirement is ?

i need to create a table in access and transfer my data in my worksheets over in this case i have about 8 columns to transfer from my worksheets and i need 2 columns to be defaulted to 0 which means i have 10 columns all in all. one more thing i realise when i run this coding it does transfer but the excel application appear to be still running in the background. is it my system problem or?..

[tpiano]

add this lines to close the excel application in the background

objexcel.activeworkbook.close
set objexcel = nothing

[dawn77]

shucks.. how do i link to the database using ADO

in DAO
set db = opendatabase("c:\test.mdb")
sql = "select * from test table"
set rs= db.openrecordset(sql,dbopendynaset)

text1.text = rs!name


erm how do we code using ADO?