CONCATENATE macro program

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
chandrankrishnan
Posts: 63
Joined: Sat Mar 05, 2016 1:21 pm

CONCATENATE macro program

Post 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
Attachments
CONCATENATE mac.ods
(15.59 KiB) Downloaded 195 times
OpenOffice 4.1.2 windows vista
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: CONCATENATE macro program

Post 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.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
chandrankrishnan
Posts: 63
Joined: Sat Mar 05, 2016 1:21 pm

Re: CONCATENATE macro program

Post 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.
OpenOffice 4.1.2 windows vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CONCATENATE macro program

Post by Villeroy »

This is not the place where you can learn how to program.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: CONCATENATE macro program

Post by RoryOF »

You need to study Andrew Pitonyak's books on macro programming. These are at
http://www.pitonyak.org/oo.php
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
chandrankrishnan
Posts: 63
Joined: Sat Mar 05, 2016 1:21 pm

Re: CONCATENATE macro program

Post 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
Last edited by RoryOF on Tue Sep 12, 2017 2:36 pm, edited 1 time in total.
Reason: Added [Code] tags [RoryOF, Moderator]
OpenOffice 4.1.2 windows vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CONCATENATE macro program

Post 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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply