OLE Open Office Calc

[Pete119j]

Hi,

I currently have a script that outputs data to cells in an Excel spreadsheet. I found the info for the script on the alpha message board written by John Magno & Stan Mathews and wondered if it is possible to adapt it to work with Open Office Calc.

Any help would be very much appreciated.

Pete

script below

Dim xlApp as p
dim myCell as c
xlApp = ole.create("Excel.Application")
xlApp.Workbooks.create("c:\test.xls")
xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
xlApp.Workbooks.Add() 'adds a workbook object
xlApp.ActiveSheet.Name = "Line Items"
myCell = "A1"
xlApp.Sheets(1).Range(myCell).Select 'selects a cell
xlApp.ActiveCell.FormulaR1C1 = "This" 'adds text to that cell
myCell = "b1"
xlApp.Sheets(1).Range(myCell).Select 'selects a cell
xlApp.ActiveCell.FormulaR1C1 = "Is" 'adds text to that cell
myCell = "c1"
xlApp.Sheets(1).Range(myCell).Select 'selects a cell
xlApp.ActiveCell.FormulaR1C1 = "A" 'adds text to that cell
myCell = "d1"
xlApp.Sheets(1).Range(myCell).Select 'selects a cell
xlApp.ActiveCell.FormulaR1C1 = "Test" 'adds text to that cell

[Stan Mathews]

Re: OLE Open Office Calc

The OpenOffice.org website indicates that OpenOffice supports ole. The line
xlApp = ole.create("Excel.Application") would become
xlApp = ole.create("OO.Application") where OO is the name of the Open Office Calc executable just as Excel is the name of the Microsoft executable (excel.exe).

You will need to discover if the rest of the example works as if or if you must use different terminology. Does Open Office support macros? Do macros written in Excel work in Open Office? If so you may not need to do much translation.

[Pete119j]

Re: OLE Open Office Calc

Hi, i've found some code which works in VB script. Not sure what I need to do in alpha5 though.

This code only works when openoffice calc is aready open

Set oServiceManager = CreateObject("com.sun.star.ServiceManager")
Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")
Set oComponents = oDesktop.getComponents()
Set oEnum = oComponents.createEnumeration
On Error Resume Next
Do While oEnum.hasMoreElements
Set ThisComponent = oEnum.nextElement
If ThisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Do
Loop
Set oDoc = ThisComponent
Set oSheet = oDoc.getSheets.getByIndex(0)
text = "This"
oSheet.getCellRangeByName("A1").setString(text)
text = "is"
oSheet.getCellRangeByName("B1").setString(text)
text = "a"
oSheet.getCellRangeByName("C1").setString(text)
text = "Test"
oSheet.getCellRangeByName("D1").setString(text)
msgbox "END"

I think i need this line in alpha5 to start with

xlApp = ole.create("soffice.starcalcdocument")

and have also found some info on other languages

http://www.oooforum.org/forum/viewtopic.phtml?t=9815
http://www.oooforum.org/forum/viewtopic.phtml?p=28389

Still have no idea how to make it work in alpha5 though and i'm a bit out my depth.

[ANGEL SANCHEZ]

Re: OLE Open Office Calc

try this

Dim oServiceManager as P
Dim oDesktop        as P
Dim oComponents     as P
Dim oEnum           as P
oServiceManager = ole.Create("com.sun.star.ServiceManager")
oDesktop        = oServiceManager.createInstance("com.sun.star.frame.Desktop")
oComponents     = oDesktop.getComponents()
oEnum           = oComponents.createEnumeration

WHILE oEnum.hasMoreElements
	ThisComponent = oEnum.nextElement
	IF ThisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument") THEN
		oDoc = ThisComponent
		oSheet = oDoc.getSheets.getByIndex(0)
		text = "this"
		oSheet.getCellRangeByName("A1").setString(text)
		text = "is"
		oSheet.getCellRangeByName("B1").setString(text)
		text = "a"
		oSheet.getCellRangeByName("C1").setString(text)
		text = "Test"
		oSheet.getCellRangeByName("D1").setString(text)
	END IF
END WHILE
msgbox("END")