Communication with OpenOffice Calc

This is the main technical forum for discussing problems that were encountered by users while developing PROMOTIC applications, and their possible solutions.

Communication with OpenOffice Calc

Postby minder » Tue 14. Jan 2020 15:19:44


I have the working project on Promotic 8.3.26 version. Now the customer wants to put some data from Promotic to OpenOffice .ods file. I tired to use script from the help with smole modifications:
Dim objServiceManager, objCoreReflection, objDesktop, objDocument, objText
Dim oCell, args(), arg(), s, sPath, bDone
Dim testas, tDate, sDate, q, Eil_Nr, temp

Set testas = pMe.Pm("../Data/#vars/testas")
Set temp = pMe.Items("#vars/var1")
sPath = Pm.EvalMacro("$")
s = Replace(sPath, "\", "/")

'The service manager is always the starting point
'If there is no office running then an office is started up
Set objServiceManager = CreateObject("")
testas.Value = 1

'Create the CoreReflection service that is later used to create structs
Set objCoreReflection = objServiceManager.createInstance("")
testas.Value = 2

'Create the Desktop
Set objDesktop = objServiceManager.createInstance("")
testas.Value = 3

'Esamos ataskaitos pavadinimas
tDate = Pm.Time
sDate = Pm.FormatDate(tDate, "%Y%m%d")
q = "Ataskaita_" + CStr(sDate) + ".ods"

'Open a new empty calc document
'Set objDocument = objDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, args)
Set objDocument = objDesktop.loadComponentFromURL("file:///" & s & q, "_blank", 0, args)
testas.Value = 4

'Create a text object
Set objText = objDocument.getSheets.GetByIndex(0)
testas.Value = 5

Set oCell = objText.GetCellByPosition(0, 0)
Eil_Nr = oCell.Value
'oCell.CellBackColor = 6710932
Set oCell = objText.GetCellByPosition(1, Eil_Nr)
oCell.Value = temp.Value
objText.GetCellByPosition(2, Eil_Nr).String = "C1"
objText.GetCellByPosition(3, Eil_Nr).String = "CC"
Set oCell = objText.GetCellByPosition(0, 0)
oCell.Value = Eil_Nr + 1

sPath = Pm.EvalMacro("$")
s = Replace(sPath, "\", "/")
bDone = objDocument.storeAsURL("file:///" & s & q, arg)


Set objDocument = Nothing
Set objDesktop = Nothing
Set objCoreReflection = Nothing
Set objServiceManager = Nothing
Script works, but every time it opens the .ods file on desktop, above the scada. At the script end the file closes.
Is it possible to read/ write data from .ods with out opening the .ods file on desktop?
Posts: 6
Joined: Tue 13. Feb 2018 20:35:53

Re: Communication with OpenOffice Calc

Postby admin » Mon 10. Feb 2020 9:41:14


please refer to the OO documentation:

focus on the "bHidden" parameter.

(But please understand that this has not been properly tested with the Calc program)
Posts: 64
Joined: Wed 12. Dec 2012 15:43:09

Return to I don't know how to...

Who is online

Users browsing this forum: No registered users and 1 guest