[Solved] VB macro code rewritten in Python macro

[Prasanjith]

Hi Team,

I want the following VB macro (excel) code rewritten in Python macro for libre office calc.

Sub TransposeAreas()
Dim aArea As Range
Dim nr As Long

Application.ScreenUpdating = False
nr = 1
For Each aArea In Range("A1", Range("A" & Rows.Count).End(xlUp)) _
.SpecialCells(xlCellTypeConstants).Areas
aArea.Copy
Cells(nr, 3).PasteSpecial Transpose:=True
nr = nr + 1
Next aArea
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Can somebody help me on this?

Thank you
Prasanjith

[Zizi64]

You must study tha API functions and procedures of the AOO/LO. You can call them from StarBasic, from Python, from java...

API = Application Programming Interface..

The MS Office VBA is integrated with its own "API" functions, but the API of the AOO/LO is a standalone set, and it is independent from the built-in programming languages. You can call them fron many programming IDE on many operating system.

[JeJe]

Here's a page to get you started.

https://wiki.openoffice.org/wiki/Python-Calc

If you make an effort to write it yourself, people may be more willing to help with where you get stuck than to write the whole thing for you...

If you do a search in this forum or a search engine for the bits you want... eg searching for Openoffice screen updating gives this thread which tells you that the near equivalent of screen updating in VBA is thiscomponent.lockcontrollers...

viewtopic.php?f=9&t=84189

(results are most likely to be in OOBasic as that's what most people use)

[Prasanjith]

Understood....Thanks guys

Prasanjith

[Villeroy]

Col2Rows.ods

(18.39 KiB) Downloaded 269 times

[Prasanjith]

Col2Rows.ods

Hi Villeroy,

Thank you very much. its working

Prasanjith

PS: to any one who is curious to know whats going on...The above attached document contains a script which when executed, data in column A will be transpose to rows(starting from the 1st cell in column E). and when ever there is a blank cell in column A ....it will continue to transpose on the next row in column E. This is very useful when preparing data for mail merge.

But to use the document ....

1) first of all you need to enable macro in calc (tools>options>security>macro security>> set it to low)

2) then you need to install LibreOffice-script-provider-python. If not .....you will get an error (the scripting language python is not supported)

on Ubuntu you can install via the terminal with the following commandments

a) Update the package index:

# sudo apt-get update

b) Install libreoffice-script-provider-python deb package:

# sudo apt-get install libreoffice-script-provider-python

Villeroy thank you once again
Kudos...

[Villeroy]

1) first of all you need to enable macro in calc (tools>options>security>macro security>> set it to low)

No, don't do that. Set it to the highest level and add some trusted directories where document embedded macros are allowed to be executed ("Downloads" would be a bad idea, but "Documents" may fit).

2) then you need to install LibreOffice-script-provider-python. If not .....you will get an error (the scripting language python is not supported)

Yes, Ubuntu does not install the Python bridge. If you can see menu:Tools>Macros>Organize>Python then you don't have to install anything. Possibly you also want to install libreoffice-base because otherwise you can not create any serial letters, not even from spreadsheets.

[Prasanjith]

1) first of all you need to enable macro in calc (tools>options>security>macro security>> set it to low)

No, don't do that. Set it to the highest level and add some trusted directories where document embedded macros are allowed to be executed ("Downloads" would be a bad idea, but "Documents" may fit).

2) then you need to install LibreOffice-script-provider-python. If not .....you will get an error (the scripting language python is not supported)

Yes, Ubuntu does not install the Python bridge. If you can see menu:Tools>Macros>Organize>Python then you don't have to install anything. Possibly you also want to install libreoffice-base because otherwise you can not create any serial letters, not even from spreadsheets.

100% agreed

Thank you
Prasanjith

[karolus]

hallo

@villeroy: transposing DataArray is simple by the pattern

Code: Select all

transposed = tuple( zip(*some_dataarray))

so I come up with:

Code: Select all

from com.sun.star.sheet.CellFlags import STRING, VALUE, DATETIME

def Main(*a):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.Sheets.getByIndex(0)
    oCol = sheet.Columns.getByIndex(0)
    oRanges = oCol.queryContentCells( STRING + VALUE + DATETIME )
    
    for i, singlerange in enumerate(oRanges):
        data = tuple(zip(*singlerange.DataArray))
        sheet.getCellRangeByPosition(4, i, len(data[0])+3, i).setDataArray( data )

 

[Villeroy]

Yes, the unpacking operator makes the difference.
I should read more books on Python or read more code from real programmers at least.
Thanks for the snippet.