Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Transpose from within VBA with 256 array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Transpose from within VBA with 256 array
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. Renate wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Transpose from within VBA with 256 array
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 "Dave Peterson" wrote: 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. Renate wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Transpose from within VBA with 256 array
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! "Renate" wrote: 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 "Dave Peterson" wrote: 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. Renate wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Transpose from within VBA with 256 array
Or upgrade to xl2007--it has 16k columns and 1m rows.
(or redesign (=transpose) your spreadsheet????? Renate wrote: 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! "Renate" wrote: 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 "Dave Peterson" wrote: 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. Renate wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose array sum | Excel Worksheet Functions | |||
transpose - array problem | Excel Discussion (Misc queries) | |||
Transpose Array | Excel Discussion (Misc queries) | |||
Conditional transpose to Array | Excel Discussion (Misc queries) | |||
Out of Memory: Array Transpose | Excel Programming |