OOo/LibO Calc UDF

[GMK]

14 hours ago, mLipok said:

Constants:

Global Enum _ ; @Extended Status Types
        $0CALC_EXT_DEFAULT, _
        $0CALC_EXT_PARAM1, _
        $0CALC_EXT_PARAM2, _
        $0CALC_EXT_PARAM3, _
        $0CALC_EXT_PARAM4, _
        $0CALC_EXT_PARAM5, _
        $0CALC_EXT_COUNTER

Since some functions have more than five parameters, I'm choosing not to use that method.

[mLipok]

This is yours UDF and yours choice, I only give you some proposals.

.... of course you can add more $OCALC_EXT_PARAM# ....

 

 

Edited November 15, 2016 by mLipok

[GMK]

16 minutes ago, mLipok said:

This is yours UDF and yours choice, I only give you some proposals.

.... of course you can add more $OCALC_EXT_PARAM# ....

The largest is 14 parameters, which I think is a bit excessive for constants. ;-)

Thanks for your other suggestions, though!

Edited November 15, 2016 by GMK
Clarification

[GMK]

Newest version posted.  Hopefully the error checking is as tight as it needs to be now.

[mLipok]

When you are passing obj to function in most cases it should be passed as ByRef

_OOoCalc_WriteFromArray(ByRef $oObj

Of course in all cases when you want work on the same object and do not want to "replicate" this object.
In some cases this is also a "speed / memory" usage issue EDIT: I mean issue can happen when you not use ByRef.

 

Edited November 18, 2016 by mLipok

[mLipok]

This two functions do not work:

_OOoCalc_SheetDelete($oCalc, "Sheet2")
    _OOoCalc_SheetDelete($oCalc, "Sheet3")

As sheet name are named in local language for example:

Quote

Row|Col 0
[0]|3
[1]|Arkusz1
[2]|Arkusz2
[3]|Arkusz3
 

this is a result of this:

Local $aSheets = _OOoCalc_SheetList($oCalc)
    _ArrayDisplay($aSheets, "Sheet List")
    Global $sSheet1 = $aSheets[1]
    Global $sSheet2 = $aSheets[2]
    Global $sSheet3 = $aSheets[3]

How you can see I store this sheet names and use it later (look in my proposal for demo: OOoCalc_Demo.au3 )

EDIT:
In my proposed Demo This line should be commented:

;~  Local $sSheet1 = $aSheets[1]

Because is not used :

Quote

"C:\.......\OOoCalc_Demo.au3"(27,30) : warning: $sSheet1: declared, but not used in func.
    Local $sSheet1 = $aSheets[1]
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
 

 

EDIT 2:
Look also for other changes in Demo , like:
 

If $iAnswer2 = $IDYES Then

and 

_Example()
Func _Example()

and removed Global declaration

Edited November 16, 2016 by mLipok

[mLipok]

there is no need to add date in header:

; Modified ......: 2016/02/12 by GMK; 2016/11/15 by mLipok

You are doing this all in:

Quote

#cs #CHANGES ====================================================================================================================
 

btw. Whether this should be called "History of changes" ? 

[HeiWoMa]

I have modified the SaveAs function to SaveAs as TXT file with TAB separated. 
Maybe it is useful for somebody.

Func _OOoCalc_BookSaveAsTxtWithTab(ByRef $oObj, $sFilePath)
    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)
    If Not IsString($sFilePath) Then Return SetError($_OOoCalcStatus_InvalidDataType, 2, 0)
    ; If Not IsString($sFilterName) Then Return SetError($_OOoCalcStatus_InvalidDataType, 3, 0)
    ; If Not IsBool($bOverwrite) Then Return SetError($_OOoCalcStatus_InvalidDataType, 4, 0)
    $sFilePath = __OOoCalc_FileToURL($sFilePath)
    Local $avOptions[3] = [__OOoCalc_SetPropertyValue('FilterName', 'Text - txt - csv (StarCalc)'), __OOoCalc_SetPropertyValue('Overwrite', 'TRUE'), __OOoCalc_SetPropertyValue('FilterOptions', '9,34,0,1,1')] ; 9 = horizontal Tab
    $oObj.storeToURL($sFilePath, $avOptions)
    If @error Then Return SetError($_OOoCalcStatus_ComError, 0, 0)
    Return SetError($_OOoCalcStatus_Success, 0, 1)
EndFunc

The code for the FilterOptions can be found here: http://www.ascii-code.com/

Regards,
-HeiWoMa-

Edited January 10, 2017 by HeiWoMa

[marcgforce]

Hello everybody, i've made a script using this UDF and it's working perfectly, but when i tried to change the sheet to LANDSCAPE mode or format the page (1 page /1 page)  there was nothing to do it, so is it possible to add something like this at this great UDF, it would be nice.

 

Thanks.

[GMK]

Thanks for the feedback.  I'll have to see what I can find.

[AIstarter]

Would second the wish for Landscape for a book - I've switched from Excel to LO-Calc and miss this

Edit: One thing I noticed: I can't overwrite with the SaveAs Function (despite setting Overwrite to True)

Edited March 14, 2017 by AIstarter

[mmoalem]

Hi all - am a noobie so please bear with me  

at one point in my script I am reading a URL from a browser (copy to clipboard and using clipget into a local variable $NewURL) and want to input it into a cell (N1, N2 etc) - but it doesnt work - it works ok if $NewURL is a simple text string but not a URL - any idea if I need a different write command for a URL?

I use _OOoCalc_WriteCell($oCalc, $NewURL, "N" & $m)

Edited April 24, 2017 by mmoalem

[keafter]

On 4/24/2017 at 3:49 PM, mmoalem said:

but it doesnt work - it works ok if $NewURL is a simple text string but not a URL - any idea if I need a different write command for a URL?

I use _OOoCalc_WriteCell($oCalc, $NewURL, "N" & $m)

What is the raw string of $NewURL and we can work from there.

 

Hey I'm back after a long while again. I'm about to test the auto sort fix....

 

[mmoalem]

13 minutes ago, keafter said:

What is the raw string of $NewURL and we can work from there.

 

Hey I'm back after a long while again. I'm about to test the auto sort fix....

 

Its a URL like this one https://soundcloud.com/memimo-media/sets/always-up-always-onwards copied straight from browser address bar to clipboard and than using ClipGet to the variable $NewURL - the function complete without an error but the cell stays empty (one point: when  i try to close calc straight after it asks if i want to save the changes so maybe the command does alter something in the sheet...)

[keafter]

Try specifying the sheet name you want to edit:

 _OOoCalc_WriteCell($oCalc, $NewURL, "N" & $m, "Sheet 1") 

 

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.

Edited April 26, 2017 by keafter

[mmoalem]

27 minutes ago, keafter said:

Try specifying the sheet name you want to edit:

 _OOoCalc_WriteCell($oCalc, $NewURL, "N" & $m, "Sheet 1") 

will try BUT it works as it is if the value of $NewURL is a 'standard' text string (for example when the value is this string: 'always up' it pastes it into the cell correctly...

[keafter]

Ok cool! Sort works just fine. Here's how I got it to work:

_OOoCalc_RangeSort($OoCalcWindowJobSpreadsheet,"Sheet1","A6:P90",1)

It goes: Workbook Object, Sheet Name, Range To Sort, Column to sort by (in this instance sort by column A).

 

25 minutes ago, mmoalem said:

will try BUT it works as it is if the value of $NewURL is a 'standard' text string (for example when the value is this string: 'always up' it pastes it into the cell correctly...

Hmm If the suggestion I made doesn't work, it might be helpful to have the code to test with. In my experience with autoit to open office, I've had troubles getting dates to be properly input. Luckily there was an easy fix by  using _OOoCalc_NumberFormat. Hmm, what happens if you pull the $NewURL with the url into a msgbox. Does it display the url correctly? I'm just wondering if somehow the structure of the url is being broken up or something. Yeah hmm. Code to look at would be good.

Edited April 26, 2017 by keafter

[GMK]

@mmoalem: I believe you're looking for _OOoCalc_HyperlinkInsert

[mmoalem]

41 minutes ago, GMK said:

@mmoalem: I believe you're looking for _OOoCalc_HyperlinkInsert

Hi and thanks  - got stuck with another project and put this one on hold for couple of weeks but thanks for the suggestion - will let you all know if it worked

[mmoalem]

On 2017-4-26 at 11:45 PM, keafter said:

 

Hmm If the suggestion I made doesn't work, it might be helpful to have the code to test with. In my experience with autoit to open office, I've had troubles getting dates to be properly input. Luckily there was an easy fix by  using _OOoCalc_NumberFormat. Hmm, what happens if you pull the $NewURL with the url into a msgbox. Does it display the url correctly? I'm just wondering if somehow the structure of the url is being broken up or something. Yeah hmm. Code to look at would be good.

Hi unfortunately your suggestions did not work... BUT msgbox does not return anything either (unless i dones something wrong)

On 2017-5-3 at 8:46 PM, GMK said:

@mmoalem: I believe you're looking for _OOoCalc_HyperlinkInsert

this too didnt make a difference

EDIT - i think my problem is that clipget does not work... the clipboard is populated correctly but:

Local $NewURL = ClipGet()

does not seems to populate $NewURL with anything - msgbox returns an exclamation mark 

 

here is the whole code in case it helps

expandcollapse popup

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <OOoCalc.au3>

Opt("WinTitleMatchMode", 2)

;_OOoCalc_ComErrorHandler_UserFunction(_ErrFunc)

Global $sTitle = "Calc Demo"
$sFileName = "C:\Users\admin\Desktop\Autoit\Linkstest.xls"

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, $sFileName)
Global $oCalc = _OOoCalc_BookOpen($sFileName)
If @error Then Exit MsgBox($MB_TOPMOST, "Calc Test", "Unable to create new Book.")

WinWait("Calc")
WinSetState("Calc", "", @SW_MAXIMIZE)
WinActivate("Calc", "")

Global $URL = _OOoCalc_ReadSheetToArray($oCalc, "M1:M162") ;URLs
Global $Name = _OOoCalc_ReadSheetToArray($oCalc, "I1:I162") ;playlist name


For $i = 0 to UBound( $URL, 1) - 1
    For $j = 0 to UBound($URL, 2) - 1

        ClipPut($Name[$i][$j]);copy playlist name from sheet
        RUN('"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" --profile-directory="Profile 18" -new ' & $URL[$i][$j] )
        Sleep (1000)
        WinSetState("mydailypick", "", @SW_MAXIMIZE)
        Sleep (45000);wait for ad to finish
MouseClick ( "",1161,831, 1)
Sleep (2000)
MouseClick ( "",997,202, 1)
Sleep (2000)
Send ("^v");paste playlist name
Sleep (2000)
Send("{Enter}");eneter playlist name
Sleep (2000)
MouseClick ( "",1155,285, 1);go to playlist
Sleep (2000)
send ("!d");select URL in browser
send ("^c");copy selected URL
Local $NewURL = ClipGet()
MouseClick ( "",1891,12, 1);close chrome
Local $m=$i+1
WinActivate("Calc", "")
Sleep (2000)
;_OOoCalc_HyperlinkInsert($oCalc, $NewURL, "N" & $m)
_OOoCalc_WriteCell($oCalc, $NewURL, "N" & $m, "Sheet8")
Sleep (2000)
        _ArrayDisplay($URL, "M1:M162") ;use to break
    Next


Next

 

Edited May 15, 2017 by mmoalem