#] #] ********************* #] "$d_SysMaint"'LibreOffice/1_LibreOffice macros.txt' www.BillHowell.ca 07Sep2013 initial # view in text editor, using constant-width font (eg courier), tabWidth = 3 see also group : "$d_PROJECTS"'Investments/0_banking notes.txt' "$d_Midas"'Investments/0_account macro system notes.txt' "$d_SysMaint"'LibreOffice/1_LibreOffice macros.txt' #48************************************************48 #24************************24 # Table of Contents, generate with : # $ grep "^#]" "$d_SysMaint"'LibreOffice/1_LibreOffice macros.txt' | sed "s/^#\]/ /" # ********************* "$d_SysMaint"'LibreOffice/1_LibreOffice macros.txt' 18Aug2022 adaptation of LibreCalc macros to Suse 10Jun2021 '0_Category_Setup.ods' workbook 05Dec2020 Don't forget Pitonyk!!!! https://www.pitonyak.org/OOME_3_0.pdf 25Aug2017 Remove images from a Write document 05Dec2020 'LibreOffice Help -> Contents tab -> Macros&Progmng -> Command Ref -> Using Procs&Funcs 10Jun2021 use of global macros as formulae in cells 05Dec2020 Function MonthNum_to_shortName(monthNum As Integer) 23Dec2019 LibreCalc macros - alternatives to ThisComponent 14Nov2017 follow-on to 18Sep2017 Cell formulas - need macro function to "see" them 18Sep2017 Fix ToDos update macro 25Aug2017 Remove images from a Write document 06Jun2017 Dsum over a date range 30Apr2017 Banking analysis - dsum problems with dates 30Apr2017 VISA statements - Remove special control characters with date 30Apr2017 Convert text to date 03Apr2017 12:55 24Nov2016 Macro to select another document 24Nov2016 Macro selection of a range to delete 21Nov2016 Problems with basic - IJCNN2017 authors papers 12Nov2016 IEEE prohibited authors macro - Now working! 11Nov2016 LibreCalc function use in macros 10Nov2016 Howell_IEEE_prohibited_macLib/Howell_IEEE_prohibited_script.xba 04Sep2016 run LibreOffice functions (APIs) from command line : cli-UNO-bridge commands 04Sep2016 Host commands from within LibreOffice 09May2016 Macros not found on Lenovo 29Aug2016 Exporting slides to .png files 30Jun2016 Icons in spreadsheet 04May2016 USB siting of [ToDos,contacts] - see my note of 11Feb2015 below 04Apr2016 LibreCalc - password no longer protects 22Mar2016 Copy/Paste doesn't include tabs ?!!?? 11Feb2015 INNS_mass_email_macro 11Feb2015 Creating a [traceable, backupable] library of a spreadsheets macros : 01Feb2015 How do I force a recalc from a macro? 14Jan2015 - Created macro to set background color in "ToDos" sheet 25Nov2014 Copy-over of macros from Toshiba to Lenovo (LinuxMintDeb) 16Nov2014 Again after 12, 13Jul2014 - "non-exisiting" LibreCalc file prevents smooth startup 18Sep2014 Porting macros to Toshiba laptop LinuxMintDeb 12Jul2014 Again after 13Jul2014 - "non-exisiting" LibreCalc file prevents smooth startup 13Jul2014 LibreCalc crashes with row deletes 13Jul2014 "non-exisiting" LibreCalc file prevents smooth startup 01Jul2014 How to set a few pages in a document to landscape? 27May2014 To export all slides to jpeg files 22Mar2014 Calling Calc functions from a macro 17Mar2014 To remove button etc from LibreWriter 18Mar2014 - I discovered this myself 01Mar2014 LibrCalc -> Tools -> Cell contents -> turn OFF recalc & autoinput! 29Oct2013 Fixing ToDos sort so the range is no longer selected 28Oct2013 Archiving LibreOffice Calc macros 24Oct2013 ThisComponent - but how to refer to a different file? 24Oct2013 Sort doesn't work with real numbers! - MUST be INTEGER! 24Oct2013 Unlimited Sort Columns & Custom Sort Order Using Basic 18Oct2013 How to assign values to variables with LibreCalc- setValue() in LibreOffice Calc macro doesn't work 17Oct2013 LibreCalc - Show Changes #24************************24 # Setup, ToDos, #08********08 #] ??Aug2022 #08********08 #] ??Aug2022 #08********08 #] ??Aug2022 #08********08 #] ??Aug2022 #08********08 #] ??Aug2022 #08********08 #] ??Aug2022 #08********08 #] 18Aug2022 Instructions : Connect First [download account activity, update Categories] see "$d_Midas"'Investments/0_account macro system notes.txt' >> Done - works very well!! #08********08 #] 10Jun2021 '0_Category_Setup.ods' workbook Categories : Tools->Macros->Organize->My Macros & Dialogs->Howell_extract_codes ->Howell_extract_codes_script->Howell_extract_code See the macro for detailed instructions. The following ois only part of ' Instructions : ' 15Jun2020 initial, 10Jun2021 augmented (1 year later) ' 1. Open the spreadsheets : ' '0_Category Setup.ods' spreadsheet, used for most, if not all, accounts : ' /media/bill/Midas/Investments/0_Category Setup.ods ' account spreadsheet to process, for example : ' /media/bill/Midas/Investments/0_TD Canada Trust cheq.ods ' 2. Open this script file as "Edit" : ' run Menu -> Tools -> Macros -> Organize macros -> "Howell_extract_codes_script" ' 3. Update the account spreadsheet : ' Banking transaction sheets : LibreOffice macros suck for simple stuff ' Use geany!!! copy-paste transactions of bank statement to new .txt window ' TD dates to LibreOffice : ' geany search ^(...).(..)..(....) replace \2-\1-\3 ' also - get rid of dollar signs ' 4. Click to move cursor into account spreadsheet ' (as macro is be tied to the ACTIVE sheet!!!) ' 5. Put the cursor for this "My Macros & Dialogs" window ' within the "Sub Howell_extract_code()" macro ' -> then click "Run Basic" icon ' 6. WAIT 10 minutes or so - it's slow ' 7. The macro identifies "Items" and puts label in "Category_auto" column ' 8. The macro also identifies the "Category_set" code-word based on the "Category_auto" class ' and puts that in the "Category_set" column +-----+ To see standard macros : LibreOffice Menu -> LibreOffice Help -> Contents -> Macros and Programming -> Command reference -> Run-Time Functions, Statements, and Operators -> select theme etc To use spreadsheet functions (see below, I think somewhere) +-----+ BEST REFERENCE for OpenOffice macros : http://api.libreoffice.org/ This is a great reference. Also good www.pitonyak.org/AndrewMacro.odt - see /home/bill/System maintenance/LibreOffice/Pitonyak 130321 Useful Macro Information For OpenOffice.odt http://www.openoffice.org/documentation/manuals/userguide3/0312CG3-CalcMacros.pdf - kind of Mickey Mouse #] 05Dec2020 Don't forget Pitonyk!!!! https://www.pitonyak.org/OOME_3_0.pdf Remember - spreadsheet function names are NOT necessarily the same as for macros!! +----+ #] 25Aug2017 Remove images from a Write document see macro : /home/bill/.config/libreoffice/4/user/basic/Howell_LibreWrite_remove_images +--+ #] 05Dec2020 'LibreOffice Help -> Contents tab -> Macros&Progmng -> Command Ref -> Using Procs&Funcs 'The FUNCTION is called using the following syntax 'Variable=FunctionName(Parameter1, Parameter2,...) +--+ =DATE(VALUE(MID(E3,11,4)),MONTHSHORTNAME_GET_MONTHNUM(MID(E3,3,3)),VALUE(MID(E3,7,2))) +-----+ Detailed list of methods etc https://api.libreoffice.org/docs/java/ref/index.html See or end of this file for : - List of Howell's LibreCalc macros - Tricks & Treats - Creating a new macro (using an example Howell_IEEE_prohibited_macLib) +-----+ spreadsheet func macros A = Array("Fred","Tom","Bill") Returns the type Variant with a data field. CStr Converts any numeric expression to a string expression. TEXT(s,"###.##") Str (Expression) converts a number to a string Val Function [Runtime] Converts a string to a numeric expression. CAT?? Join (Text As String Array, delimiter) Returns a string from a number of substrings in a string array. 48****************************************************************48 08********08 #] 10Jun2021 use of global macros as formulae in cells https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/ In OpenOffice.org Macros Explained - OOME_3_0 by Andrew Pitonyak -669 pages, on page 514 or section 15.7 is 'Write your own Calc functions'. Here is another example to fill in for a missing function in standard Calc: This Basic Macro adds a new Calc function that can format a number as a string using the Basic (not Calc) format function to do the work, for example you can now format a number as a string with leading zeros or the like: Function FORMAT_(Number As Integer, sFormat As String) As Variant Dim s$ : s=format(CStr(Number), sFormat) Format_ = s End Function Note that this could not simply be named Format, as that name conflicts with the other built in function named Format. (I think it's strange that Calc can't seem to use that built in function, but that is something to figure out another day. Also I was surprised that I couldn't find a function in Calc to format a number as a string, perhaps it's there by a name that I just haven't found yet.) If you just want to keep it simple this works the same as the code above: Function FORMAT_(N, s) As Variant : Format_ = format(CStr(N), s) : End Function Then call your new Basic macro in a cell formula just like any other function, with the name followed by a parenthesized list of parameters, like this: =FORMAT_(A15,"0#") & "/" & FORMAT_(B15,"0#") & "/" & C15 to format a fixed width date string from Day, Month, and Year integer columns. The 0# string parameter says to output a string where if the 10's numeral (in a 2 digit number the left most numeral) is missing then display a zero, and a numeral 0-9 for the right most digit. I.e. 5 --> "05", and 15 --> "15". TIPS: Once you've created your Basic Macro, to get it to work, you must do two things: First save your Calc doc. Then do Menu | File | Reload and be sure to click on Enable Macros when it reloads. This will cause Calc to read the new list of available Basic Macros. Also after you make any further edits to your macro, you can hit the F9 key to cause Calc to re-calculate using the newly edited macro code. +-----+ '0_Category_Setup.ods' workbook Categories : Tools->Macros->Organize->My Macros & Dialogs->Howell_extract_codes ->Howell_extract_codes_script->Howell_extract_code See the macro for detailed instructions. The following ois only part of +--+ olde code : ' Not used anymore 10Jun2002 - I don't know date that they were dropped ' nyet?: Category_DateEnd $summary.$E$3 ' nyet?: Category_DateStart $summary.$E$2 08********08 #] 05Dec2020 Function MonthNum_to_shortName(monthNum As Integer) Function MonthNum_to_shortName(monthNum As Integer) ' from monthShortNamesAssign() monthShortNames(0) = "Jan" : monthShortNames(1) = "Feb" monthShortNames(2) = "Mar" : monthShortNames(3) = "Apr" monthShortNames(4) = "May" : monthShortNames(5) = "Jun" monthShortNames(6) = "Jul" : monthShortNames(7) = "Aug" monthShortNames(8) = "Sep" : monthShortNames(9) = "Oct" monthShortNames(10) = "Nov" : monthShortNames(11) = "Dec" MonthNum_to_shortName = monthShortNames(monthNum) end Function Global monthShortNames(11) As String Sub monthShortNamesAssign() monthShortNames(0) = "Jan" : monthShortNames(1) = "Feb" monthShortNames(2) = "Mar" : monthShortNames(3) = "Apr" monthShortNames(4) = "May" : monthShortNames(5) = "Jun" monthShortNames(6) = "Jul" : monthShortNames(7) = "Aug" monthShortNames(8) = "Sep" : monthShortNames(9) = "Oct" monthShortNames(10) = "Nov" : monthShortNames(11) = "Dec" End Sub ' LibreOffice list of macros -> Help -> Contents -> Macros & programming -> Command Reference : ' -> Runtime [functions, statements, operators] ' -> Alphabetical list of [functions, statements, operators] ' https://help.libreoffice.org/4.3/Calc/User-Defined_Functions ' https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/ ' In OpenOffice.org Macros Explained - OOME_3_0 by Andrew Pitonyak -669 pages, ' on page 514 or section 15.7 is 'Write your own Calc functions'. ' EasyTrieve, 18Jan2020 ' p64 Global ' Use Global to declare a variable that is available to every module in every library. The library containing the ' Global variable must be loaded for the variable to be visible. ' When a library is loaded, it is automatically compiled and made ready for use; this is when a Global variable ' is initialized. Changes made to a Global variable are seen by every module and are persisted even after the ' macro is finished. Global variables are reset when the containing library is compiled. Exiting and restarting ' OpenOffice.org causes all libraries to be compiled and all Global variables to be initialized. Modifying the ' module containing the Global definition also forces the module to be recompiled. ' Global iNumberOfTimesRun ' Variables declared Global are similar to variables declared Static, but Static works only for local variables, ' and Global works only for variables declared in the header. 'LibreOffice Help -> Contents tab -> Macros&Progmng -> Command Ref -> Using Procs&Funcs 'The FUNCTION is called using the following syntax 'Variable=FunctionName(Parameter1, Parameter2,...) ''You can also use the fully qualified name to call a procedure or function: 'Library.Module.Macro() 'For example, to call the Autotext macro from the Gimmicks library, use the following command: 'Gimmicks.AutoText.Main() ' https://stackoverflow.com/questions/47432132/define-global-variables ************** #] 23Dec2019 LibreCalc macros - alternatives to ThisComponent Easy - to reference a spreadsheet, just use : sUrl = convertToURL("/media/bill/Midas/Investments/0_Category Setup.ods") categoryDoc = StarDesktop.loadComponentFromURL(sURL, "_default", 0, Array() ) +-----+ https://ask.libreoffice.org/en/question/52020/how-to-open-ms-excel-file-in-libre-office-macro/ Hi - Something like this: Sub OpenXLS Dim oDoc As Object Dim sUrl As String Dim Prop(0) as New com.sun.star.beans.PropertyValue Prop(0).name="FilterName" Prop(0).value="MS Excel 97" sUrl = convertToURL("c:\Test\foo.xls") if fileExists(sUrl) then oDoc = stardesktop.LoadComponentFromURL(sUrl, "_blank",0, Prop()) else msgbox "Not found" end if End Sub See : API and Filter options Regards answered Jun 18 '15, pierre-yves samyn, 6841 ●15 ●47 >> Howell : is there an equivalent of stardesktop.LoadComponentFromFile? oDoc = stardesktop.LoadComponentFromURL(sUrl, "_blank",0, Prop()) +-----+ not so useful right now : https://ask.libreoffice.org/en/question/17209/how-to-change-thiscomponent/ Hi,Gaffer I think when you write the macro in Calc Document, " ThisComponent" means Calc document. So, "ThisComponent.supportsService("com.sun.star.text.TextDocument")" will return false. I guess your correct code mean following ; Sub Main Dim oDoc as Object, oText as Object, oInsertText as String oDoc = StarDesktop.loadComponentFromURL( "private:factory/swriter", "_blank", 0, Array()) oText = oDoc.getText() oInsrtText = "The quick brown fox jumped over the lazy dogs." oText.insertString(oText.getStart(), oInsrtText , false) End Sub answered May 9 '13, newOOo3 +--+ Many thanks. Your example has set me in the right direction. That is very helpful. Gaffer gravatar imageGaffer ( May 9 '13 ) ****************** #] 14Nov2017 follow-on to 18Sep2017 Cell formulas - need macro function to "see" them /media/bill/HOWELL_BASE/ToDos/0_ToDos.ods +-----+ Abandoned code : 'date_DrumExercise = cell_date_DrumExercise.formula 'date_DrumShop = cell_date_DrumShop.formula 'date_haircut = cell_date_haircut.formula 'date_laundry = cell_date_laundry.formula 'date_longTerm = cell_date_longTerm.formula 'date_MomDad = cell_date_MomDad.formula 'For jRow = 43 to 49 '**************************** ' don't work : ' wasted attempt to get what I already know! ' cell_addr(0) = "D" ' cell_addr(1) = "hamburger" ' cell_addr(1) = str(jRow) ' cell_address = join(cell_addr(),"") ' MsgBox cell_address ' OK - this gives correct cell address ' cell_formula = oCell.formula ' Always returns 42960 - but when there is no formula!!!! ' cell_formula = formula(oCell) ' BASIC runtime error. ' Sub-procedure or function procedure not defined. ' svc = createUnoService( "com.sun.star.sheet.FunctionAccess" ) ' cell_formula = svc.callFunction("FORMULA",oCell) ' BASIC runtime error. ' An exception occurred ' Type: com.sun.star.lang.IllegalArgumentException ' Message: cannot coerce argument type during corereflection call' ' svc = createUnoService( "com.sun.star.sheet.FunctionAccess" ) ' arg = String(cell_address) ' BASIC runtime error. ' Object variable not set. ' arg = Object(cell_address) ' BASIC runtime error. ' Sub-procedure or function procedure not defined. ' cell_formula = svc.callFunction("FORMULA",arg) ' cell_formula = oCell.getFormula ' Always returns 42960 '***************************** ' MsgBox cell_formula print jRow, oCell.value, oCell.formula, oCell.FormulaLocal +-----+ All the rest is now useless - problem solved! I tried : "cell_formula = svc.callFunction("FORMULA",oCell)" >> Error : BASIC runtime error. An exception occurred Type: com.sun.star.lang.IllegalArgumentException Message: cannot coerce argument type during corereflection call' +-----+ LibreOffice macro help - this might help with CreateUnoValue Function [Runtime] Returns an object that represents a strictly typed value referring to the Uno type system. This object is automatically converted to an Any of the corresponding type when passed to Uno. The type must be specified by its fully qualified Uno type name. The LibreOffice API frequently uses the Any type. It is the counterpart of the Variant type known from other environments. The Any type holds one arbitrary Uno type and is used in generic Uno interfaces. Syntax: oUnoValue = CreateUnoValue( "[]byte", MyBasicValue ) to get a byte sequence. If CreateUnoValue cannot be converted to the specified Uno type, and error occurs. For the conversion, the TypeConverter service is used. This function is intended for use in situations where the default Basic to Uno type converting mechanism is insufficient. This can happen when you try to access generic Any based interfaces, such as XPropertySet::setPropertyValue( Name, Value ) or X???Container::insertBy???( ???, Value ), from LibreOffice Basic. The Basic runtime does not recognize these types as they are only defined in the corresponding service. In this type of situation, LibreOffice Basic chooses the best matching type for the Basic type that you want to convert. However, if the wrong type is selected, an error occurs. You use the CreateUnoValue() function to create a value for the unknown Uno type. You can also use this function to pass non-Any values, but this is not recommend. If Basic already knows the target type, using the CreateUnoValue() function will only lead to additional converting operations that slow down the Basic executi Search "LibreOffice Calc and macro to get the FORMULA of a cell" https://ask.libreoffice.org/en/question/77399/setting-a-formula-in-macro-basic/ answered Sep 15 '16, Ratslinger The formula is a string, opened and closed by a quote. In Basic quotes contained within must be doubled sheet.getCellByPosition(3,16).setFormula("=IF(B17="""";"""";1)") ****************** #] 18Sep2017 Fix ToDos update macro problem with "Today" -> "Todayer" range name -> fixed problem with colors of days : want to use formulae, not values, for [Mom&Dad_visit, Drumheller_exercise, Drumheller_shop, haircut, laundry] Current - date based : +-----+ date_today = cell_date_today.Value date_DrumExercise = cell_date_DrumExercise.Value date_DrumShop = cell_date_DrumShop.Value date_MomDad = cell_date_MomDad.Value date_longTerm = cell_date_longTerm.Value For jRow = row_start to row_ender oCell = oSheet.GetCellByPosition(col_start, jRow) cell_date = oCell.value Select Case cell_date Case date_DrumExercise oCell.CellBackColor = color_DrumExercise +-----+ Want something like : +-----+ date_today = "=date_today" date_DrumExercise = "=date_DrumExercise" date_DrumShop = "=date_DrumShop" date_MomDad = "=date_MomDad" date_longTerm = "=date_longTerm" For jRow = row_start to row_ender oCell = oSheet.GetCellByPosition(col_start, jRow) cell_date = oCell.value Select Case cell_date Case date_today oCell.CellBackColor = color_today Case Else oCell.CellBackColor = color_white End Select cell_formula= oCell.formula ' MsgBox cell_formula IF cell_formula = date_DrumExercise THEN ' MsgBox "Cell formula= date_DrumExercise" oCell.CellBackColor = color_DrumExercise EndIf IF cell_formula = date_DrumShop THEN ' MsgBox "Cell formula= date_DrumShop" oCell.CellBackColor = color_DrumShop EndIf IF cell_formula = date_MomDad THEN ' MsgBox "Cell formula= date_MomDad" oCell.CellBackColor = color_MomDad EndIf IF cell_formula = date_longTerm THEN ' MsgBox "Cell formula= date_longTerm" oCell.CellBackColor = color_longTerm EndIf Next jRow +-----+ https://forum.openoffice.org/en/forum/viewtopic.php?f=5&t=7915 oCell.formula -> same as my wild guess above First - try date value only to make sure that works Problem - cell_formula doesn't change color of cell - only cells afterwards!??? ************* #] 25Aug2017 Remove images from a Write document see macro : /home/bill/.config/libreoffice/4/user/basic/Howell_LibreWrite_remove_images ************** #] 06Jun2017 Dsum over a date range I can't remember where I already did this ... **************** #] 30Apr2017 Banking analysis - dsum problems with dates see banking transaction sheets Remember - criteria in same row have AND, same column OR Example "/media/bill/HOWELL_BASE/Investments/TD Canada Trust.ods" Selection period Time series date_start >2004-03-31 31Mar2004 date_end <2020-01-01 01Jan2020 Code Date Date CANADA PSS >2004-03-31 <2020-01-01 **************** #] 30Apr2017 VISA statements - Remove special control characters with date Select & cut one example Search & Replace all with nothing Copy&Paste from online monthly display : Normal copy&replace selecting "Custom" and "Recognize dates, numbers" From : 30Jun2016 Icons in spreadsheet edit -> links -> select all links -> break **************** #] 30Apr2017 Convert text to date 1. Create a "Date code" column 2. Formula to convert text-to-date, example : '=DATE(MID(D6746,9,4),VLOOKUP(MID(D6746,1,3),month_num,2),MID(D6746,5,2)) 3. Once OK, paste back into Date column as date Also from MBNA sheet : '=DATE(VALUE(MID(D6,7,4)),VALUE(MID(D6,1,2)),VALUE(MID(D6,4,2))) **************** #] 03Apr2017 12:55 LibreOffice recovery - files blocked When recovering LibreOffice, often files that were often are blocked (red X) from re-loading. Why? and how do I prevent this from recurring every recovery of reboot? I solved this in the past, but I don't remember how... Can't see anything pertinent in /home/bill/.config/libreoffice Close all open files Open one of "locked" files : 0_todoz_historical_cumulative_timesheet.ods ASCII codes.ods Cancel the recovery. OK - now the recovery doesn't have the "locked" files. ***************** #] 24Nov2016 Macro to select another document https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=59932 Macro code for changing into another opened document Postby ptbento » Sat Feb 23, 2013 3:31 pm I have code performing actions in doc A and I would like to go to doc B, perform actions (based on info gathered from doc A), and get back to doc A in the same place. What would be the command for this? Thanks, +-----+ Re: Macro code for changing into another opened document Postby RPG » Sat Feb 23, 2013 8:01 pm Hallo I have done a little test. Romke Code: Select all Expand view sub openform2 dim sNewfileFormName sNewfileFormName="file:///home/romgro/Documenten/opof/data/exampledata.ods" 'stardesktop.loadComponentFromURL(sNewfileFormName,"_blank",0,array()) ' _blank open a new view stardesktop.loadComponentFromURL(sNewfileFormName,"_default",0,array()) ' sets focus to the document end sub >> Howell - ALSO!!! Sub RunMacroInAnotherDoc() -> will be handy some day!!! **************** #] 24Nov2016 Macro selection of a range to delete http://www.debugpoint.com/2015/03/calc-cell-selection-processing-using-macro/ Calc Cell Selection Processing Using Macro >> This is to identify user-selected ranges, no use here but could be useful in the future https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=2562 Clear cell contents Macro help Re: Clear cell contents Macro help Postby kingfisher » Mon Feb 11, 2008 11:01 pm You should install the Xray tool and the SDK. The former is on the developers' page of http://www.ooomacros.org The latter is probably available from the mirrors. First, the information about clearContents. That contains a link to the flags page. I find it simpler to use numerical values. To delete strings only : Code: Select all Expand view CellRange.clearContents( 4 ) To clear simple values (i.e. not dates or times) : Code: Select all Expand view CellRange.clearContents( 1 ) To clear both strings and simple values : Code: Select all Expand view CellRange.clearContents( 5 ) kingfisher, Posts: 2109, Joined: Tue Nov 20, 2007 10:53 am Postby martius » Mon Dec 19, 2011 2:52 pm If you want to clear eveything this is the best way: Code: Select all Expand view Sub clearEverything Thiscomponent.CurrentSelection.ClearContents(1 OR 2 OR 4 OR 8 OR 16 OR 32 OR 64 OR 128 OR 256 OR 512) End Sub 'constants group CellFlags 'com.sun.star.sheet.CellFlags. 'These constants select different types of cell contents. 'The values can be combined. They are used to insert, copy, or delete contents. 'com.sun.star.sheet.CellFlags. 'VALUE = 1 ->selects constant numeric values that are not formatted as dates or times. 'DATETIME = 2 ->selects constant numeric values that have a date or time number format. 'STRING = 4 ->selects constant strings. 'ANNOTATION = 8 ->selects cell annotations. 'FORMULA = 16 ->selects formulas. 'HARDATTR = 32 ->selects all explicit formatting, but not the formatting which is applied implicitly through style sheets. 'STYLES = 64 ->selects cell styles. 'OBJECTS = 128 ->selects drawing objects. 'EDITATTR = 256 ->selects formatting within parts of the cell contents. 'FORMATTED = 512 ->selects cells with formatting within the cells or cells with more than one paragraph within the cells. **************** #] 21Nov2016 Problems with basic - IJCNN2017 authors papers Dim paprnAutVal As String paprnAutStr = paprnAutCel.String - returns "" paprnAutVal = paprnAutCel.Value - returns "0" Dim paprnAutVal As Integer paprnAutVal = paprnAutCel.Integer >> Basic error: property or method not found : Integer http://extensions.libreoffice.org/extension-center/mri-uno-object-inspection-tool MRI can set property values that have numeric, string, enum or boolean >> can't use "Enum" as Property type?! try "Object" in crawl_authors paprnAutVal = paprnAutCel.Object >> Basic error: property or method not found : Object try : Dim paprnAutVal As Value paprnAutVal = paprnAutCel.Value >> OOPS!! col_paprnAut wasn't declared - empty in crawl_authors!! >> But paprnAutVal still = 0 Dim paprnAutVal As String paprnAutStr = paprnAutCel.String >> WORKS!!! (my stupid mistake) ************ #] 12Nov2016 IEEE prohibited authors macro - Now working! /media/bill/HOWELL_BASE/System_maintenance/LibreOffice/macro_IEEE_prohibited_authors.txt www.BillHowell.ca 09Nov2016 initial, 12Nov2016 now works LibreCalc macro for marking prohibited author papers Based on Howell_ToDos_macLib Executable is in : /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/Howell_IEEE_prohibited_script.xba LibreOffice Calc macro Currently uses a simple approach, needs refinements for : - data entry problems eg [extra, leading, trailing] spaces - swapped first/last names - common versus formal names ************* #] 11Nov2016 LibreCalc function use in macros "MID" works, but not "FIND" or "SEARCH" (which are more basic & important) : 'commaBanPsn = find(",",namerBan) svc = createUnoService( "com.sun.star.sheet.FunctionAccess" ) arg = Array(",",namerBan,1) commaBanPsn = svc.callFunction("SEARCH",arg) 'lastrBanStr = mid(namerBanStr,1,commaBanPsn-1) svc = createUnoService( "com.sun.star.sheet.FunctionAccess" ) arg = Array(namerBanStr,1,commaBanPsn-1) lastrBanStr = svc.callFunction("MID",arg) 'firstBanStr = mid(namerBanStr, commaBanPsn+2, len(namerBanStr)-commaBanPsn) svc = createUnoService( "com.sun.star.sheet.FunctionAccess" ) arg = Array(namerBanStr, commaBanPsn+2, len(namerBanStr)-commaBanPsn) firstBanStr = svc.callFunction("MID",arg) neither "FIND" or "SEARCH" are m,acro functions (see help listing) >> AH HAH! - InStr function!!! "0" denotes case-sensistive (for comma) 'commaBanPsn = find(",",namerBan) commaBanPsn = InStr(",",namerBan,0) ************* #] 10Nov2016 Howell_IEEE_prohibited_macLib/Howell_IEEE_prohibited_script.xba database-type script 1. I set up the macro in LibreCalc - see "Creating a new macro" instructions above 2. Initial test before CONVERTING the macro, by opening a spreadsheet : >> in LibreCalc : Tools -> macros -> Organize macros -> LibreOffice basic >> is listed : Howell_IEEE_prohibited_macLib >> OOPS - script is labelled "Howell_ToDos_script!!! 3. Correct the script name : in /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/script.xlb lines : changed to : >> OK, now it seems to work 4. Convert the macro to what I need : ************* #] 04Sep2016 run LibreOffice functions (APIs) from command line : cli-UNO-bridge commands (from /media/bill/HOWELL_BASE/Projects/ACM Facility Safety presentation 01Sep2016/video production/0_vidProdn notes ACM.txt) Size on-screen view of [heading, reference] bands for screen capture search "libreoffice write and host commands" http://unix.stackexchange.com/questions/140537/run-terminal-commands-from-within-libreoffices-basic-programming Run terminal commands from within LibreOffice's Basic programming? Is there a way to run system commands in LibreOffice Basic macros so that macros will be able to do things outside LibreOffice (e.g. changing keyboard layout)? edited Jul 3 '14 at 13:30, slm♦ asked Jul 3 '14 at 12:05, user69453 >> might be useful for some other project http://pythonhosted.org/iac-protocol/lowriter.html This page provides a guide on how to setup and use LibreOffice Writer for the IAC protocol. Warning : LibreOffice needs to have the UNO bridge running for it to function properly. Please see the instructions for more details https://community.linuxmint.com/software/view/cli-uno-bridge cli-uno-bridge "Openoffice.org is a full-featured office productivity suite that provides a near drop-in replacement for microsoft(r) office. this package contains the cli bindings to openoffice.org for accessing the openoffice.org api from mono languages (e.g. c# and boo) note that currently the use of mono for add-ins & scripting inside openoffice.org is *not* yet supported." ...I didn't do anything with it today - will stick with LibreOffice Write copy&paste ************* #] 04Sep2016 Host commands from within LibreOffice http://unix.stackexchange.com/questions/140537/run-terminal-commands-from-within-libreoffices-basic-programming Run terminal commands from within LibreOffice's Basic programming? Is there a way to run system commands in LibreOffice Basic macros so that macros will be able to do things outside LibreOffice (e.g. changing keyboard layout)? edited Jul 3 '14 at 13:30, slm♦ asked Jul 3 '14 at 12:05, user69453 >> might be useful for some other project ******************** #] 09May2016 Macros not found on Lenovo After successfully switching from Lenovo to Toshiba computers, now the macros don't work going the other way (I probably first got them to work on Toshiba, and am trying Lenovo for 1st time thereafter). A Scripting Framework error occurred while running the Basic script vnd.sun.star.script:Howell_ToDos_MnthCnt_macLib.Module1.ToDos_Month_Count_update?language=Basic&location=application. Message: The following Basic script could not be found: library: 'Howell_ToDos_MnthCnt_macLib' module: 'Module1' method: 'ToDos_Month_Count_update' location: 'application' /home/bill/.config/libreoffice/4/user/basic/Howell_ToDos_MnthCnt_macLib There are no macros? where did I put them? >> /media/bill/USB DISK/ToDos/basic/Howell_ToDos_MnthCnt_macLib >> note difference : Howell_ToDos_macro_library (empty) Howell_ToDos_macLib I copied the Lenovo "basic" directory to : /home/bill/.config/libreoffice/4/user/basic_backup 160509 then copied, USB : /media/bill/USB DISK/ToDos/basic to , Lenovo : /home/bill/.config/libreoffice/4/user/basic I prefer to have all macros on the USB to avoid duplicate copies on different systems! So in /home/bill/.config/libreoffice/4/user/basic/script.xlc I replaced : With : Then I copied : /media/bill/USB DISK/ToDos/basic/script.xlc To : /home/bill/.config/libreoffice/4/user/basic/script.xlc Then I closed all LibreCalc files, then DesktopSetup -> start_ToDos 1 Fix [macros, formulae] in : /media/bill/USB DISK/ToDos/0_ToDos_Month_Count.ods especially the table plus the range A37:I62 Change : 0_ToDos_Lenovo.ods To : 0_ToDos.ods Change : 'file:///home/bill/Contacts/0_ToDos.ods' To : 'file:///media/bill/USB DISK/ToDos/0_ToDos.ods' Change : file:///media/bill/USB DISK/ToDos/0_todos.ods To : file:///media/bill/USB DISK/ToDos/0_ToDos.ods The press macro button, and delete old links. Check that links are not broken now!! >> So far, this seems to have worked Fix [macros, formulae] in : 0_ToDos.ods Actually, there aren't so many formulae that I need to worry about (famous last words) >> Again, it sems to work for now... **************** #] 29Aug2016 Exporting slides to .png files search "libreoffice impress export slides as images" Solution: Menu -> Files -> Export -> specify filename, directory, UNCLICK retain transparency https://ask.libreoffice.org/en/question/24507/how-do-i-export-all-slides-as-images-in-impress/ oweng, answered Oct 27 '13 Apart from the extension linked to there is no elegant method of doing this, however there is a workaround that may suffice: Export to HTML. File > Export... > select the File Type of "HTML Document (Impress)"; click Save. This will start the HTML Export wizard. Click through the default settings on most of the dialogs, with the exception of the third, which offers a choice of graphics format (PNG, GIF, JPG) and resolution (640x480, 800x600, 1024x768). Click Create once you get to the end, or at any time. The chosen settings can be saved as a Design for future use. The result will be a series of imgN.html and corresponding imgN.png (if PNG was selected) files, one per slide. The HTML files can be deleted if not required. http://extensions.libreoffice.org/extension-center/export-as-images Export As Images 0.9.3 Released Apr 03, 2016 — tested with LibreOffice 5.1 This extension lets you export all the Impress slides or Draw pages as images of JPG, PNG, GIF, BMP and TIFF format. **************** #] 30Jun2016 Icons in spreadsheet I found out how to get rid of this before - but can't find my notes edit -> links -> select all links -> break ********************* #] 04May2016 USB siting of [ToDos,contacts] - see my note of 11Feb2015 below +-----+ New version of : /home/bill/.config/libreoffice/4/user/basic/script.xlc +-----+ Adapt the following : In /home/bill/.config/libreoffice/4/user/basic/ 1. dialog.xlc - doesn't require changes 2. script.xlc - open with kwrite : a) copy an existing line such as b) change to . current status of /home/bill/.config/libreoffice/4/user/basic/script.xlc . Test accessibility : 1. Through the normal Menu -> Tools -> macros -> Organize macros -> LibreOffice Basic -> expand the directory "My Macros" Normally, the new library WON'T appear! 2. Close ALL LibreOffice spreadsheets & documents in ALL Desktops 3. Re-Open "/home/bill/Contacts/0_calendar_Lenovo.ods" ONLY (for testing this is simpler as it is a small file with no macros!) Through the normal Menu -> Tools -> macros -> Organize macros -> LibreOffice Basic -> the new macro library should appear : "Howell_INNS_mass_email_macLib" expand this directory and you should see "Howell_INNS_mass_email_script" . Through the normal Menu -> Tools -> macros -> Organize macros -> LibreOffice Basic -> Edit the text in the original macros for "INNS mass email list.ods" +------------------+ Backup of current version of : /home/bill/.config/libreoffice/4/user/basic/script.xlc ***************** #] 04Apr2016 LibreCalc - password no longer protects Save As -> check "Save with password" ********************** #] 22Mar2016 Copy/Paste doesn't include tabs ?!!?? SOLVED! >> unformatted paste (Ctrl-Shift-Alt) worked!!! To change diverse tabs in a section of text : Select the text -> Menu -> Format -> Clear direct formatting (but all formatting is lost!!) Actually, this problem seems to stem from the right TEXT margin in the source file being before the left PAGE margin in the destination file? When going : from : /home/bill/Projects/Lucas - Universal Force/Working document.odt to : /home/bill/Projects/Lucas - Universal Force/Howell - Background math for Lucas Universal Force, Chapter 4.odt but6 OK the other way around Tabs and indents - didn't help very frustrating and could kill LibreOffice Writer!! **************************************** #] 11Feb2015 INNS_mass_email_macro oDoc = ThisComponent 'specify correct sheet, select the range to sort oSheet = oDoc.Sheets.getByName("ToDos") oRange = oSheet.getCellRangeByName("ToDos_sortRange") ThisComponent.getCurrentController.select(oRange) 'oDoc.getCurrentController.select(oRange) http://ask.libreoffice.org/en/answers/6342/revisions/ Built in functions in LibreOffice can be called from a basic macro, but the argument to the function needs to be passed as an array. I found that tip here. Here is a function that calls the built in EASTERSUNDAY function function esunday(year) svc = createUnoService("com.sun.star.sheet.FunctionAccess") arg=array(year) esunday=svc.callFunction("EASTERSUNDAY",arg) end function I can't speak to any problem with Å, but maybe you can work it out and let us know. >> Howell - this is shit! Can't get it to work as I don't understand uno and the structures !!!!!!!!! FINALLY - after an entire day of frustration - Success!!! see macros in : /home/bill/.config/libreoffice/4/user/basic/Howell_INNS_mass_email_macLib/ **************************************** #] 11Feb2015 Creating a [traceable, backupable] library of a spreadsheets macros : today - macros for "INNS mass email list.ods" see 28Oct2013 Archiving LibreOffice Calc macros export recalc_INNS_mass_emails to library Howell_INNS_mass_emails_macLib > Hard to find!! I created the menu "MacLibrary". To access functions for libraries : Menu -> MacLibrary -> Organize Macros -> LibreOffice Basic -> Organizer -> Libraries tab -> select source (eg INNS mass email list.ods") ... oops - can't select "Export"? Doesn't work (arrrggghhhh!) . !!!!!!!!!!!!!!!!!!!!!!!!! THE FOLLOWING WORKS - but I later changes filenames etc... Try just creating a directory for the macro library and copying the macro dirs&files : /home/bill/.config/libreoffice/4/user/basic/Howell_INNS_mass_email_macLib from : /home/bill/.config/libreoffice/4/user/basic/Howell_ToDos_macLib/ to : /home/bill/.config/libreoffice/4/user/basic/Howell_INNS_mass_email_macLib/ copy : [dialog.xlb, Howell_ToDos_macros.xba, script.xlb] rename "Howell_ToDos_macros.xba" to "Howell_INNS_mass_emails_macros.xba" edit : [dialog.xlb, Howell_ToDos_macros.xba, script.xlb] to use macros that have 1. in dialog.xlb library name : replace "Howell_ToDos_macro_library" with "Howell_INNS_mass_email_macLib" 2. in Howell_INNS_mass_emails_macros.xba script name : replace "Howell_ToDos_script" with "Howell_INNS_mass_email_script" 3. in script.xlb library name : replace "Howell_ToDos_macro_library" with "Howell_INNS_mass_email_macLib" change to 4. copy macros from "INNS mass email list.ods" into the file /home/bill/.config/libreoffice/4/user/basic/Howell_INNS_mass_email_macLib/Howell_ToDos_macros.xba 5. Note : deletion of old macros will occur at a later step below, and will be done through the normal Menu -> Tools -> macros -> Organize macros -> Libreoffice basic -> 6. save all 3 files and close NOTE : Do not need to make some changes a) replace ALL apostrophes "'" with "'" b) replace ALL quotes """ with """ . In /home/bill/.config/libreoffice/4/user/basic/ 1. dialog.xlc - doesn't require changes 2. script.xlc - open with kwrite : a) copy an existing line such as b) change to . current status of /home/bill/.config/libreoffice/4/user/basic/script.xlc . Test accessibility : 1. Through the normal Menu -> Tools -> macros -> Organize macros -> LibreOffice Basic -> expand the directory "My Macros" Normally, the new library WON'T appear! 2. Close ALL LibreOffice spreadsheets & documents in ALL Desktops 3. Re-Open "/home/bill/Contacts/0_calendar_Lenovo.ods" ONLY (for testing this is simpler as it is a small file with no macros!) Through the normal Menu -> Tools -> macros -> Organize macros -> LibreOffice Basic -> the new macro library should appear : "Howell_INNS_mass_email_macLib" expand this directory and you should see "Howell_INNS_mass_email_script" . Through the normal Menu -> Tools -> macros -> Organize macros -> LibreOffice Basic -> Edit the text in the original macros for "INNS mass email list.ods" **************************************** #] 01Feb2015 How do I force a recalc from a macro? see 07Sep2013 : /home/bill/Contacts/0_ToDos_Month_Count.ods Update macro - very simple, but what a pain in the ass finding shit! (just to enter text into a cell!!!) >> No good I simply GUESSED !!! : put in "Calculate" below document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") dispatcher.executeDispatch(document, ".uno:Calculate", "", 0, Array()) >> Seems to work? not sure... **************************************** #] 14Jan2015 - Created macro to set background color in "ToDos" sheet *********** #] 25Nov2014 Copy-over of macros from Toshiba to Lenovo (LinuxMintDeb) Copy : /home/bill/System_maintenance/bin/basic macros 141123/[Howell_ToDos_macLib, Howell_ToDos_macro_library, Howell_ToDos_MnthCnt_macLib] To : /home/bill/.config/libreoffice/4/user/basic Edit : /home/bill/.config/libreoffice/4/user/basic/script.xlc Start with : Add lines : To get : ******* #] 16Nov2014 Again after 12, 13Jul2014 - "non-exisiting" LibreCalc file prevents smooth startup As per the comment 13Jul2014, by doing once "Cancel document recovery", it permanently clears the item causing the problem ********************** #] 18Sep2014 Porting macros to Toshiba laptop LinuxMintDeb Initial attempt did NOT carry-over macros? /home/bill/System_maintenance/LibreOffice/macro_backups/basic 140913/ The files are there in TWO places! (probably error of /home/bill/bin/keyFiles_archive_weekly_script) : .../Howell_ToDos_macLib/ .../basic/Howell_ToDos_macLib/ Only need to backup FULL /home/bill/.config/libreoffice/4/user/basic/ correction made to /home/bill/bin/keyFiles_archive_weekly_script must check next backup! Try to copy /home/bill/.config/libreoffice/4/user/basic/ to Fujitsu 100 Gb USB drive : Result : OK, itworked this time? I will try macros on Toshiba ******* #] 12Jul2014 Again after 13Jul2014 - "non-exisiting" LibreCalc file prevents smooth startup IJCNN Mass email instructions tfor all senders.odt 2014 dance work schedule.xlsx Check ~/.config/libreoffice Forget it! SIMPLE Solution - just click "Cancel doc recovery" with LibreOffice startup!!!! ********* #] 13Jul2014 LibreCalc crashes with row deletes - fairly recent, recurring problem in spite of reboots, file saves etc - error window notice of "vector::_M_range_check", then crashed all of LibreOffice I've had this problem before but forget how I fixed it. Google - many problems with this in recent months, seems that slight verion upgrades help I ran Muon upate Manager... ********** #] 13Jul2014 "non-exisiting" LibreCalc file prevents smooth startup I've had this problem before and forget how I solved it. Maybe in /home/bill/.config/libreoffice/4/user/config or macros defns - I forget where this is? *********** #] 01Jul2014 How to set a few pages in a document to landscape? ... the following worked beautifully! http://listarchives.libreoffice.org/global/users/msg16166.html Re: [libreoffice-users] How do I change page orientation for only ONE page in Writer? [Thread Prev] | [Thread Next] [Date Prev] | [Date Next] Subject: Re: [libreoffice-users] How do I change page orientation for only ONE page in Writer? From: Calvin Kim Date: Tue, 31 Jan 2012 20:01:20 -0500 To: users@global.libreoffice.org On 01/31/2012 06:34 PM, Brian Barker wrote: At 13:31 31/01/2012 +0200, Konstantinos Tzoannopoulos wrote: I need to change the orientation of only ONE page in Writer, but for some reason it only lets me do it for ALL the pages. Anyone know what I'm doing wrong? Page orientation is a property of page styles - so if all your pages have the same page style they will all have the same orientation. If you change it, the change will apply to all pages. Perhaps all your pages have the Default (portrait) page style. There is a built-in Landscape page style which has the other orientation and may suit your purpose. o Put the cursor at the end of the last portrait page. o Go to Insert | Manual Break... . o Under Type, select "Page break". o Under Style, select Landscape from the drop-down list. o Now put the cursor at the end of the single landscape page. o Go to Insert | Manual Break... . o Under Type, select "Page break". o Under Style, select Default from the drop-down list. Each page break is associated with a change in page style: from Default to Landscape and back again. If these built-in page styles do not suffice, you can modify them or even create your own, of course. I trust this helps. Brian Barker And make sure not to use keyboard shortcut +. This will not give you options descbribed above. cK **************************************** #] 27May2014 To export all slides to jpeg files This already exists! http://extensions.libreoffice.org/extension-center/export-as-images Export all the Impress slides or Draw pages as images of JPG, PNG, GIF, BMP and TIFF format. Project Description This extension lets you export all the Impress slides or Draw pages as images of JPG, PNG, GIF, BMP and TIFF format. I adds a menu entry "Export as images..." to File menu and allows you to choose a file name for exported images, image size as well as some other parameters. PERFECT!!! +-----+ Sub Main Dim oDocs, oDoc, oSheets, oSheet, oCells, oCell, oPresentation, oRange As Object Dim slide_index, slide_count As Integer oDoc = ThisComponent oPresentation = Doc.Presentation opages = oDoc.drawpages oController = oDoc.CurrentController slide_count = opages.Count() For slide_index = 1 to slide_count oDispatch = createUnoService( "com.sun.star.frame.DispatchHelper") oProvider = oController.Frame oDispatch.executeDispatch(oProvider, ".uno:Export","", 0, Array()) oPage = opages.getByIndex(slide_index - 1) oController.setCurrentPage(oPage) oDispatch.executeDispatch(oProvider, ".uno:Paste", "", 0, Array()) opages.remove(oCurrentPage) oController.setCurrentPage(opages(slide_index)) Next slide_index End Sub +-----+ http://rknet.pl/student/2013_2014/pg/mat_pom/LibreOfficeMakra-Rysunki-i-prezentacje.pdf This chapter provides an introduction to the macro-controlled creation and editing of drawings and presentations. The first section describes the structure of drawings, including the basic elements that contain drawings. The second section addresses more complex editing functions, such as grouping, rotating, and scaling objects. The third section deals with presentations. Information about creating, opening, and saving drawings can be found in Working With Documents Howell - too short a chapter - not much use!! http://www.7loop.com/content/libreoffice-impress-merge-slides-macro Inviato da luca il Ven, 11/05/2012 - 16:27 A macro to merge all slides inside a folder, working on LibO 3.5. (Part of the code comes from oooforum.org) Howell - a bit long and indirect to be of real help? https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=68461 [REQ] Impress macro to move selected slide to last Postby Tommy » Wed Mar 19, 2014 5:33 pm is there any way to accomplish that? having to manually drag a selected slide to the last position can be annoying if you have a very long presentation a macro to directly move that slide to the last position would be a time-saver. +-----+ Re: [REQ] Impress macro to move selected slide to last Postby F3K Total » Wed Mar 19, 2014 9:37 pm Nearly: This code copies the current slide to last, and then deletes it and jumps to the next. Code: Select all Expand view Sub S_move_selected_page_to_last oDoc = ThisComponent opages = oDoc.drawpages oController = oDoc.CurrentController oCurrentPage = oController.CurrentPage nIndex = oCurrentPage.Number - 1 oDispatch = createUnoService( "com.sun.star.frame.DispatchHelper") oProvider = oController.Frame oDispatch.executeDispatch(oProvider, ".uno:Copy","", 0, Array()) oPage = opages.getByIndex(opages.Count() - 1) oController.setCurrentPage(oPage) oDispatch.executeDispatch(oProvider, ".uno:Paste", "", 0, Array()) opages.remove(oCurrentPage) oController.setCurrentPage(opages(nIndex)) End Sub R from http://en.libreofficeforum.org/node/1200 Thu, 25 Aug 2011 - 9:43 AM, Apostolo, Last seen: 2 years 39 weeks ago, Joined: 25 Aug 2011 .... Sub main Dim Presentation As Object dim showController As object dim RandomNumber RandomNumber = RandomNumberF() ' a function that returns a random number between the 2 and last slide. Doc = StarDesktop.CurrentComponent Presentation = Doc.Presentation showController = Presentation.getController() showController.CurrentPage = Doc.getDrawPages().getByIndex( RandomNumber ) end sub See 22Mar2014 below as well http://stackoverflow.com/questions/12434803/path-of-current-document-in-libreopenoffice asked Sep 15 '12 at 5:35 dubbaluga How can I determine the path of the currently open document in an OpenOffice or LibreOffice document? I want to forward the path to an external application. More specifically I want to launch an external player from impress with a video file in full-screen mode: Shell("/usr/bin/mplayer", 2, """ -fs"" some-file-in-same-dir-as-document.mp4") For the last parameter I would need the path to the currently opened document in order to append it. Not specifying the path results in referring to The current path (as given by the CurDir() function) which is something different. Is there actually a good reference for LibreOffice Macros? The only relatively good (but hard to search) document I have found is "OpenOffice Macros Explained" by Andrew Pitonyak. ... In the meantime I have found a feasible solution. Look here: Sub RunSomeMovie GlobalScope.BasicLibraries.loadLibrary("Tools") Shell("/usr/bin/mplayer -fs ", 2, "" & Tools.Strings.DirectoryNameoutofPath(ThisComponent.getURL(),"/") & "/media/somemovie.mp4") End Sub **************************************** #] 22Mar2014 Calling Calc functions from a macro https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=30380 FJCC Moderator Posts: 3593 Joined: Sat Nov 08, 2008 8:08 pm Location: Colorado, USA Re: Can't use MIN or MAX in my macro function Postby FJCC » Sat May 08, 2010 5:41 pm Expanding on my last post a bit, you can access any spreadsheet function with the com.sun.star.sheet.FunctionAccess service. You just have to pass it the function name and the arguments of the function in an array. You can use the Vlookup function like this. Code: Select all Expand view Sub Exmpl3 Sheet = ThisComponent.Sheets.getByIndex(0) oCellRange = Sheet.getCellRangeByName("A1:B10") SearchValue = Sheet.getCellRangeByName("C1").getString() Column = 2 Mode = 0 svc = createUnoService( "com.sun.star.sheet.FunctionAccess" ) arg = Array(SearchValue, oCellRange, Column, Mode) Value = svc.callFunction("VLOOKUP",arg) Print Value End Sub You can even use an array function, such as the FREQUENCY function. Naturally, you get an array back in that case. Code: Select all Expand view FreqArray = array(oCellRange, oCellRange2) oCellRange3.setData(svc.callFunction( "FREQUENCY", FreqArray )) ********************** #] 17Mar2014 To remove button etc from LibreWriter ??? I did by left-click -> delete, but there is an easier way! #] 18Mar2014 - I discovered this myself 1. right-click to select one of the pictures 2. Menu -> Edit -> select All then check to make sure "box" around only the images" 3. press delete key! *********** #] 01Mar2014 LibrCalc -> Tools -> Cell contents -> turn OFF recalc & autoinput! ************************************* ddmm2013 Links Open a spreadsheet document, choose Tools - Options - LibreOffice Calc - General Update Update links when loading Always Always updates links while loading a document. On request Updates links only on request while loading a document. Never Links are never updated while loading a document. Choose Tools - Detective Links to other sheets Simply search for the filename of the link causing the problems **************************************** #] 29Oct2013 Fixing ToDos sort so the range is no longer selected How does one move the cursor in a spreadsheet with a macro? - meddenly hard to find! **************************************** #] 28Oct2013 Archiving LibreOffice Calc macros from : http://listarchives.libreoffice.org/global/users/msg05200.html Use Tools → Macros → Organize Dialogs to open the LibreOffice Macro Organizer dialog. Another common way to open this dialog is to use Tools → Macros → Organize Macros → LibreOffice Basic to open the LibreOffice Macros dialog and then click the Organizer button. --> Howell : this shows filepaths of the LibreOffice Macros, but not of MY macros! same URL/note : However, when you open the LibreOffice Macro Organizer dialog, go to the Libraries page, and select any library other than Standard, you'll see both the location of that file and an Export button. Click Export and a little dialog pops up, giving you the choice to Export as BASIC library, which you can then move to another machine and import. You can also simply copy the directory from one machine to the other. --> Howell : Maybe the problem is that ALL my macros fall under "Standard", and should be moved "out" of that for every sheet! I can't see any means of creating a new Module outside of "Standard" in any workbook or in My Macors!!! http://www.linuxquestions.org/questions/linux-software-2/where-are-macros-stored-in-libre-office-calc-3-6-2-a-4175459438/ 04-24-2013, 10:36 AM #2 jdkaye Senior Member Salve, I'm now using LibreOffice v.4 and the macros appear to be stored here: Code: ~/.config/libreoffice/4/user/basic/Standard/Module1.xba In version 3 they were stored here: Code: ~/.libreoffice/3/user/basic/Standard/Module1.xba ciao, jdk --> Howell : BINGO! for "My Macros & Dialogs" - now where are the macros for each workbook? http://forum.openoffice.org/en/forum/viewtopic.php?f=74&t=5519 Howell - This URL seems to explain the code library setup **************************************** #] 24Oct2013 ThisComponent - but how to refer to a different file? --- from : http://ask.libreoffice.org/en/question/17209/how-to-change-thiscomponent/ answered May 9 '13 newOOo3 Sub Main Dim oDoc as Object, oText as Object, oInsertText as String oDoc = StarDesktop.loadComponentFromURL( "private:factory/swriter", "_blank", 0, Array()) oText = oDoc.getText() oInsrtText = "The quick brown fox jumped over the lazy dogs." oText.insertString(oText.getStart(), oInsrtText , false) End Sub --- --- from : http://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=44142&hilit=loadcomponentfromURL+hidden Charlie Young » Tue Sep 20, 2011 6:55 pm Sub LoadNewWorkbook Dim Doc As Object Dim NewWorkbook As Object Dim oDispatch As Object Dim Url As String Dim Args(1) As new com.sun.star.beans.PropertyValue Dim s As String Doc = ThisComponent Url = "file:///C:/MyNewWorkbook.ods" if FileExists(ConvertFromURL(URL)) then MsgBox(ConvertFromURL(URL) & " exists.") else MsgBox(ConvertFromURL(URL) & " does not exist.") endif Args(0).Name = "Hidden" Args(0).Value = False Args(1).Name = "MacroExecutionMode" Args(1).Value = 4 NewWorkbook = StarDesktop.loadComponentFromURL("private:factory/scalc", "_Blank", 0, Args) Args(0).Name = "FilterName" Args(0).Value = "calc8" Args(1).Name = "Overwrite" Args(1).value = True NewWorkbook.storeAsURL(Url,Args) if FileExists(ConvertFromURL(URL)) then MsgBox(ConvertFromURL(URL) & " exists.") else MsgBox(ConvertFromURL(URL) & " does not exist.") endif End Sub --- ********************************************** #] 24Oct2013 Sort doesn't work with real numbers! - MUST be INTEGER! ********************************************** #] 24Oct2013 Unlimited Sort Columns & Custom Sort Order Using Basic http://forum.openoffice.org/en/forum/viewtopic.php?f=75&t=44334 Postby alsOO » Mon Oct 03, 2011 1:33 am Two key additional sortingstatements to do >3 column macro sorts! : ' the next two statements enable use of a custom sort order defined in Data/Sorts,,,/Options/Custom Sort Order ' note: I only have ONE custom sort order defined. I did not test with multiple custom sort orders sortProperties(1).Name = "IsUserListEnabled" sortProperties(1).Value = TRUE ********************************************** #] 18Oct2013 How to assign values to variables with LibreCalc- setValue() in LibreOffice Calc macro doesn't work RESOLVED - cannot use functions for "side effects" like assignments. Must use Subs Nice advice below - but not comforting. I will cold reboot the entire computer, then try. After, if it still doesn't work, is the next step. - cold reboot didn't help. I'll take B Marcelly's advice - how do I make it explicit? use doc.sheet context? - try : oDoc.oSheet.cel_dateChanges.setValue(dateChanges) - oops oRow(), oCol() should be declared arrays! error window : "Basic runtime error. Argument is not optional" at line "oRow = iRow -1" ??? - had to remove "oDoc." from "oDoc.oSheet.cel_dateChanges.setValue(dateChanges)" - same problem with oSheet try : "Document(oDoc).Sheet(oSheet).Cell(cel_dateChanges).setValue(dateChanges)" -> same problem error window : "Basic runtime error. Argument is not optional" - remove "Document(oDoc)." same problem : ""Basic runtime error. Object variable not set." - back to "cel_dateChanges.setValue(dateChanges)" -> doesn't change cell values! - maybe "Functions" can't change cells (indirect effect)? - 18Oct2013 - now it runs? but only at whim it seems?!?!?? And can no longerbring up macro editor???? http://ooo-forums.apache.org/en/forum/viewtopic.php?f=20&t=45009 B Marcelly Thu Oct 27, 2011 9:13 am If you use your code as a function called from a cell formula, it won't work. What you are doing is not explicit. Keith Wild Thu Oct 27, 2011 3:44 am It works for me as well. Try declaring Cell as well and then stepping through the macro in the IDE with Doc, Sheet and Cell on watch to make sure you are picking up what you think you are. I remember I had a similar problem once and it was caused because I had lots of other components open at the same time (e.g. Basic Programming Guide, Andrew Pitonyak's books etc) and the macro thought that BPG was the active component and so that is what Doc became and so the macro didn't work properly. Bernard Marcelly solved this for me. Close any other components down and try again with just the spreadsheet open. ********************************************* #] 17Oct2013 LibreCalc - Show Changes This Show Changes looks like it might do what my "sper-macro system" did for my "Employee Information System" (EIS) at NRCan-MMSL Will haveto try it out some time! ********************************************** 07Sep2013 /home/bill/Contacts/0_ToDos_Month_Count.ods Update macro - very simple, but what a pain in the ass finding shit! (just to enter text into a cell!!!) PYTHON - As per my frustrated note below, LibreOffice Basic macros are BULLSHIT!!! (so is Visual Basic) Not so fast - huge learning curve, for what? ______________ http://stackoverflow.com/questions/11638170/what-language-do-i-need-to-write-macros-in-libre-office-calc Python's a great skill to learn - I use it for everything. It's the glue language for virtually every tool out there (you can even use it with .Net). Documentation for Python + LibreOffice is however a bit sketchy currently, although I don't have much experience with Calc. There is some work-in-progress documentation at http://documenthacker.wordpress.com (or soon www.documenthacker.com). It has examples for working with Writer, rather than Calc, but you might still find it useful. answered Mar 5 at 21:01 Jamie Boyle SHIT - still a jungle of convoluted detail... ******************************** ---------------------- ---------------------- RESOURCES for LibreOffice Basic macros : http://www.openoffice.org/documentation/manuals/userguide3/0312CG3-CalcMacros.pdf OpenOffice.org 3 - Chapter 12 Calc Macros, Automating repetitive tasks ------------------------ LibreOffice macros help CreateUnoService Function [Runtime] Instantiates a Uno service with the ProcessServiceManager. Syntax: oService = CreateUnoService( Uno service name ) For a list of available services, go to: http://api.libreoffice.org/docs/common/ref/com/sun/star/module-ix.html !?!?! URL didn't work, tried http://api.libreoffice.org/ This is a great reference. ---------------------- http://www.mousetech.com/blog/?page_id=134 Things I wish I’d Known Earlier OpenOffice Calc Macro CheatSheet Programming OpenOffice/Libre Office is not, alas, for the faint of heart. You can get utterly lost even trying. This cheat sheet is a short intro that I hope will help. ----------------------- from : http://www.open-of-course.org/courses/mod/resource/view.php?id=826 This starter tutorial isn't too helpful - not much is explained, and it does "tricky, useless" things. ---------------------- http://www.oooforum.org/forum/viewtopic.phtml?t=135341 can't record macros in LibreOffice Calc **************************************** List of Howell's LibreCalc macros I don't know exactly what this does, but obviously important! : /home/bill/.config/libreoffice/4/user/basic/dialog.xlc See /home/bill/.config/libreoffice/4/user/basic/script.xlc As of 10Nov2016 : LibreImpress macro /home/bill/Projects/Stalin supported Hitler/Title pages.odp export all slides as jpg **************************************** Tricks & Treats To look at a macro script, open it either by : in LibreCalc : Tools -> macros -> Organize macros -> LibreOffice basic >> This provides a listing. ALL of my macros should be under "My Macros" in Kwrite : /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/Howell_IEEE_prohibited_script.xba **************************************** Creating a new macro (using an example Howell_IEEE_prohibited_macLib) NOTE: I NEED macros to be in /home/bill/.config/libreoffice/4/user/basic That way my weekly backups automatically catch them!! 1. CLOSE all instances of LibreOffice 2. EDIT LibreCalc Basic script file : /home/bill/.config/libreoffice/4/user/basic/script.xlc -> copy an existing line with a maLib, eg : copy to 3. COPY an existing macro directory and rename to new macro, eg. : copy /home/bill/.config/libreoffice/4/user/basic/Howell_ToDos_macLib to /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib 4. EDIT /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/dialog.xlb change line : to line : 5. EDIT /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/script.xlb change lines : to lines : 6. CHANGE filename of script file : from : /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/Howell_ToDos_script.xba to : /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/Howell_IEEE_prohibited_script.xba 7. EDIT & CONVERT the old macro to the new macro (old code can be handy as a template) :LibreOffice /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/Howell_IEEE_prohibited_script.xba 8. No need to backup all macro directories - this is done by regular weekly and monthly backups # enddoc