Assigning array to range inside Excel Object

[Bobsa]

I'm having problems assigning an array back to a worksheet created
with the CreateObject method.

Once the array has been processed and is in memory I can use this line

Range(Cells(1, 1), Cells(NewDataRowNumber + 1, ExpandedLastColumn)) =
NewPositionsArray

to assign it back fine to the instance of excel I'm using to process
and control everything from.

but when I use it with "With xlapp" .....in the form....

..Range(Cells(1, 1), Cells(NewDataRowNumber + 1, ExpandedLastColumn)) =
NewPositionsArray

I get an "object application defined error"

If I manually type the address for test purposes this works

..Range("a1:AO166") = NewPositionsArray

But I cannot understand why the ... .Range(Cells(1, 1),..... doesn't
work

Below is a snippet of code with both bits in the right place...
obviously only one is used at runtime.....

any help appreciated.

Bobsa







==================================


Dim xlapp As Object
Set xlapp = CreateObject("Excel.Application")

With xlapp
.Visible = True
.Workbooks.Add
End With

'between these points there is a lot of code which creates an array
called
'NewPositionsArray


With xlapp
.Workbooks(1).Activate
.Selection.Delete 'clear the area to receive the array
.Range("a1").Select 'just used to test if this is active

.Range("a1:AO166") = NewPositionsArray 'this works inside the
object

.Range(Cells(1, 1), Cells(NewDataRowNumber + 1,
ExpandedLastColumn)) = NewPositionsArray
'I would like to use this style

End With

 

[Tom Ogilvy]

It is unclear, if you are using Excel and working in Excel, that you would
start a new instance of excel to work with. Excel can open multiple
workbooks in the same instance.

However, your problem is the unqualified cells refers to the activesheet in
the activeworkbook in the instance of excel containing the code.

.Range(.Cells(1, 1), .Cells(NewDataRowNumber + 1, _
ExpandedLastColumn)) = NewPositionsArray

should clear up your problem. then range and cells will refer to the same
sheet.

 

[Jon Peltier]

Bobsa -

You should also reference the Cells part:

With xlapp.wkbk.wksht
.Range(.Cells(1,1), .Cells(New + 1, Expanded)) = NewArray
End With

- Jon

 

[Tom Ogilvy]

this is an odd construct:

With xlapp.wkbk.wksht

a wksht with no parent?
a wkbk with no application?

 

[Jon Peltier]

Too busy copying and pasting, and not thinking!

- Jon