transfer range of cells from excel to Acess using vba

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

christian_spaceman - 10 Dec 2007 15:00 GMT

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

Reply to this Message

Smallweed - 10 Dec 2007 15:22 GMT

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.

> Hi there,
>
[quoted text clipped - 27 lines]
>
> Chris

Reply to this Message

christian_spaceman - 10 Dec 2007 16:10 GMT

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 <Smallw...@discussions.microsoft.com>
wrote:

> You can't do it from the Access side can you? Then you can use:
>
[quoted text clipped - 42 lines]
>
> - Show quoted text -

Reply to this Message

Jim Thomlinson - 10 Dec 2007 16:41 GMT

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.

Signature

HTH...

Jim Thomlinson

> Thanks for replying
>
[quoted text clipped - 60 lines]
> >
> > - Show quoted text -

Reply to this Message

christian_spaceman - 10 Dec 2007 22:16 GMT

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.
[quoted text clipped - 71 lines]
>
> > > - Show quoted text -

Reply to this Message