to write cells with their number in macro

[libremacrouser]

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 from A1, A2, B1, B2 to (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 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"


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.)

[Lupp]

You won’t get happy with recorded macros for Copy/Paste tasks (or for others).
The appropriate means to copy a cell range to elsewhere is the copyRange method as decribed here.
Your GetCellByPosition(4,12) cannot work this way because it must be called as a method of the sheet containing the cell. …

[Lupp]

“… I don’t want to use it like $E$12. I want to get value “E” like (5,12)”
-1- E12 has (Column, Row) = (4, 11) in Calc. The indices start with 0 for the API.
-2- Programming is not exclusively about what somebody “wants” (likes?), but mainla about what is to achieve, and how things can be done in an efficient way.

You find an example attached to my answer. The used method copies everything as also the .uno:Paste command does.

[libremacrouser]

Thanks a lot. It helped me really so much. Now I can work on it.

[Lupp]

f you actually need something like GetCellByPosition(4,12), you must first get access to the sheet containingf the respective cell. Getting sheets as objects is done by methods of the .Sheets property of the spreadsheet document.
mySheet = ThisComponent.Sheets.GetByName("myCollection") is an example.
The cell E13 of that sheet you can get then either by
cellE13 = mySheet.GetCellByPosition(4, 12) or by
cellE13 = mySheet.GetCellRangeByName("E13") …

Try to avoid “macros”. If you feel sure you will need some, start reading the guide and the famous texts by Andrew Pitonyak.
See this link and this one to start off.

==Edit1 2018-11-22 14:55 CET===
The announced example.

[libremacrouser]

Hi Lupp!

Thanks for your reply. It is good but I need help to write your code that are

cellE13 = mySheet.GetCellByPosition(4, 12)

mySheet = ThisComponent.Sheets.GetByName("myCollection") into my code.

into my code. When I wrote them into mine, they doesn’t work.

I hope I could explain my problem in a detail way. If I repeat it, I want to copy many cells and I want to past all of them in the other sheet and different cells. I can’t do them without macros because there are so many data and they are in different cells.

I need to solve my problem with macro and I have decided to use for loop to make automatically. But I can’t do for loop because of “E” in E12 cell. And then I searched and I saw that I can use number instead of “E” but I couldn’t run it. I get error.

I again tried your code and I again get error. My error is here

Can you give me an advice how can do it? Or you can implement your code into my code?

Thanks for your help.

Best regard

[SM_Riga]

Hi @libremacrouser. Do not use Answer section to add coments. You shall use comments section or edit your own question. As @Lupp mentioned, recorded Macros are of no use in the most cases, it is preferable to use methods and properties of a desired object to perform the task. It is most likely “myCollection” name is incorrect or misspelled or you are trying to access sheet with name “myCollection” when other calc document is active.

[SM_Riga]

@Lupp gave you two methods to get cell object or by it’s name, or by it’s index/postition. Then you can use CellAddress property to get cell position indexes (with sheet index included). So you can get cell object by name and then get its position indexes.

[Lupp]

Abandon tampering with “recorded macros” as long as you don’t know anything about programming for LibreOffice.
The concept of dispatcher commands (“slots”) is “very special”. A recorded macro must either be used “as is” or you only can rework it for actual usability based on actual knowledge of the LibO API (Application Programming Interface). Start studies or abandon macros. I would not expect contributors here to give a one-person-course on programming again and again.

[libremacrouser]

@SM_Riga it is still difficult for me, I work hard on it but I am still couldn’t solve this problem. I think I couldn’t understand what will I do.

[libremacrouser]

@Lupp I tried to use recorded macro because I couldn’t write code what I want. My main goal to make copy paste. This way seemed the great way to make it. I don’t know how can I use things that you said me. If you can send me an example, I can explain it and understand easily. I looked for an example to look and learn it but I couldn’t it. In addition you already know I want to write cells with number like (4,12) because I want to write for loop for second step.

[SM_Riga]

To save yours and others time - please edit your question and describe how exactly you want cells to be copied. What is the copy/paste pattern? Do source cells contain formulas and if so, shall formulas also be copied or values only?

[libremacrouser]

@SM_Riga I edited my question. I hope it is easy understandable write now.