Getting macro code from a cell and using it for copy paste

Creating a macro - Writing a Script - Using the API

Getting macro code from a cell and using it for copy paste

Postby sokolowitzky » Fri Sep 14, 2018 4:20 am

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   Expand viewCollapse view
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
)
Last edited by sokolowitzky on Fri Sep 14, 2018 7:10 am, edited 1 time in total.
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 51
Joined: Mon Sep 15, 2014 7:34 pm

Re: Getting macro code from a cell and using it for copy pas

Postby Zizi64 » Fri Sep 14, 2018 7:07 am

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.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
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: 7360
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Getting macro code from a cell and using it for copy pas

Postby sokolowitzky » Mon Nov 12, 2018 7:18 pm

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?
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 51
Joined: Mon Sep 15, 2014 7:34 pm

Re: Getting macro code from a cell and using it for copy pas

Postby Lupp » Mon Nov 12, 2018 8:47 pm

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   Expand viewCollapse view
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 .
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2096
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Getting macro code from a cell and using it for copy pas

Postby sokolowitzky » Thu Nov 15, 2018 4:46 am

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.
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 51
Joined: Mon Sep 15, 2014 7:34 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: OldStd and 8 guests