Moving data from a Cellset to a Worksheet

[leejayd]

Hi,

I'm querying an OLAP source using ADO from VBA with code similar to below :

 Set rs = New Cellset
        
 With rs
        .Open sQry, cnn
 End With

Usually I dump the Cellset into an array and then dump to the worksheet in one pass.

This doesn't work with a big query (1m rows+) due to memory problems.

I tried looping through the CellSet and writing each worksheet cell. This is impractical due the number of cells.

Does anyone if there is a better way to load a CellSet to a worksheet?

 

[Darren Bartrup]

Have you tried using two queries - add a where condition in your SQL to cut the data set into two.

Something like:
WHERE UniqueID<500000

 

[leejayd]

I copied out the MDX query and pasted it into a Power Pivot query - it worked ! 1.5 million rows returned in less than 2 minutes.

This was a very big query with many member predicates - I was surprised Power Pivot didn't crash.