Inserting a Row and Copy Functions Macro

[nsoon]

I'm trying to create a macro linked to a Form Control button to insert a new row above whatever cell is currently active. After the row has been inserted it needs to copy the conditional formatting and functions of the previous row without duplicating the data.

I've perused many posts, the closest of which was this one: http://user.services.openoffice.org/en/ ... +row+macro
The difference between what I need and what the aforementioned post accomplishes is that the active cell will not be at the bottom of the page.

I've tried recording macros and even browsed through a few tutorials (Hello World, Databases, etc), but none of what I've been able to find pertains to a row that is relative to the currently selected cell. I don't know that it makes a huge difference but a lot of the functions I need are in hidden columns.

It looks like this

Date Category Qty
xx/xx Screws 4 (functions in hidden columns)
xx/xx Eyebolts 6 (functions in hidden columns)
xx/xx Clamps 2 (functions in hidden columns)
xx/xx Eyebolts 9 (functions in hidden columns)
<- Need to insert here ->

Total Screws 4
Total Eyebolts 15
Total Clamps 2
Total Washers 0




Thanks in advance for any thoughts!

[Villeroy]

[Calc, Python] Expand/shrink list ranges

[nsoon]

Is this possible with a non-python macro embedded within the spreadsheet?

[karolus]

Hi

Is this possible with a non-python macro embedded within the spreadsheet?

May be, it is - but why not with embedded python-script ?

calc_with_python_embeded.ods

(10.78 KiB) Downloaded 378 times

Karo

[rudolfo]

Hi

Is this possible with a non-python macro embedded within the spreadsheet?

May be, it is - but why not with embedded python-script ?

Karo

Maybe because embedding a python script into a OpenOffice Spreadsheet isn't easy to accomplish, neither is it well documented. I see that the .py file is included in the zip-archive of the Spreadsheet that you uploaded and there is an entry in the manifest.xml for it. But how did you get there?

Code: Select all

 <manifest:file-entry manifest:media-type="" manifest:full-path="Scripts/python/embeded_python_lib.py"/>

And in deed, if you launch "Organize Python macros" the "embedded_python_lib" is right there and its two methods can be executed. Neat!

[karolus]

Hi

Hi

Is this possible with a non-python macro embedded within the spreadsheet?

May be, it is - but why not with embedded python-script ?

Karo

Maybe because embedding a python script into a OpenOffice Spreadsheet isn't easy to accomplish, neither is it well documented. I see that the .py file is included in the zip-archive of the Spreadsheet that you uploaded and there is an entry in the manifest.xml for it. But how did you get there?

For my convinience, actually i do it with Help of 'ModifiedScriptProvider.oxt' and 'EditorKicker...oxt' from Hanya:
http://hermione.s41.xrea.com/pukiwiki/i ... bbs2%2F194
But its also possible to do it the hard way on a closed Document:

• > unzip xyz.ods -d newfolder
  > cd newfolder
  > mkdir Scripts
  > mkdir Scripts/python
  > cp /path/to/your_script.py Scripts/python

Open 'manifest.xml with your favourite Editor and put in 3 lines like:

<manifest:file-entry manifest:media-type="" manifest:full-path="Scripts/python/your_script.py"/>
<manifest:file-entry manifest:media-type="application/binary" manifest:full-path="Scripts/python/"/>
<manifest:file-entry manifest:media-type="application/binary" manifest:full-path="Scripts/"/>

at the end put it back to .ods -file with:

• > ls | zip -r@ new_calc_python.ods

Karo

[kingfisher]

The way I add a row to tables involves defining the tables as database ranges. This is one example:

Code: Select all

'Define the codes table.  'Insert a row at the end of the Codes table.
' EXAMPLE ONLY
oDBase = vDBases.getByName( "_TableCodes" )
oCells = oDBase.ReferredCells : oDocSheet = oCells.Spreadsheet
lFoot = oCells.RangeAddress.EndRow + 1 : oDocSheet.Rows.insertByIndex( lFoot, 1 )
oExtDBase = oDBase : lNum = 1 : Call extDBRows : oDoc.calculateAll()
oDBase = oExtDBase.ReferredCells : lLeft = lDBLeft : lFoot = lDBFoot

'Insert the new code in the last cell of the table.
oCell = oDocSheet.getCellByPosition( lLeft, lFoot ) : oCell.setString( UCase( sCode ) )
If oDoc.StyleFamilies.CellStyles.hasByName( "_Titles" ) Then : oCell.CellStyle = "_Titles" : End If

'Sort the table in alphabetical order.
Dim aSortField( 0 ) as new com.sun.star.util.SortField, _
	aSortVal( 1 ) as new com.sun.star.beans.PropertyValue

aSortField( 0 ).Field = 0 : aSortField( 0 ).SortAscending = TRUE
aSortVal( 0 ).Name = "SortFields" : aSortVal( 0 ).Value = aSortField()
aSortVal( 1 ).Name = "ContainsHeader" : aSortVal( 1 ).Value = true

oDBase.Sort( aSortVal() )

[Villeroy]

Thank you karolus for this demo how to wrap script code into documents.

However, my pair of Python macros is designed to extend the capability of the whole Calc component. It does not depend on the properties of a certain Calc document. There is no need to adjust the source code to specific ranges, styles nor other attributes. Therefore I do not understand why it should be restricted to a particular document.

It operates on the current range selection (in case of no range selection that is the active cell) taking care that all references expand to the resized range as you would expect from a growing and shrinking list range, regardless if that is a database range, named range, chart range and no matter which formula expressions refer to the involved cells. You can turn off the obscure and sometimes annoying "expand reference" option while using the advantages of that option when working with a list. I use this code since years and never got any error message nor did I lose any of my references nor formulas even when the formulas are in hidden columns. Unlike Basic code it does not fail when you call it many times while the first calls are not yet finished.
This pair of macros could be distributed as an extension. But why should I struggle with extension packaging when dropping a script into the user profile or application profile is so easy?

[karolus]

Hallo

Thank you karolus for this demo how to wrap script code into documents.

However, my pair of Python macros is designed to extend the capability of the whole Calc component. It does not depend on the properties of a certain Calc document. There is no need to adjust the source code to specific ranges, styles nor other attributes.

Yes, i know, my post is only a reply to the Question:

Is this possible with a non-python macro embedded within the spreadsheet?

....
This pair of macros could be distributed as an extension. But why should I struggle with extension packaging when dropping a script into the user profile or application profile is so easy?

+1 ... but think about some users wich are in trouble to find the 'user profile' ...

Karo

[Villeroy]

Hallo

....
This pair of macros could be distributed as an extension. But why should I struggle with extension packaging when dropping a script into the user profile or application profile is so easy?

+1 ... but think about some users wich are in trouble to find the 'user profile' ...

Karo

Well, I try my best to ignore this category of non-users.