OOo/LibO Calc UDF

[AIstarter]

Hi,

 

I'm trying to set autofilter for a sheet - is this by any way possible with this udf?

Thanks!

[mLipok]

You say 'set filter'.

Do you mean create new one or use already existing.

[AIstarter]

The AutoFilter function. But I figured it out:

Local $oSheet = __OOoCalc_GetSheet($oWorkbook, -1)
$oRange = $oSheet.getCellRangeByPosition(0, 0, $Columns, UBound($aArray)).getRangeAddress()
$oWorkbook.DatabaseRanges.addNewByName("Filters", $oRange)
$oWorkbook.DatabaseRanges.getByName("Filters").AutoFilter = True

This enables the AutoFilter for the selected Range.

[lastmember]

Hi !
Is possible to hide a Column/Row for printing?

[aio]

On 4/26/2017 at 11:51 PM, keafter said:

......................

Side note,

It would appear that there is some code out of place. Either that or I'm not understanding the _OOoCalc_BookAttach($sFileName) function properly.

In the  _OOoCalc_BookAttach function around line 230ish I commented out

If Not FileExists($sFileName) Then Return SetError($_OOoCalcStatus_NoMatch, 1, 0)

$sFileName = "2017 Notary.ods" which will always fail in FileExists() because it's not the entire file path. However If I give it the full file path it will fail a few lines further down at:

If StringInStr($sWinTitle, $sFileName) <> 1 Then Return SetError($_OOoCalcStatus_NoMatch, 0, 0)

Because $sWinTitle = "2017 Notary.ods - Open Office Calc" and the entire filepath is not contained within that. So it seems like with BookAttach the file is already open, it is looking for the window title and therefore never the path. That's my thought process anyway. I could very easily be looking at it the wrong way though, lemme know. Back to testing the sort function.

Hi.

Sorry for going back so much with the quoted post, but I was looking for some inspiration to make _OOoCalc_BookAttach() function working in my code.

I thought I was coding something wrong, but I believe that @keafter was right when indicating the problem in the FileExists verification.

If Not FileExists($sFileName) Then Return SetError($_OOoCalcStatus_NoMatch, 1, 0)

I'm using UDF OOoCalc.au3 last modification dated 2016/11/16.

For my purpose the FileExists() verification is not needed, so my personal workaround is just skip the verification and everything works perfectly.

[GaRydelaMer]

Hi.

I've made a modification to _OOoCalc_BookAttach()  in case you have 2 or more document open with same name ( in different directories ).

I use the URL to determine if the document exist.

Func _OOoCalc_BookAttach($sFileName)
    Local $oOOoCalc_COM_ErrorHandler = ObjEvent("AutoIt.Error", __OOoCalc_ComErrorHandler_InternalFunction)
    #forceref $oOOoCalc_COM_ErrorHandler
    If Not IsString($sFileName) Then Return SetError($_OOoCalcStatus_InvalidDataType, 1, 0)
    If Not FileExists($sFileName) Then Return SetError($_OOoCalcStatus_NoMatch, 1, 0)
    Local $oSM = ObjCreate("com.sun.star.ServiceManager")
    If Not IsObj($oSM) Then Return SetError($_OOoCalcStatus_GeneralError, 0, 0)
    Local $oDesktop = $oSM.createInstance("com.sun.star.frame.Desktop")
    If Not IsObj($oDesktop) Then Return SetError($_OOoCalcStatus_GeneralError, 0, 0)
    
    Local $bDocExist = False, $oReturn
    Local $oComponents = $oDesktop.getComponents().createEnumeration()
    Local $sFileURL = __OOoCalc_FileToURL($sFileName)
    While $oComponents.hasMoreElements()
        $oReturn = $oComponents.nextElement()
        If $oReturn.getURL() = $sFileURL Then
            $bDocExist = True
            ExitLoop
        EndIf
    WEnd
    If Not $bDocExist Then Return SetError($_OOoCalcStatus_NoMatch, 0, 0)
    Return SetError($_OOoCalcStatus_Success, 0, $oReturn)
EndFunc   ;==>_OOoCalc_BookAttach

Func _OOoCalc_BookGetHwnd(ByRef $oObj)
    Local $oOOoCalc_COM_ErrorHandler = ObjEvent("AutoIt.Error", __OOoCalc_ComErrorHandler_InternalFunction)
    #forceref $oOOoCalc_COM_ErrorHandler
    If Not IsObj($oObj) Then Return SetError($_OOoCalcStatus_InvalidDataType, 1, 0)
    Local $array[0]
    Local $oWindow = $oObj.CurrentController.Frame.getContainerWindow()
    If Not IsObj($oWindow) Then Return SetError($_OOoCalcStatus_GeneralError, 0, 0)
    Local $handle = HWnd($oWindow.getWindowHandle($array, 1))
    Return SetError($_OOoCalcStatus_Success, 0, $handle)
EndFunc

 

[PnD]

Dear all,

I am using the latest "OOoCalc.au3" and this works beautifully when saving the files to xls (Both OpenOffice and LibreOffice)

_OOoCalc_BookSaveAs($oCalc, "C:\test.xls",'MS Excel 97',False)

Lately, LibreOffice 7.1.3.2  comes with the option to save files to xlsx which OpenOffice do not offer so I just uninstall OpenOffice and use LibreOffice instead.

The problem is that if I do the following modifications to the codes below, nothing happens and the test.xlsx is not created. Do anyone have any idea that I did wrong?

_OOoCalc_BookSaveAs($oCalc, "C:\test.xlsx",'Excel 2007-365',False)

or 

_OOoCalc_BookSaveAs($oCalc, "C:\test.xlsx",'MS Excel 2007',False)

Thank you all!

[PnD]

16 minutes ago, PnD said:

Dear all,

I am using the latest "OOoCalc.au3" and this works beautifully when saving the files to xls (Both OpenOffice and LibreOffice)

_OOoCalc_BookSaveAs($oCalc, "C:\test.xls",'MS Excel 97',False)

Lately, LibreOffice 7.1.3.2  comes with the option to save files to xlsx which OpenOffice do not offer so I just uninstall OpenOffice and use LibreOffice instead.

The problem is that if I do the following modifications to the codes below, nothing happens and the test.xlsx is not created. Do anyone have any idea that I did wrong?

_OOoCalc_BookSaveAs($oCalc, "C:\test.xlsx",'Excel 2007-365',False)

or 

_OOoCalc_BookSaveAs($oCalc, "C:\test.xlsx",'MS Excel 2007',False)

Thank you all!

Never mind all. I just figured it out and this should work excellently

_OOoCalc_BookSaveAs($oCalc, "C:\test.xlsx",'Calc MS Excel 2007 XML',False)

I hope this will help anyone who needs it.

[NassauSky]

@GMK

Excellent work.

There is a bug in setFormula when you're writing a HYPERLINK. Possibly other formulas but I was attempting _OOoCalc_WriteFormula and realized it seemed to be cleaning the variable before setting it.  For example:

 

Local $sFormula = '=HYPERLINK("https://www.google.com","My Link")'
_OOoCalc_WriteFormula($oCalc, $sFormula, "A1")

would look fine in the Calc input line but show an Err:508 in the cell. I then realized if I go into Calc and add a space to the end of the input line the error would disappear and the link would work.

Until the bug is fixed, here is the trick to put a HYPERLINK in your calc spreadsheet:

Local $sFormula = '=HYPELINK("https://www.google.com","My Link")'
_OOoCalc_WriteFormula($oCalc, $sFormula, "A1")
_OOoCalc_ReplaceInRange($oCalc, "HYPELINK", "HYPERLINK")

It's all about the 'hype' 

OK you got me it's not about the hype but it turns out after you write your formula into the cell, the replace function seems to undo whatever damage it does to the formula.

 

Edited July 30, 2021 by NassauSky

[GMK]

@NassauSky, thanks for the bug report!  I'll have to look into it.

Edit, does this work any better?

Local $sFormula = '=HYPERLINK("https://www.google.com";"My Link")'
_OOoCalc_WriteFormula($oCalc, $sFormula, "A1")

https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_HYPERLINK_function

Edited August 2, 2021 by GMK
Possible solution added

[GMK]

Also: did you see _OOoCalc_HyperlinkInsert?

[lapoelkan12]

hi  , if i write  a  calc file  and  after i reopen  how can  restart at point that i left ?  thanks

[mLipok]

39 minutes ago, lapoelkan12 said:

hi  , if i write  a  calc file  and  after i reopen  how can  restart at point that i left ?  thanks

From your not entirely clear statement, I conclude that you must to store position, and then restore position to the same cell.

 

[lapoelkan12]

sorry if is not clear,  suppose you write one line   in calc , after  you close  next day restart script  and  you want  restart to write  at  second  line  , how  is possible  do it ?

 

[water]

Something like this? You "simply" need to translate it to autoIt  

https://ask.libreoffice.org/t/basic-calc-how-to-get-address-of-last-cell-used-with-content/46656

[lapoelkan12]

sorry again but  not  save me  a  file  when i call

_OOoCalc_BookSaveAs($oCalc, @DesktopDir & "\SignalCrypto\CryptoS.ods")

is  possible  see what type of  error return __

 

 

[water]

Don't know the oO UDF but usually functions return a value and/or set @error and @extended.
Please check this values.

 

[NassauSky]

Hi all,

How can I open OpenOffice Calc hidden so I can then reposition the window with WinMove and then show the OpenOffice calc window? 

The following doesn't work:

#include ".\Required-AU\OOoCalc.au3"    ; For Open Office / Libre Office Spreadsheet
#include <MsgBoxConstants.au3>

Global $oCalc
Local $fExcelTemplate = @ScriptDir & "\Required\PO.xls"

MsgBox($MB_OK, "Open Office", "Starting OO Hidden")
Local $oCalc = _OOoCalc_BookOpen($fExcelTemplate, True)
    If @error Then Exit MsgBox($MB_TOPMOST, "Notice", "Unable to open Book: " & $fExcelTemplate)

Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase
SplashTextOn("Wait","Waiting for OO To Start")
    WinWait("Office Calc")
    WinActivate("Office Calc")
SplashOff()

SplashTextOn("Wait","Moving Hidden Window")
    WinMove("Office Calc", "", 1000, 0, 800, 800) ;x,y,w,h
SplashOff()

SplashTextOn("Wait","In 3 secs will show OO window")
    Sleep(3000)
SplashOff()
WinSetState("Office Calc", "", @SW_SHOW) ;Now shows Office Calc but it's all grey inside with no cells or icons

MsgBox($MB_OK, "Open Office", "Click OK to close the sheet")
_OOoCalc_BookClose($oCalc)

 

 

Edited July 14, 2023 by NassauSky
Clarifying Code and added #include

[GMK]

On 7/14/2023 at 12:28 PM, NassauSky said:

How can I open OpenOffice Calc hidden so I can then reposition the window with WinMove and then show the OpenOffice calc window?

Can't test right now, but try this:

#include ".\Required-AU\OOoCalc.au3"    ; For Open Office / Libre Office Spreadsheet
#include <MsgBoxConstants.au3>

Global $fExcelTemplate = @ScriptDir & "\Required\PO.xls"

MsgBox($MB_OK, "Open Office", "Starting OO Hidden")
Global $oCalc = _OOoCalc_BookOpen($fExcelTemplate, True)
If @error Then Exit MsgBox($MB_TOPMOST, "Notice", "Unable to open Book: " & $fExcelTemplate)
Global $oCalcWin = $oCalc.CurrentController.Frame.ContainerWindow

Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase
SplashTextOn("Wait","Waiting for OO To Start")
WinWait("Office Calc")
WinActivate("Office Calc")
SplashOff()

SplashTextOn("Wait","Moving Hidden Window")
$oCalcWin.setPosSize(1000, 0, 800, 800, 15) ;x,y,w,h,flag=15
SplashOff()

SplashTextOn("Wait","In 3 secs will show OO window")
Sleep(3000)
SplashOff()
$oCalcWin.Visible = True

MsgBox($MB_OK, "Open Office", "Click OK to close the sheet")
_OOoCalc_BookClose($oCalc)

Edited August 10, 2023 by GMK
ContainerWindow object

[NassauSky]

Thanks @GMK that did it!  I also realized it didn't work at first because I had the workbook saved as read only since it was a template.