[Solved] Getting code from a cell for copy paste

[sokolowitzky]

Hello there,
I have various spreadsheets, in which copy paste areas are different than each other.
So I need some kind of copy paste maker. I will write name of the file and the area to be copied and the file to be written and area to be pasted.
Like C!= ...\xxx.ods D1= Sheet1.A3:E3 and F1: ...\xxy.ods G1=Sheet2.A6:E5
So this list of files will be processed in loop. But to be able to define a Copy paste area the way that I could find is
at first defining the row in the processor sheet with a loop like this
"for i to 100" CELL1=thiscomponent.sheets(0).getcellbyposition(10, i) CELL2=thiscomponent.sheets(0).getcellbyposition(12, i)

On the cell K1 I've input this: DOC2.Sheets.getByName("S1").getcellrangebyname("a3:c3")
and on the cell L1, i've written this. DOC3.Sheets.getByName("S1").getcellrangebyname("a4:c4")

How can I make a copy paste with the basic codes that I've written into spreadsheet?

CELL1.setdataarray (CELL2.getdataarray())
It seems like what I'm asking is a bit confusing. All I need to know is if I can turn a cell string into a macro code.
If I write this =thiscomponent.sheets.getbyname("E2").getcellrangebyname("F1") into a cell, can I use it as an addtess to another cell?

Code: Select all

sub trydis
Dim thing as object
Dim thang as object
Dim ranger1 as string
Dim ranger2 as string
ranger1=thiscomponent.sheets.getbyname("E2").getcellrangebyname("F1").string
ranger2=thiscomponent.sheets.getbyname("E2").getcellrangebyname("F6").string
thing.string = ranger1
thang.string = ranger2
thang.setFormulaArray (thing.getFormulaArray())

end sub

)

[Zizi64]

i think, You must pass only the arguments from a cell to a macro, but not a full line of the commands. You can pass the Document URL, the Sheet name, the name of the cell Range to copy "From" or "To". You must use more than one cell for it, or you must manage the passed strings like "Sheet1.A1:B2" by the macro.

[sokolowitzky]

Since I am an amateur, I could not use exact terms that I need, then.
Here I want to rephrase it one more time.
Can I turn a string into an object?
I want to copy from the area defined in cell, with help of a macro.
Let's say in c1 it says "a2:j592".
Can I somehow define the cell c1.string as object?
Anyone with an idea to this?

[Lupp]

Do you actually have a sheet named "E2"? Strange. Taken for an example the more strange. Everybody would suppose "E2" to be a cell's address.

Assuming a sheet named "Sheet1" has the cell C1 containing a RangeName (in different context called a range address), say "G3:K99". Everything contained in the SpreadsheetDocument accessible as ThisComponent.
To get the range described like above as an object you can use:

Code: Select all

theSheet = ThisComponent.Sheets.GetByName("Sheet1")
cellC1   = theSheet.GetCellRangeByName("C1")  REM or cellC1 = theSheet.GetCellByPosition(2, 0)
rgName   = cellC1.String
theRange = theSheet.GetCellRangeByName(rgName) 

The range theRange now has a property .RangeAddress (a structure of 5 numbers). To copy contents from this range as the source range to a target you need in addition the .CellAddress (a structure of 3 numbers) of the cell where the targetRange is starting. The way to get that cell as an object is the same as used for cellC1.
See https://api.libreoffice.org/docs/idl/re ... 62731cbca7 .

[sokolowitzky]

thank you very much. this is exactly what I was looking for. bytheway, yes, I use E1 as sheet name because I try to use shortest way possible to name the sheets.