OOo/LibO Calc UDF

[GMK]

I decided to enhance the functionality of the OOo COM UDF found >here and >here.  Thanks to Leagnus and Andy G for providing the initial functions and framework.

This UDF includes the following working functions:

_OOoCalc_BookNew
_OOoCalc_BookOpen
_OOoCalc_BookAttach
_OOoCalc_BookSave
_OOoCalc_BookSaveAs
_OOoCalc_BookClose
_OOoCalc_WriteCell
_OOoCalc_WriteFormula
_OOoCalc_WriteFromArray
_OOoCalc_HyperlinkInsert
_OOoCalc_RangeMoveOrCopy
_OOoCalc_RangeSort
_OOoCalc_RangeClearContents
_OOoCalc_CreateBorders
_OOoCalc_NumberFormat
_OOoCalc_ReadCell
_OOoCalc_ReadSheetToArray
_OOoCalc_RowDelete
_OOoCalc_ColumnDelete
_OOoCalc_RowInsert
_OOoCalc_ColumnInsert
_OOoCalc_SheetAddNew
_OOoCalc_SheetDelete
_OOoCalc_SheetNameGet
_OOoCalc_SheetNameSet
_OOoCalc_SheetList
_OOoCalc_SheetActivate
_OOoCalc_SheetSetVisibility
_OOoCalc_SheetMove
_OOoCalc_SheetPrint
_OOoCalc_HorizontalAlignSet
_OOoCalc_FontSetProperties
_OOoCalc_CellSetColors
_OOoCalc_RowSetColors
_OOoCalc_ColumnSetColors
_OOoCalc_RowSetProperties
_OOoCalc_ColumnSetProperties
_OOoCalc_FindInRange
_OOoCalc_ReplaceInRange

Please help me test this and let me know of any problems, questions, suggestions or comments you may have.

Edit (August 5, 2016): Latest files have been uploaded.  Script-breaking changes include renaming the functions to start with _OOoCalc_.

Edit (November 14, 2016): New version, including fixed sort function!

Edit (November 15, 2016): Lots of minor changes including tightening up the error checking.

Edit (November 16, 2016): Added ByRef to object parameters. Edited demo.

 

 

 

 

OOoCalcConstants.au3

 

OOoCalc.au3

OOoCalc_Demo.au3

Edited November 16, 2016 by GMK
File Update (2016/11/16)

[Mehomic]

GMK:

Thanks for posting this 'updated' version of the OOCalc UDF?

I can confirm that your demo file works well in the current stable Apache OpenOffice v 3.4.1 version.

It did not report any errors. I am sure there will be a use for this script.

Again, Many thanks for sharing.

Edited June 5, 2013 by Mehomic

[mLipok]

Very usefull UDF and example

Thanks for sharing

[DickWms]

I've tried to run the example but get the following error:

OOoCalc.au3 (3151) : ==> Variable must be of type "Object".:
Local $oSM = ObjCreate("com.sun.star.ServiceManager"), $oReturn = $oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
Local $oSM = ObjCreate("com.sun.star.ServiceManager"), $oReturn = $oSM^ ERROR
>Exit code: 1    Time: 2.335
 

Any help would be appreciated.

Dick Williams

[BrewManNH]

Do you have OpenOffice installed?

EdIt: Or LibreOffice?

Edited August 5, 2013 by BrewManNH

[DickWms]

Yes - It's Open Office 3.4.1 on a Windows 8, 64 bit, computer.

Dick Williams

[GMK]

Unfortunately, I'm unable to test on Windows 8.  Can anyone else with  Windows 8 run the example successfully?

[BrewManNH]

Just a suggestion, add some error checking to the UDF functions because currently there's practically none. That error wouldn't have crashed the script if there was a check to see if $oSM was an object prior to trying to use it as one.

@DickWms - Have you tried running the script as a 32 bit process instead of a 64 bit one?

[DickWms]

Thank you for the suggestion.  I have now tried the Run Script (x86) option but get the same error message.

Dick Willaims

[GMK]

I'm adding some additional error checking to the UDF.  How far into the demo do you get?

[BrewManNH]

Without OOo or Libre installed, it's from the first call to _OOoCalcBookNew, as soon as it hits that it jumps to the function __OOoCalcSetPropertyValue which causes the error to appear. I'm guessing, and only a guess, that DickWms is seeing the same thing.

[DickWms]

I just get through the line: MsgBox(64 + 262144, $sTitle, "Create a new Book")

The error seems to be called by the line: $oCalc = _OOoCalcBookNew() ;Create test file

Dick Williams

[GMK]

I added some error checking and uploaded the latest version.  (See )

[DickWms]

I've tried the latest version.  Here are the first few lines of the error messages I get.

Dick Williams

 

>"C:Program Files (x86)AutoIt3SciTE..autoit3.exe" /ErrorStdOut "C:Usersdickw_000Desktopnew autoitOOoCalc_Demo.au3"
--> OOoCalc.au3 V2.1.0 Error from function _OOoCalcBookNew ($_OOoCalcStatus_GeneralError - Unable to create service manager object)
--> OOoCalc.au3 V2.1.0 Error from function _OOoCalcSheetList ($_OOoCalcStatus_InvalidDataType - Invalid Calc object)
--> OOoCalc.au3 V2.1.0 Error from function _OOoCalcSheetNameSet ($_OOoCalcStatus_InvalidDataType - Invalid Calc object)
--> OOoCalc.au3 V2.1.0 Error from function _OOoCalcWriteCell ($_OOoCalcStatus_InvalidDataType - Invalid Calc object)
--> OOoCalc.au3 V2.1.0 Error from function _OOoCalcCellSetColors ($_OOoCalcStatus_InvalidDataType - Invalid Calc object)
--> OOoCalc.au3 V2.1.0 Error from function _OOoCalcWriteCell ($_OOoCalcStatus_InvalidDataType - Invalid Calc object)

[GMK]

Unfortunately, I don't have a Windows 8 machine with OpenOffice 3.4.1.  Have you tried re-installing (possibly after uninstalling) OpenOffice?  (EDIT: Make sure you do a full install, in case there are some components missing at the moment.)  There sounds like something different going on with Windows 8 that doesn't allow the UNO objects to be created.

If that doesn't work (and I know this might be asking a bit much), would it be possible to download and install LibreOffice 4.1 and test with that?

Edited August 8, 2013 by GMK

[GMK]

I just stumbled upon a possible cause of the problem.  It's possible that there could be a class permissions issue, in which case you'd have to go to the Component Services administrative tool.  I'm guessing you'll want to look for OpenOffice Service Manager, or something similar.

[DickWms]

Success -  I uninstalled Office 3.4.1 and installed Office 4.0.0.

OOoCalc_Demo.au3 sems to run just fine now.

Thank you for your help.

Dick Williams

[GMK]

Glad you got it to work!

[Wombat]

If I may make a suggestion... It would extremely helpful if you could add the $fVisible parameter into _OOoCalcBookOpen so that we can open the book hidden just as we are able to in the _ExcelBookOpen. This makes a world of difference if someone just wants to write data to a file without actually showing the file.

[BrewManNH]

I don't believe that option is possible with OOo because OOo's interface doesn't support it.