transfer range of cells from excel to Acess using vba
Hi there,
I am working on a script to transfer a *lot* of data between excel and
MS Access. Currently it has a lot of loops, transfering a single value
each time, for example:
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\...\Output.mdb;"
Set rs = New ADODB.Recordset
rs.Open "Output_Table", cn, adOpenKeyset, adLockOptimistic,
adCmdTable
for i in 1:10000
' calculate some number
' export i'th number to access
rs.fields(i) = worksheets.range(...
next i
I am hoping that it might be possible to do all the calculation, then
transfer the whole lot in one go to access - I have a feeling that
this may speed it up a bit. Any views on this?
My main question is, is it possible to transfer a range of cells from
excel to access via vba?
Many thanks,
Chris
|