Author Topic: Rexx script to enable OpenOffice Macro support!  (Read 4778 times)

Jan-Erik Lärka

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 275
  • Karma: +5/-0
    • View Profile
Rexx script to enable OpenOffice Macro support!
« on: April 16, 2014, 11:28:30 pm »
Hello,

I've used OpenOffice.org for quite some time and consider myself as a power user when it come to designing spreadsheets, so the disabled support for macros in OpenOffice.org has been annoying to say the least

I today found the relevant post how to enable marco security and now written a rexx script and tested it successfully. You can test and use it as well!

You need a macro in your spreadsheet to see that it actually work, so follow the steps below to add the relevant but very simple code to your spreadsheet

Regards,
//Jan-Erik

OpenOffice.org Calc
Menu:Tools -> Macros -> Organize macros -> OpenOffice.org Basic...
Window:OpenOffice.org Basic Macros
Area:Macro from
Treeview:[-]<Select your spreadsheet>
Mark:[-] Standard
Button:[  New  ]
Window:New Module
Field:Name:
Keyboard:GetSheetName
Button:[  OK  ]
Window:<Your spreadsheets name> - OpenOffice.org Basic
Cut & Paste:
Code: [Select]
Function GetSheetName( i )
On Error Goto trap
GetSheetName = ThisComponent.Sheets(i-1).Name
Exit Function
trap:
GetSheetName = erl()
End Function
Menu:File -> Save
Menu:File -> Close
Spreadsheet:Select a cell
Keyboard:=getsheetname(3)
Keyboard:Enter
As you press enter the cell will show #VALUE
Save the spreadsheet and run the script below and set it to 1
Higher value mean higher security but also more restrictions
Reopen the spreadsheet
Allow it to run the macro
You should now see the name of the third tab in your spreadsheet in the cell.
====

You will see a window that ask if you want to enable macros if you set the security level to 1.
You can rerun this script if you like to increase or decrease the security level.
Code: [Select]
/*
 * Filename: OOorgMacro.cmd
 *   Author: JAN-ERIK
 *  Created: Wed Apr 16 2014
 *  Purpose: Add macro security tag to configuration file to be able to run Macros in the OS/2 and eComstaiton version of OpenOffice.org
 *  Changes:
 */

/* Load RexxUtil Library */
IF RxFuncQuery('SysLoadFuncs') THEN
DO
    CALL RxFuncAdd 'SysLoadFuncs', 'RexxUtil', 'SysLoadFuncs'
    CALL SysLoadFuncs
END
CALL SysCls
CALL LINEOUT 'STDERR', 'OpenOffice.org for OS/2 and eComstation'
CALL LINEOUT 'STDERR', "doesn't contain a utility to allow you to enable macros"
CALL LINEOUT 'STDERR', ''
CALL LINEOUT 'STDERR', 'This script will attempt to enable macro security for you.'
CALL LINEOUT 'STDERR', ''
CALL LINEOUT 'STDERR', 'Please do note that you should be aware that macros may'
CALL LINEOUT 'STDERR', "be harmful so don't open files from unknown sources or"
CALL LINEOUT 'STDERR', 'files that may contain macros.'
CALL LINEOUT 'STDERR', ''
CALL LINEOUT 'STDERR', "Don't blame the author of this script for any harm caused!"
CALL LINEOUT 'STDERR', ''
CALL CHAROUT 'STDERR', 'Do you want to continue (yes/No)? (N by default) '
PARSE PULL answer
IF TRANSLATE( LEFT( answer, 1 ) ) <> 'Y' THEN RETURN 0
level = ' '
DO WHILE \DATATYPE( level, 'W' )
    CALL LINEOUT 'STDERR', ''
    CALL LINEOUT 'STDERR', 'Valid values are: 0 - the lowest; 1 - the lower; 2 - default; 3 - the highest'
    CALL LINEOUT 'STDERR', ''
    CALL CHAROUT 'STDERR', 'Specify the security level to use (4 and above to abort): '
    PARSE PULL level
    IF level = '' THEN level = 2
    IF level < 0 THEN level = ' '
    IF level > 3 THEN RETURN level
END

newln = D2C(10)
add.1 = '<node oor:name="Security">'
add.2 = '  <node oor:name="Scripting">'
add.3 = '   <prop oor:name="MacroSecurityLevel" oor:type="xs:int">'
add.4 = '    <value>'||level||'</value>'
add.5 = '   </prop>'
add.6 = '  </node>'
add.7 = '</node>'
add.8 = '</oor:component-data>'
add.0 = 8

CALL CHAROUT 'STDERR', '.'
home_dir = VALUE( 'HOME',, 'OS2ENVIRONMENT' )

IF SysFileTree( home_dir||'\*common.xcu', 'file.', 'SFO' ) = 0 THEN
DO i = 1 TO file.0
    f_size = STREAM( file.i, 'C', 'QUERY SIZE' )
    IF f_size = 0 THEN ITERATE
    input = CHARIN( file.i, 1, f_size )
    CALL STREAM file.i, 'C', 'CLOSE'
    PARSE VALUE input WITH pre(add.8).
    PARSE VALUE pre WITH pre(add.1).
    DO j = 1 TO add.0
        CALL CHAROUT 'STDERR', '.'
        pre = pre||add.j
        IF j < add.0 THEN
            pre = pre||newln
    END
    IF SysFileDelete( file.i ) = 0 THEN
        CALL CHAROUT file.i, pre
    CALL CHAROUT 'STDERR', '.'
END
« Last Edit: April 17, 2014, 03:00:06 pm by Jan-Erik Lärka »