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

transfer range of cells from excel to Acess using vba
 

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

transfer range of cells from excel to Acess using vba
 

Thanks for replying

hmm - that first example is exactly what I was after, the ability to
transfer a whole range at once. I think in the second, I'd still have
to iterate through all the cells in the array and transfer them
individually wouldn't I?

I could do it from the access side, the only thing thats stopping me
is that the rest of the model is written and distributed in excel
(only really using access to store resutls because it has more rows).
Do you know of anyway to transfer a range from the excel side?

Cheers

Chris


On Dec 10, 3:22 pm, Smallweed
wrote:
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- Hide quoted text -

- Show quoted text -

transfer range of cells from excel to Acess using vba
 

You are going to use code similar to what you have. Your recordset object is
exactly what the name implies... An object designed to hold a set of records.
What you want to do is to write records into the recordset using the
rst.AddNew (list fields and values) method. When the recordset is complete
then you commit the recordset to the database with the rst.Update method of
the recordset object.
--
HTH...

Jim Thomlinson


"christian_spaceman" wrote:

Thanks for replying

hmm - that first example is exactly what I was after, the ability to
transfer a whole range at once. I think in the second, I'd still have
to iterate through all the cells in the array and transfer them
individually wouldn't I?

I could do it from the access side, the only thing thats stopping me
is that the rest of the model is written and distributed in excel
(only really using access to store resutls because it has more rows).
Do you know of anyway to transfer a range from the excel side?

Cheers

Chris


On Dec 10, 3:22 pm, Smallweed
wrote:
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- Hide quoted text -

- Show quoted text -

transfer range of cells from excel to Acess using vba
 

Thanks Jim,

It looks like I misunderstand the recordset - I can keep adding as
much *stuff* to it, then do a single update once it is full ( I am
currently updating once per loop iteration). No idea if this will
speed it up any but worth a try!

Thanks for your help,

Cheers,

Chris


On Dec 10, 4:41 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
You are going to use code similar to what you have. Your recordset object is
exactly what the name implies... An object designed to hold a set of records.
What you want to do is to write records into the recordset using the
rst.AddNew (list fields and values) method. When the recordset is complete
then you commit the recordset to the database with the rst.Update method of
the recordset object.
--
HTH...

Jim Thomlinson

"christian_spaceman" wrote:
Thanks for replying

hmm - that first example is exactly what I was after, the ability to
transfer a whole range at once. I think in the second, I'd still have
to iterate through all the cells in the array and transfer them
individually wouldn't I?

I could do it from the access side, the only thing thats stopping me
is that the rest of the model is written and distributed in excel
(only really using access to store resutls because it has more rows).
Do you know of anyway to transfer a range from the excel side?

Cheers

Chris

On Dec 10, 3:22 pm, Smallweed
wrote:
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- Hide quoted text -

- Show quoted text -