Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

to write cells with thier number in macro

Hi!

I write a macro in libre Office and I have a problem. I want to use it with for loop and because of this I have to change my cells id like A1, A2, B1, B2 to (1,1),(1,2),(2,1),(2,2)

Can you help me how can I write it?

my code is:

sub Main rem ---------------------------------------------------------------------- rem define variables dim document as object dim dispatcher as object rem ---------------------------------------------------------------------- rem get access to the document document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ---------------------------------------------------------------------- dim args1(0) as new com.sun.star.beans.PropertyValue args1(0).Name = "Nr" args1(0).Value = 6

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())

rem ---------------------------------------------------------------------- dim args2(0) as new com.sun.star.beans.PropertyValue args1(0).Name = "ToPoint" args1(0).Value = "$E$12"

GetCellByPosition(4,12)

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ---------------------------------------------------------------------- dim args3(0) as new com.sun.star.beans.PropertyValue args3(0).Name = "Nr" args3(0).Value = 7

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args3())

rem ---------------------------------------------------------------------- dim args4(0) as new com.sun.star.beans.PropertyValue args4(0).Name = "ToPoint" args4(0).Value = "$G$17"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

End Sub

click to hide/show revision 2
None

updated 2018-11-21 19:50:09 +0100

Lupp gravatar image

to write cells with thier number in macro

Hi!

I write a macro in libre Office and I have a problem. I want to use it with for loop and because of this I have to change my cells id like A1, A2, B1, B2 to (1,1),(1,2),(2,1),(2,2)

Can you help me how can I write it?

my code is:

is:

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

createUnoService("com.sun.star.frame.DispatchHelper") rem ---------------------------------------------------------------------- dim args1(0) as new com.sun.star.beans.PropertyValue args1(0).Name = "Nr" args1(0).Value = 6

6 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())

args1()) rem ---------------------------------------------------------------------- dim args2(0) as new com.sun.star.beans.PropertyValue args1(0).Name = "ToPoint" args1(0).Value = "$E$12"

GetCellByPosition(4,12)

"$E$12" GetCellByPosition(4,12) dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

args1()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

Array()) rem ---------------------------------------------------------------------- dim args3(0) as new com.sun.star.beans.PropertyValue args3(0).Name = "Nr" args3(0).Value = 7

7 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args3())

args3()) rem ---------------------------------------------------------------------- dim args4(0) as new com.sun.star.beans.PropertyValue args4(0).Name = "ToPoint" args4(0).Value = "$G$17"

"$G$17" dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

args4()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

Array()) End SubSub

(Slightly edited for better readability by @Lupp.)

to write cells with thier number in macro

Hi!

I write a macro in libre Office and I have a problem. I want to use it with for loop and because of this I have to change my cells id like from A1, A2, B1, B2 to (1,1),(1,2),(2,1),(2,2)(1,1),(1,2),(2,1),(2,2). Because thanks to numbers I can do "for loop"

This macro is "recorded macro" because I couldn't write by myself.

I want to make in this macro to copy paste. I have data in a sheet and I want to copy this data to other sheet. I only want to copy data. There isn't formulas and other things.

The macro that is under the text, work good but I don't want to use it like $E$12. I want to get value "E" like (5,12)

I don't have to write my code like this. I can also write with another method but I don't know how to write it.

If I can get a sample of a copy-paste macro, I can explain it and I can solve my problem by looking it.

Can you help me how can I write about it?

my code is:

sub Main
rem ----------------------------------------------------------------------
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = 6
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$E$12"

GetCellByPosition(4,12)

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Nr"
args3(0).Value = 7
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args3())
rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$G$17"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
End Sub

(Slightly edited for better readability by @Lupp.)