Handling a Sheet Object in Richtext

[stanl]

I have been playing with the Richtext control, and assuming one is instantiated as oRTF, I can insert an Excel Spreadsheet with

oXL = oRTF.OLEObjects.Add(, "Test", , "Excel.Sheet")

and after this an instance of Excel.exe is in the task manager. My question(s) are how to address the oXL (object). I can close it with

If oRTF.OLEObjects.Count>0 Then oRTF.OLEObjects.Remove(0)

but code like oXL.Save, oXL.Quit or .copyfromrecordset produce errors. I have never dealt with the sheet object as a separate entity and my difficulty is perhaps separating it from the Excel hierarchy.

Hope this makes sense.

Stan

[XLGibbs]

It remains in the hierarchy of excel no matter what..

What you want to do is also object references to the sheet itself

Set ws1 = oXL.Sheets(1)

then reference that object in your code to manipulate the data on the sheet.

While testing all of this, you should also make

oXL.visible = true so you can see the file in windows. That way whenthe code crashes you can manually close the application (be sure to quit the app as well in the code!)

Also, because you are setting oXL as a workbook.add, this will cause issues..since any reference thereafter to oXL would potentially add a workbook instead of referencing the active one..

Dim objExcelApp As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet

Set objExcelApp = New Excel.Application
'sets object as new instance of excel

Set objExcelApp.Workbooks.Open (path and filename go here)

Set objWorksheet = objWorkbook.ActiveSheet

[stanl]

I think we have a disconnect (perhaps due to my choice of object reference). I am not creating a workbook. I am embedding a sheet object into a RichText window. Adding code like

Set ws1 = oXL.Sheets(1)

just errors out as "Unknown Name".

Stan

[stanl]

...and as a P.S.... I tried oXL.Parent, or even enumerating oXL.Properties but keep erroring with 'Unknown Name'. There is not a lot of information on the .OLEObjects property of a control that supports it, like RichText, so perhaps I am just Stan