Array dump to Excel fails

[MePenguin]

Hi,

I'm trying to dump an array from Access to Excel using code.
The following (abbreviated), looping through each element, works fine, but is REALLY slow:

'ArrayMCROver() is passed to the sub

Set ObjExcel = GetObject(Filename)
Set ExcelApp = ObjExcel.Parent 
With ExcelApp
        For r = LBound(ArrayMCROver, 2) To UBound(ArrayMCROver, 2)
            For c = LBound(ArrayMCROver, 1) To UBound(ArrayMCROver, 1)
                .Cells(r + 1, c + 1).Value = ArrayMCROver(c, r)
            Next c
        Next r
End with

The alternatives below both only deliver the first 36 columns of the array, the rest of the cells within the 36rows*60columns being filled with "#N/A".

'Alternative 1
ExcelApp.Range(Cells(1, 1), ExcelApp.Cells(UBound(ArrayMCROver, 2) + 1, 
_UBound(ArrayMCROver, 1) + 1)).Value = ArrayMCROver

'Alternative 2
ExcelApp.Cells(1, 1).Resize(UBound(ArrayMCROver, 2) + 1,
 _UBound(ArrayMCROver, 1) + 1).Value = ArrayMCROver

What's going on??? I can't find anything at MS about a limit to the size of a transfered array...

Would it help to try to refer to the range using the "A1:BH36" syntax?

Any ideas?

Please?

 

[MePenguin]

I just tried exporting the array transformed, ie. switching the rows and columns in the uper limit

ExcelApp.Cells(1, 1).Resize(UBound(ArrayMCROver, 1) + 1,
 _UBound(ArrayMCROver, 2) + 1).Value = ArrayMCROver

And it worked!

Is there a column limit?? Help please?

 

[MePenguin]

Feel like I'm talking to myself, but for anyone interested out there I've solved it... feel a bit silly really:

ExcelApp.Range(Cells(1, 1), ExcelApp.Cells(UBound(ArrayMCROver, 2) + 1, 
_UBound(ArrayMCROver, 1) + 1)).Value = ExcelApp.Transpose(ArrayMCROver)

i.e. transpose the array.