AIstarter Posted June 1, 2018 Share Posted June 1, 2018 Hi, I'm trying to set autofilter for a sheet - is this by any way possible with this udf? Thanks! Link to comment Share on other sites More sharing options...
mLipok Posted June 1, 2018 Share Posted June 1, 2018 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 Code * for other useful stuff click the following button: Spoiler Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API * ErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 * My contribution to others projects or UDF based on others projects: * _sql.au3 UDF * POP3.au3 UDF * RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF * SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane * Useful links: * Forum Rules * Forum etiquette * Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * Wiki: * Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX IE Related: * How to use IE.au3 UDF with AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler * IE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related: * How to get reference to PDF object embeded in IE * IE on Windows 11 * I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions * EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *I also encourage you to check awesome @trancexx code: * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuff * OnHungApp handler * Avoid "AutoIt Error" message box in unknown errors * HTML editor * winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/ "Homo sum; humani nil a me alienum puto" - Publius Terentius Afer"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming" , be and \\//_. Anticipating Errors : "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty." Signature last update: 2023-04-24 Link to comment Share on other sites More sharing options...
AIstarter Posted June 1, 2018 Share Posted June 1, 2018 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. Link to comment Share on other sites More sharing options...
lastmember Posted March 30, 2019 Share Posted March 30, 2019 Hi ! Is possible to hide a Column/Row for printing? Link to comment Share on other sites More sharing options...
aio Posted May 8, 2019 Share Posted May 8, 2019 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. Link to comment Share on other sites More sharing options...
GaRydelaMer Posted November 17, 2019 Share Posted November 17, 2019 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 Link to comment Share on other sites More sharing options...
PnD Posted May 8, 2021 Share Posted May 8, 2021 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! Link to comment Share on other sites More sharing options...
PnD Posted May 8, 2021 Share Posted May 8, 2021 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. Link to comment Share on other sites More sharing options...
NassauSky Posted July 30, 2021 Share Posted July 30, 2021 (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' 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 Link to comment Share on other sites More sharing options...
GMK Posted August 2, 2021 Author Share Posted August 2, 2021 (edited) @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 Link to comment Share on other sites More sharing options...
GMK Posted August 2, 2021 Author Share Posted August 2, 2021 Also: did you see _OOoCalc_HyperlinkInsert? NassauSky 1 Link to comment Share on other sites More sharing options...
lapoelkan12 Posted July 18, 2022 Share Posted July 18, 2022 hi , if i write a calc file and after i reopen how can restart at point that i left ? thanks Link to comment Share on other sites More sharing options...
mLipok Posted July 18, 2022 Share Posted July 18, 2022 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. 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 Code * for other useful stuff click the following button: Spoiler Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API * ErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 * My contribution to others projects or UDF based on others projects: * _sql.au3 UDF * POP3.au3 UDF * RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF * SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane * Useful links: * Forum Rules * Forum etiquette * Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * Wiki: * Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX IE Related: * How to use IE.au3 UDF with AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler * IE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related: * How to get reference to PDF object embeded in IE * IE on Windows 11 * I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions * EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *I also encourage you to check awesome @trancexx code: * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuff * OnHungApp handler * Avoid "AutoIt Error" message box in unknown errors * HTML editor * winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/ "Homo sum; humani nil a me alienum puto" - Publius Terentius Afer"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming" , be and \\//_. Anticipating Errors : "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty." Signature last update: 2023-04-24 Link to comment Share on other sites More sharing options...
lapoelkan12 Posted July 18, 2022 Share Posted July 18, 2022 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 ? Link to comment Share on other sites More sharing options...
water Posted July 18, 2022 Share Posted July 18, 2022 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 mLipok 1 My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
lapoelkan12 Posted July 18, 2022 Share Posted July 18, 2022 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 __ Link to comment Share on other sites More sharing options...
water Posted July 18, 2022 Share Posted July 18, 2022 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: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
NassauSky Posted July 14, 2023 Share Posted July 14, 2023 (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 July 14, 2023 by NassauSky Clarifying Code and added #include Link to comment Share on other sites More sharing options...
GMK Posted August 10, 2023 Author Share Posted August 10, 2023 (edited) 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 1 Link to comment Share on other sites More sharing options...
NassauSky Posted August 10, 2023 Share Posted August 10, 2023 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now