Using Transpose from within VBA with >256 array

[Guest]

Hi all,

I'm trying to automate Excel from within Word. In my code I have a two
dimensional array with over 300 elements. The Transpose command doesn't seem
to work in this situation.
It works perfectly for arrays with up to 256 elements but I can't get it to
work for my code.

I made an example to show where I am talking about, this code doesn't put
anything in the Excel sheet.

-----------------------------------------------------------------------------------
Dim arrData() As String
Dim i As Long
Dim j As Long
Dim oXlApp As Excel.Application
Dim oXlBoek As Excel.Workbook

ReDim arrData(1, 257)

For i = 1 To 257

j = i - 1
arrData(0, j) = j
arrData(1, j) = j

Next i

On Error Resume Next
Set oXlApp = GetObject(Class:="Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set oXlApp = CreateObject(Class:="Excel.Application")
End If

oXlApp.Visible = True
Set oXlBoek = oXlApp.Workbooks.Add
arrData = oXlApp.Application.Transpose(arrData)
j = UBound(arrData, 2)
oXlBoek.Sheets(1).Cells(1, 1).Resize(2, j).Value = arrData

Set oXlBoek = Nothing
Set oXlApp = Nothing

-----------------------------------------------------------------------------------

If I redimension the array to 256 elements, this code works fine, but I need
it to work for a larger array. Can anyone please give me a suggestion how I
can solve this?

TIA,
Renate

 

[Dave Peterson]

I don't think it's a problem with application.transpose.

This worked fine for me (from within excel):

Dim arrData As Variant
Dim i As Long
Dim j As Long
Dim oXlBoek As Workbook

ReDim arrData(0 To 1, 0 To 256)

For i = 1 To 257
j = i - 1
arrData(0, j) = j
arrData(1, j) = j
Next i

Set oXlBoek = Workbooks.Add
arrData = Application.Transpose(arrData)
j = UBound(arrData, 2)
oXlBoek.Sheets(1).Cells(1, 1).Resize(2, j).Value = arrData

=======
But it didn't put the values in A1:IV2.

Was that line just for testing in the sample code--'cause you didn't need to
transpose the data.

 

[Guest]

Hi Dave,

Thanks for responding. You were right about the Transpose. I didn't need it,
I thought I needed it, but your code proves otherwise ;-)
However if I run your code it'll show values up to 255 and not over 256.

If I change
ReDim arrData(0 To 1, 0 To 256)
to
ReDim arrData(0 To 1, 0 To 260)

and
For i = 1 To 257
to
For i = 1 To 261

I'll get an error 1004 on the last line of the code. It seems I still can
only put 256 elements in the worksheet or am I missing something?

Regards,
Renate

 

[Guest]

Bummer!

Stupid me ... caused by a lack of sleep perhaps ;-)
No limits to transpose or resize but (of course) the limit of the maximum
number of columns in Excel .. back to textfiles for my goal I suppose ... )-:

Thanks for helping out!

 

[Dave Peterson]

Or upgrade to xl2007--it has 16k columns and 1m rows.

(or redesign (=transpose) your spreadsheet?????