Page 1 of 1
CONCATENATE macro program
Posted: Tue Sep 12, 2017 11:16 am
by chandrankrishnan
Hi all,Good day to you
can anyone help me with a concatenate macro program for the attached sheet.
i add tried with recordings but result varies from cell to cell.
I Uploaded my sample attachment sheet.
Thanks in advance
Re: CONCATENATE macro program
Posted: Tue Sep 12, 2017 11:21 am
by Zizi64
i add tried with recordings but result varies from cell to cell.
Recording a macro is not an efficeient way to create macros. The macro recorder can not record all of user's activity, and it works in the Calc and the Writer applications only.
You need WRITE your macros based on the API functions. You can use one of the supported programming languages to call the API functions.
Re: CONCATENATE macro program
Posted: Tue Sep 12, 2017 11:48 am
by chandrankrishnan
Hi zizi64,
Thanks for the reply.
I am in starting stage and studying macro program with help of forum only.so please guide me in this.
Re: CONCATENATE macro program
Posted: Tue Sep 12, 2017 12:16 pm
by Villeroy
This is not the place where you can learn how to program.
Re: CONCATENATE macro program
Posted: Tue Sep 12, 2017 12:18 pm
by RoryOF
You need to study Andrew Pitonyak's books on macro programming. These are at
http://www.pitonyak.org/oo.php
Re: CONCATENATE macro program
Posted: Tue Sep 12, 2017 2:33 pm
by chandrankrishnan
Hai all,
i tried with below macro program instead sum i changed with concatenate(with &function) but comma is not adding.please guide me in this.
Code: Select all
sub addAndMerge(event)
oSheet = thisComponent.Sheets(0)
oSheet.Columns.insertbyindex(7,2)
oSheet.getCellRangeByName("H1").String = " TOTAL QTY"
oSheet.getCellRangeByName("I1").String = " SL NO"
doc = thiscomponent
sheet = doc.CurrentController.ActiveSheet
sum = 0
row = 1
first = row
counter = 1
do
range = sheet.getCellRangeByPosition(3, row, 8, row+1)
data = range.DataArray
sum = sum & data(0)(3)
if data(0)(0) <> data(1)(0) then
sheet.getCellByPosition(7, first).setValue(sum)
tobemerged = sheet.getCellRangeByPosition(7, first, 7, row)
tobemerged.merge(True)
sheet.getCellByPosition(8, first).setValue(counter)
tobemerged = sheet.getCellRangeByPosition(8, first, 8, row)
tobemerged.merge(True)
sheet.Columns.removeByIndex(16, 1)
sum = 0
first = row + 1
counter = counter +1
end if
row = row +1
loop until data(1)(0) = ""
end sub
Re: CONCATENATE macro program
Posted: Tue Sep 12, 2017 2:41 pm
by Villeroy
What are you trying to do? The "output" range on your spreadsheet makes no sense. The spreadsheet's formula language can concatenate. Merging cells is a misunderstood anti-feature. Merging has nothing to do with concatenation.
=A1&","&B1
=CONCAT(A1;",";B1)