Problem with Container Control

[BountyBob]

Hi people,

I've got a problem with the Container Control (actually, i sincerely doubt the problem lies with the control). What I'm trying to do is embed an Excel worksheet into a form. The data is going in OK, but only the first 7 columns and 10 rows are being shown.

What I want to do is show all rows and columns that contain data, add scrollbars and enable the menu's (so the user can save the spreadsheet).

My code is shown below: -

Dim rsHistory As New ADODB.Recordset
Dim oBook As Excel.Workbook
Dim oApp As Excel.Application
Dim oActiveSheet As Excel.Worksheet
Dim Row As Integer
Dim Col As Integer

oleExcel.CreateEmbed "", "excel.sheet"
Set oBook = oleExcel.object
Set oApp = oBook.Application
Set oActiveSheet = oApp.ActiveSheet

rsHistory.Open "SELECT * FROM MyReport", gcnTRS, adOpenStatic, adLockReadOnly

If Not rsHistory.EOF Then
Row = 1
Col = 1
Do
For Col = 1 To rsHistory.Fields.Count
oActiveSheet.Cells(Row, Col).Value = rsHistory.Fields(Col - 1).Value
Next
Row = Row + 1
rsHistory.MoveNext
Loop While Not rsHistory.EOF
End If

Any ideas how to solve this? Even better would be sources of information where I could learn more about OLE automation (at least with regard to the Container Control).

Cheers,

Rob

[brucevde]

By default the embedded object is not activated until you double click on the ole container (see the AutoActivate property).

Set the AutoActivate property to 0 - Manual and then call the DoVerb method

oleExcel.DoVerb vbOLEUIActivate

[BountyBob]

Cheers for the reply. I discovered AutoActivate and DoVerb though they didn't act in the way I thought they would.

What I ended up with appeared as though the Embedded Excel object was a new object, positioned on top of my OLE Container. If I resized my form the OLE Container would resize but the Excel object would not.

I think I might just abandon embedded objects and pump the data straight to the Excel application.

Cheers

Rob