[Solved] Add text from one cell to another using a macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
coolguyhb
Posts: 6
Joined: Tue Sep 11, 2018 9:38 pm

[Solved] Add text from one cell to another using a macro

Post 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.
Last edited by Hagar Delest on Fri Jan 25, 2019 10:40 pm, edited 1 time in total.
Reason: tagged solved
Harley
LibreOffice Version: 5.3.6.1
OS: CentOS Linux 7
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Add text from one cell to another using a macro

Post by JeJe »

cell.string = cell.string & othercell.string
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
coolguyhb
Posts: 6
Joined: Tue Sep 11, 2018 9:38 pm

Re: Add text from one cell to another using a macro

Post 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?
Harley
LibreOffice Version: 5.3.6.1
OS: CentOS Linux 7
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Add text from one cell to another using a macro

Post 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.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
coolguyhb
Posts: 6
Joined: Tue Sep 11, 2018 9:38 pm

Re: Add text from one cell to another using a macro

Post 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.
Harley
LibreOffice Version: 5.3.6.1
OS: CentOS Linux 7
Post Reply