Jump to content

Recommended Posts

Posted

You say 'set filter'.

Do you mean create new one or use already existing.

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

  Reveal hidden contents

Signature last update: 2023-04-24

Posted

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.

  • 9 months later...
  • 1 month later...
Posted
  On 4/26/2017 at 9: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.

Expand  

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.

  • 6 months later...
Posted

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

 

  • 1 year later...
Posted

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!

Posted
  On 5/8/2021 at 4:41 PM, 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!

Expand  

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.

  • 2 months later...
Posted (edited)

@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' :dance:

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 by NassauSky
  • 11 months later...
Posted
  On 7/18/2022 at 9:34 AM, lapoelkan12 said:

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

Expand  

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

 

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

  Reveal hidden contents

Signature last update: 2023-04-24

Posted

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 ?

 

Posted

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

My UDFs and Tutorials:

  Reveal hidden contents

 

Posted

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 __

 

 

Posted

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

 

My UDFs and Tutorials:

  Reveal hidden contents

 

  • 11 months later...
Posted (edited)

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 by NassauSky
Clarifying Code and added #include
  • 4 weeks later...
Posted (edited)
  On 7/14/2023 at 5: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?

Expand  

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 by GMK
ContainerWindow object

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...