Calc Macro VBA Transpose

[ibnulhusainan]

Hello, all,
can anyone help me?

i need a vba macro script to transpose rows to columns.

some time ago I got a vba macro script here to transpose data from row to column.

then the script disappears after I reinstall my laptop.

I looked for it again but I can’t find it anymore.

I hope someone can help me.

Here is an example of the transpose I mean:

A
B
C
D
E

Becomes :

A | B | C | D | E

[Lupp]

A few questions in return:
In what way do you want to tell the “macro” where to get the original (part of a) column and where to output the row?
Do you know the transposition option of Paste Special...? In what way doesn’t it meet your needs?

[ibnulhusainan]

yes sir, I know that I can use paste special, but I work with hundreds of documents and thousands of data that I need to transpose, because i will to store it into a database

[mauricio]

Options 1:

cc = ThisComponent.CurrentController
sheet = cc.ActiveSheet
frame = cc.Frame
dh = createUnoService("com.sun.star.frame.DispatchHelper")

source = sheet.getCellRangeByname("A1:B5")
cc.select(source)
dh.executeDispatch(frame, ".uno:Copy", "", 0, Array())

target = sheet.getCellRangeByname("A7")
cc.select(target)

dim args(5) as new com.sun.star.beans.PropertyValue
args(0).Name = "Flags"
args(0).Value = "SVD"
args(1).Name = "FormulaCommand"
args(1).Value = 0
args(2).Name = "SkipEmptyCells"
args(2).Value = false
args(3).Name = "Transpose"
args(3).Value = true
args(4).Name = "AsLink"
args(4).Value = false
args(5).Name = "MoveMode"
args(5).Value = 4
dh.executeDispatch(frame, ".uno:InsertContents", "", 0, args())

Option 2:

fa = createUnoService("com.sun.star.sheet.FunctionAccess")  
sheet = ThisComponent.CurrentController.ActiveSheet
source = sheet.getCellRangeByname("A1:B5")
ra = source.RangeAddress
data = fa.callFunction("TRANSPOSE", Array(source))
col = ra.StartColumn
row = ra.EndRow + 2
target = sheet.getCellRangeByPosition(col, row, col + UBound(data(0)), row + UBound(data))
target.DataArray = data

Example:

[mauricio]

If someone use Python like me and my library easymacro then

import easymacro as app

def main():
    source = app.get_cell('A1').current_region
    source.transpose()
    return

[ibnulhusainan]

Thank you very much

I didn’t think that I would get an answer that fast in this forum.

Thank you very much.

and the code works very well.

but how to make it work automatically for a lot of data?

for example I have 200 rows of data and I want to transpose it every 10 rows so that it will be 20 rows with 5 columns for each row.

can it be like that?

[mauricio]

It’s easy… but, I’m very lazy. :). Please, attach an file example and I help you with code.

[ibnulhusainan]

this is the example file:

question.xls

i need your help, please

[mauricio]

Please, attach in this issue, I’m sorry, all Google it’s block in my system.