Page 1 of 1

CONCATENATE macro program

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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   Expand viewCollapse view
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

PostPosted: 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)