Manipulating Arrays in VBA

[SamChevre]

OK, I'm working on the Access-Excel problem I mentioned in this thread.

I need to transpose a large array (about 15000x2), so it will paste into Excel correctly. I'm using this code to get the array:

Set qdf = CurrentDb().QueryDefs("Query1")
Set recSummary = qdf.OpenRecordset
recSummary.MoveLast
recSummary.MoveFirst
varArray = recSummary.GetRows(recSummary.RecordCount)

In Access, it is 10 columns, 1000 rows. I want it to paste into Excel the same way. If I do

objExcel.Range("A1").Resize(intRows + 1, intFields + 1).Value = varArray

it pastes in as 10 rows, 1000 columns.
The array is too big to transpose using the Transpose function.

Any suggestions?

[krank]

why not use rangeVar.CopyFromRecordset recordsetName

[MNBridge]

I know ZERO about VBA for Access.

But to transpose in Excel you could

Dim A ( 10000,10)
Dim B ( 10, 10000)

For X = 1 to 10000
For Y = 1 to 10
Set B(Y, X) = A.cells (X,Y)
Next Y
Next X

[SamChevre]

Originally Posted by MNBridge

I know ZERO about VBA for Access.

But to transpose in Excel you could

Dim A ( 10000,10)
Dim B ( 10, 10000)

For X = 1 to 10000
For Y = 1 to 10
Set B(Y, X) = A.cells (X,Y)
Next Y
Next X

That will work (that's basically the method I'm using now). The problem is that when the array is too large to fit in RAM (mine is), it's insanely slow, since it has to call data off the disk for every loop.

[SamChevre]

Originally Posted by krank

why not use rangeVar.CopyFromRecordset recordsetName

I get the error "Class does not support Automation". Any clue what's wrong?

[krank]

Originally Posted by SamChevre

I get the error "Class does not support Automation". Any clue what's wrong?

so your line of code looked like:

objExcel.Range("A1").CopyFromRecordset recSummary

this caused a problem?

[SamChevre]

Originally Posted by krank

so your line of code looked like:

objExcel.Range("A1").CopyFromRecordset recSummary

this caused a problem?

Yes, the relevant code snippets look like this:

Dim objExcel As New Excel.Application
Dim qdf As QueryDef
Dim recSummary As Recordset

objExcel.Workbooks.Open "C:\SamChevre.xls"
Set qdf = CurrentDb().QueryDefs("Query1")
Set recSummary = qdf.OpenRecordset
objExcel.Workbooks("SamChevre.xls").Sheets("Data").Activate
objExcel.Sheets("Data").Range("A1").CopyFromRecordset (recSummary)

[Old Actuary]

Theres' a 256 column limit in Excel. Therefore, you can't squeeze 1000 columns into one sheet, no matter what macro you use.

[2.718281828]

Yes, you cannot go beyond 256 columns.... But I'm sure our poster knows this.
Why don't you find out what the max number of args are for the Transpose function is.
Break your array into piece so that the transpose function takes it. Then make a program that recombines your arrays at the end. It will probably be the fasted way. Also, the loop in the previous post would be slow since it access the worksheet 100,000 times. You want to do everything in VBA first then put it in the worksheet. The less you access the worksheet during a procedure the better.

[Peter Lemonjello]

Doesn't the new version of Excel support more than 256 columns? Perhaps the poster has the latest and greatest.

Me? I'm stuck in 2000.