Control Calc From Microsoft Office

It turns out that you can control OOo from within the Microsoft Office family of products. The trick is to create a service manager, which starts OOo if it is not currently running. Accessing OOo documents from Microsoft Office is similar to accessing OOo documents using other non-StarBasic languages. OOo Basic provides nice shortcuts that are not available from Microsoft Office. For example, in OOo Basic, when I want the third sheet, I simply use oDoc.Sheets(2); in Microsoft Office, however, you cannot access the Sheets property as an array. I wrote and ran the macro in Listing 47 from Microsoft Visual Basic from Microsoft Excel.

Listing 47: ControlOOo() demonstrates how to control OOo from Excel.

 Sub ControlOOo()   Rem The service manager is always the first thing to create.   Rem If OOo is not running, it is started.   Set oManager = CreateObject("com.sun.star.ServiceManager")   Rem Create the desktop.   Set oDesktop = oManager.createInstance("com.sun.star.frame.Desktop")   Rem Open a new empty Calc document.   Dim args()   Dim s As String   Set s = "private:factory/scalc"   Set oDoc = oDesktop.loadComponentFromURL(s, "_blank", 0, args())   Dim oSheet As Object   Dim oSheets As Object   Dim oCell As Object   Set oSheets = oDoc.sheets.CreateEnumeration   Set oSheet = oSheets.nextElement   Set oCell = oSheet.getCellByPosition(0, 0)   oCell.setFormula ("Hello From Excel") 'Cell A1   oCell.CellBackColor = RGB(127, 127, 127) End Sub