Page 1 of 1
[Solved] Add text from one cell to another using a macro
Posted: Thu Jan 24, 2019 11:21 pm
by coolguyhb
I am trying to take text from a designated cell and add it to a designated cell that already contains text without deleting the contents in the cell.
Example:
Sheet3.E15 already contains "Tasks to complete:"
I need to copy the contents from Sheet2.Q2 and add it into Sheet3.E15 underneath "Tasks to complete:". I will be assigning this macro to a button so that the contents of Sheet2.Q2 can be added to Sheet3.E15 as many times as needed with each copied entry below the previous.
Re: Add text from one cell to another using a macro
Posted: Fri Jan 25, 2019 1:31 am
by JeJe
cell.string = cell.string & othercell.string
Re: Add text from one cell to another using a macro
Posted: Fri Jan 25, 2019 5:29 pm
by coolguyhb
JeJe wrote:cell.string = cell.string & othercell.string
I appreciate the reply JeJe! I am unsure how to use this in a macro though... I am still rather new to writing macros. Can you provide any other helpful information?
Re: Add text from one cell to another using a macro
Posted: Fri Jan 25, 2019 7:50 pm
by JeJe
replace cell or othercell with something like:
thiscomponent.sheets(0).getCellByPosition(1,2)
for sheet 0, cell co-ordinates 1 across, 2 down.
Re: Add text from one cell to another using a macro
Posted: Fri Jan 25, 2019 7:54 pm
by coolguyhb
Resolved this using a macro.
Created a new cell and used CONCATENATE to string contents together. =CONCATENATE(Sheet3.E15,CHAR(10),Sheet3.B11)
Then used a macro that copied that cell and pasted it back to Sheet3.E15 using Paste Special, Transpose
Code: Select all
sub Add_to_Note
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 = 3
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$Q$2"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Nr"
args4(0).Value = 1
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args4())
rem ----------------------------------------------------------------------
rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$E$15"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Flags"
args6(0).Value = "S"
args6(1).Name = "FormulaCommand"
args6(1).Value = 0
args6(2).Name = "SkipEmptyCells"
args6(2).Value = false
args6(3).Name = "Transpose"
args6(3).Value = true
args6(4).Name = "AsLink"
args6(4).Value = false
args6(5).Name = "MoveMode"
args6(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args6())
end sub
Then assigned this macro to a shape i am using as a button.
The result is anything in the destination cell remains and new content is added below within the same cell.