[Solved] VB macro code rewritten in Python macro

Creating a macro - Writing a Script - Using the API

[Solved] VB macro code rewritten in Python macro

Postby Prasanjith » Sat Apr 27, 2019 9:58 am

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
Last edited by Prasanjith on Sat Apr 27, 2019 12:23 pm, edited 1 time in total.
libreoffice 6.2.2.2 on Unbuntu 19.04
Prasanjith
 
Posts: 11
Joined: Thu Apr 25, 2019 8:33 pm

Re: VB macro (excel) code rewritten in Python macro for libr

Postby Zizi64 » Sat Apr 27, 2019 10:11 am

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.
Last edited by Zizi64 on Sat Apr 27, 2019 10:27 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8225
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VB macro (excel) code rewritten in Python macro for libr

Postby JeJe » Sat Apr 27, 2019 10:17 am

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)
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 575
Joined: Wed Mar 09, 2016 2:40 pm

Re: VB macro (excel) code rewritten in Python macro for libr

Postby Prasanjith » Sat Apr 27, 2019 12:20 pm

Understood....Thanks guys

Prasanjith
libreoffice 6.2.2.2 on Unbuntu 19.04
Prasanjith
 
Posts: 11
Joined: Thu Apr 25, 2019 8:33 pm

Re: [Solved] VB macro code rewritten in Python macro

Postby Villeroy » Sat Apr 27, 2019 2:44 pm

Col2Rows.ods
(18.39 KiB) Downloaded 95 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] VB macro code rewritten in Python macro

Postby Prasanjith » Sat Apr 27, 2019 6:41 pm

Villeroy wrote:
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...
libreoffice 6.2.2.2 on Unbuntu 19.04
Prasanjith
 
Posts: 11
Joined: Thu Apr 25, 2019 8:33 pm

Re: [Solved] VB macro code rewritten in Python macro

Postby Villeroy » Sat Apr 27, 2019 7:56 pm

Prasanjith wrote: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).
Prasanjith wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] VB macro code rewritten in Python macro

Postby Prasanjith » Sun Apr 28, 2019 6:00 am

Villeroy wrote:
Prasanjith wrote: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).
Prasanjith wrote: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
libreoffice 6.2.2.2 on Unbuntu 19.04
Prasanjith
 
Posts: 11
Joined: Thu Apr 25, 2019 8:33 pm

Re: [Solved] VB macro code rewritten in Python macro

Postby karolus » Sun May 05, 2019 4:42 pm

hallo

@villeroy: transposing DataArray is simple by the pattern

Code: Select all   Expand viewCollapse view
transposed = tuple( zip(*some_dataarray))


so I come up with:

Code: Select all   Expand viewCollapse view
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 )

 
Last edited by karolus on Sun May 05, 2019 6:58 pm, edited 1 time in total.
AOO4, Libreoffice - 5.1 … 5.3.2.2 on Linux Mint17
User avatar
karolus
Volunteer
 
Posts: 852
Joined: Sat Jul 02, 2011 9:47 am

Re: [Solved] VB macro code rewritten in Python macro

Postby Villeroy » Sun May 05, 2019 5:43 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests