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.
[Solved] Add text from one cell to another using a macro
[Solved] Add text from one cell to another using a macro
Last edited by Hagar Delest on Fri Jan 25, 2019 10:40 pm, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
Harley
LibreOffice Version: 5.3.6.1
OS: CentOS Linux 7
LibreOffice Version: 5.3.6.1
OS: CentOS Linux 7
Re: Add text from one cell to another using a macro
cell.string = cell.string & othercell.string
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: Add text from one cell to another using a macro
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?JeJe wrote:cell.string = cell.string & othercell.string
Harley
LibreOffice Version: 5.3.6.1
OS: CentOS Linux 7
LibreOffice Version: 5.3.6.1
OS: CentOS Linux 7
Re: Add text from one cell to another using a macro
replace cell or othercell with something like:
thiscomponent.sheets(0).getCellByPosition(1,2)
for sheet 0, cell co-ordinates 1 across, 2 down.
thiscomponent.sheets(0).getCellByPosition(1,2)
for sheet 0, cell co-ordinates 1 across, 2 down.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: Add text from one cell to another using a macro
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
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.
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
The result is anything in the destination cell remains and new content is added below within the same cell.
Harley
LibreOffice Version: 5.3.6.1
OS: CentOS Linux 7
LibreOffice Version: 5.3.6.1
OS: CentOS Linux 7