Pass Array to Range Object

**Paluee** · 04-20-2010, 02:03 PM

Hi there,

Q1:
If one has data in a database, and gets the data via ADO, is there an easy way to transfer the data to a Range object. Is there some kind VBA function that can do this.

Q2:
Or does one have to pass an ADO recordset to a VBA arrray, and then pass the array to the range object.

If this is the case, is there a VBA function to easily copy data from an array to Range object, without having to do lots of looping?

Sincerely,

Paluee

**broro183** · 04-20-2010, 02:15 PM

hi,

I may have misunderstood your question/limitations, or be over-simplifying it, but...
Are you aware that in Excel you can use Data - External Data to import data from other sources (such as Access databases)?

Here is an example of writing from an array to a spreadsheet range:
http://www.excelforum.com/2289689-post134.html

hth
Rob

**romperstomper** · 04-20-2010, 05:14 PM

The Range object has a CopyFromRecordset method which I suspect you will find useful. You can also use GetRows to put the recordset into an array but you will need to transpose it before writing it to the worksheet.

**broro183** · 04-20-2010, 06:11 PM

hi Romper,

My new job (of 4 wks) involves a lot of Access work so I'm on a steep learning curve* - but I hope that I'll soon be making more knowledgeable responses in Access related threads

*my last use of Access was playing in Northwind (?) at Uni 10yrs ago!

Rob

**romperstomper** · 04-20-2010, 06:30 PM

Good luck with the A2007 UI - I hate it. Even more than the Excel one!!

**Paluee** · 04-21-2010, 12:14 AM

Hi and thanks for all the responses.

Most of you assume that I am using Access as the DB, but I am not.
I am using SQLite as the DB and have two wrappers. One uses ADO the other does not,
but uses Arrays to hold onto data.

And I agree with the person that said something like I hate A2007, Access2007 UI.
I am not fond of any of the 2007 office apps. That is why I will stick to Excel97-2003 for as long as possible.

Thanks for the Transposing trick that it seems is needed.

But does anyone have any sample code of Array to Range Object.

But on top of this,
I don't want the data to go to a sheet. Can one create a range Object in memory and hold the data there instead of it being placed on a sheet, or does using RangeObject automatically imply that you will place it somewhere on a sheet.
Just because you pass an address like A1:B4 to the range object, does that mean that it must be placed on the sheet?

I hope what I am saying makes sense.

Paluee

**romperstomper** · 04-21-2010, 03:01 AM

We didn't assume you were using Access...

My comment about transposing the array was specifically in reference to ADO's GetRows method.

And no, you cannot create a Range purely in memory (I don't really see why you would anyway) - you have to just use the array. If you don't want to put it on a worksheet, can I ask where Excel is involved at all?

**Paluee** · 04-21-2010, 01:21 PM

Hi romperstomper,

Thanks for your reply.

Let me put some more context.
I am trying to pass the data from the database to a chart objects chart series.
But in a previous post of mine(different thread), I got feedback that trying to pass an
array you will reach a limitation. So passing a range object does not impose that limitation.

But I don't want the data to appear on the excel sheet. I guess I could clear the area afterwards, but I am just trying to find the most efficient way of doing this.

Sincerely,

Paluee

**romperstomper** · 04-21-2010, 01:44 PM

You will have to put the data on a sheet (the sheet can be hidden if necessary) then, unless you want to use a form with the Office Web Components chart object, which doesn't have the same SERIES formula limitation.