You can't do it from the Access side can you? Then you can use:

DoCmd.TransferSpreadsheet acImport, 8, "Access table", "path to Excel file",
True, "range name in workbook"

Alternatively, you might want to create a 2d variant array out of your Excel
data before populating:

Dim vntData As Variant
vntData = Worksheets("worksheet").Range("...")

You can then use vntData(x,y) to access each data element.

"christian_spaceman" wrote:

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