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

Creating a macro - Writing a Script - Using the API

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

Postby coolguyhb » Thu Jan 24, 2019 11:21 pm

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
coolguyhb
 
Posts: 6
Joined: Tue Sep 11, 2018 9:38 pm

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

Postby JeJe » Fri Jan 25, 2019 1:31 am

cell.string = cell.string & othercell.string
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby coolguyhb » Fri Jan 25, 2019 5:29 pm

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
coolguyhb
 
Posts: 6
Joined: Tue Sep 11, 2018 9:38 pm

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

Postby JeJe » Fri Jan 25, 2019 7:50 pm

replace cell or othercell with something like:
thiscomponent.sheets(0).getCellByPosition(1,2)
for sheet 0, cell co-ordinates 1 across, 2 down.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby coolguyhb » Fri Jan 25, 2019 7:54 pm

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   Expand viewCollapse view
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
coolguyhb
 
Posts: 6
Joined: Tue Sep 11, 2018 9:38 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests