Output Multi-dimensional Array to Excel Sheet

[AlexIT]

Here is a snippet of the code...pls note this must be in Excel 2000 VB. I know I am making a simple mistake:

Dim vMain as Variant
Dim vComp as Variant
Dim xcel as Object
Dim sht as Object
Dim i as Long
Dim j as Long

Set otherapp = CreateObject("Other.Application")
vMain = File.GetData
For j = 0 to UBound(vMain)
vComp = vMain(j).GetData

Set xcel = CreateObject("Excel.Application")
xcel.Visible = True
xcel.Workbooks.Add
Set sht = xcel.Worksheets.Add

For i = 0 to UBound(vComp)
sht.Cells(j + 1, i + 1).Value = vComp(i).Name2
Next i
vComp = Nothing
Next j

Ok, that works but opens like 59 Excel windows, each with its own workbook and only puts part of the array on a single sheet in each.

So I make the following change:

Set otherapp = CreateObject("Other.Application")
vMain = File.Get.Data

Set xcel = CreateObject("Excel.Application")
xcel.Visible = True
xcel.Workbooks.Add
xcel.Worksheets.Add

For j = 0 to UBound(vMain)
vComp = vMain(j).GetData

For i = 0 to UBound(vComp)
With xcel.Workbooks(1).WorkSheets(1)
.Cells(j + 1, i + 1).Value = vComp(i).Name
End With
Next i
vComp = Nothing
Next j

No dice...now I try:

Set otherapp = CreateObject("Other.Application")
vMain = File.Get.Data

Set xcel = CreateObject("Excel.Application")
xcel.Visible = True
xcel.Workbooks.Add
xcel.Worksheets.Add

For j = 0 to UBound(vMain)
vComp = vMain(j).GetData
xcel.Workbooks(1).Activate
xcel.Worksheets(1).Activate
For i = 0 to UBound(vComp)
xcel.Sheets(1).Cells(j + 1, i + 1).Value = vComp(i).Name
Next i
vComp = Nothing
Next j

One gives me "object not supported" and the second runs through the first "j" then gives a "subscript ot of range"...so how can I flip application focus between Excel and the other program without creating a new Excel instance each time?

 

[xlbo]

This is your error:

xcel.Workbooks.Add
Set sht = xcel.Worksheets[b].Add[/b]

Change to this:

xcel.Workbooks.Add
Set sht = xcel.activeWorksheet

and all should be fine

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AlexIT]

Geoff, Thanks for your reply. Changing the first code portion to:

Set xcel = CreateObject("Excel.Application")
xcel.Visible = True
xcel.Workbooks.Add
Set sht = xcel.activeWorksheet

Results in:
"Object does not support this property or method"
error on the Set sht line...

 

[VBAjedi]

This should work:

Dim wkbk As Workbook, sht As Worksheet
Set xcel = CreateObject("Excel.Application")
xcel.Visible = True
Set wkbk = xcel.Workbooks.Add
Set sht = wkbk.ActiveSheet

VBAjedi

 

[xlbo]

D'oh - forgot the workbook bit
Ta Luke

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question