CALC - copy formula via macro

users
#1

Assume:

A1 contains the formula =SUM(B1:E1)

I want to programatically copy A1's formula to somewhere else in column
A that is determined at execution time, and I need the cell references
adjusted accordingly.

If it gets copied to A19, for example, I need the formula to read:
  =SUM(B19:E19)

The getFormula and setFormula methods don't adjust the relative
references - they get and set text.

Is there a way to get the references adjusted auto-magically?
Is there a method that can take the formula obtained via the getFormula
and apply a row and column distance offset to the contents?

I don't want to use the macro recorder/dispatcher - too ugly.

#2

http://user.services.openoffice.org/en/forum/viewtopic.php?f=45&t=169&p=2136&hilit=fillSeries#p2136

#3

Did you explore the possibilities of ”com.sun.star.table.CellAddressConversion”?
If not, take a look at Listing 416 in http://www.pitonyak.org/OOME_3_0.pdf

Even if you can't use it in this situation, it might be good reading anyway…

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#4

Hi :slight_smile:
When you copy&paste a cell that contains a formula even if you paste it to a
different worksheet. If the equation was in Sheet1 cell A1 and you clicked
on a different tab at the bottom to look at Sheet3 and then pasted into
Sheet3's A12 then the formula would be
=SUM(B12:E12)

Often it is easier to get more control and sophistication by doing things
programmatically but the gui "point&click" approach is usually easier for
most people and can be surprisingly sophisticated.
Regards from
Tom :slight_smile:

#5

Sorry, my previous link answers the question how to drag down a calculated
field, so the formula with relative references spreads over a given column
of cells.

http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/XCellRangeMovement.html#copyRange
provides the "normal" way to copy&paste with all (conditional) formattings,
relative/absolute references and validation.
It takes a c.s.s.table.CellAddress to specify the target cell and a
c.s.s.table.CellRangeAddress to specify the source range.

The API has not obvious method to copy/move cells between different
documents. This is one of the rare cases where you may want to use the
dispatcher.

#6

Tom wrote:

Often it is easier to get more control and sophistication by doing things
programmatically but the gui "point&click" approach is usually easier for
most people and can be surprisingly sophisticated.
Regards from
Tom :slight_smile:

Show them how a double-click on the cell handle works. They will love it.

#7

Hi :slight_smile:
I know of dragging a cell from the handle in the bottom-right corner, or using a
right-click to get a menu of relevant things for the specific location of the
mouse at that moment but double-click and middle-clicks don't do anything for
me. Possibly i could assign functions fairly easily but i try to keep to
defaults.
Regards from
Tom :slight_smile:

#8

A double-click on the cell handle will drag down until the end of the
adjacent column which is what most people want to do in most cases.

#9

Doesn't happen to me with LibreOffice 3.3.4. Double click on the
handle does nothing, or I'm doing it wrong somehow (how many ways are
there to double click a handle anyway?).

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#10

Johnny Rosenberg wrote:

2011/9/3 Andreas Säger <villeroy@t-online.de>:

A double-click on the cell handle will drag down until the end of the
adjacent column which is what most people want to do in most cases.

Doesn't happen to me with LibreOffice 3.3.4. Double click on the
handle does nothing, or I'm doing it wrong somehow (how many ways are
there to double click a handle anyway?).

Works for me since the first version of OOo.
This feature depends on the adjacent column.
Having arbitrary content in A1:A100 and in B1, a double-click on the cell
handle in B1 drags down B1 until B100.
Works with A1 and B1:B100 as well.
Also with A1:A100 and B1:X1, dragging down B1:X1 until B100:X100 by a
double-click on the handle in X1.

#11

Okay, then I misunderstood you. Yes, you are right, it works!

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#12

Thank you for both links.

I was so intent on finding methods for formulas that I completely missed
looking at the problem from the cell perspective. Thanks for waking me up.

- --
Bill Gradwohl
Roatan, Honduras

#13

Dim o
   Dim oSheet
   Dim oRange
   Dim oDoc

   oRange = oDoc1.Sheets(0).getCellRangeByName("B2:C3")
   oDoc1.CurrentController.select(oRange)
   o = oDoc1.CurrentController.getTransferable()

   oRange = oDoc2.Sheets(0).getCellRangeByName("F1")
   oDoc2.CurrentController.select(oRange)
   oDoc2.CurrentController.insertTransferable(o)

#14

Andrew:

I'm going to have to study this one.

GetTransferable / insertTransferable - Never heard of them!

- --
Bill Gradwohl
Roatan, Honduras
504 9 899 2652
IM:BillGradwohl@gmail.com (No email please-IM only)

#15

Hi :slight_smile:
I'm not sure if it's relevant but there are some guides listed in the wiki
http://wiki.documentfoundation.org/Documentation#Other_Documentation_and_Resources

Regards from
Tom :slight_smile:

#16

Bill Gradwohl wrote:

GetTransferable / insertTransferable - Never heard of them!

It is clearly shown in my MRI object inspector, but the documentation is
somewhat obscure about what it really means.
http://api.openoffice.org/docs/common/ref/com/sun/star/datatransfer/XTransferableSupplier.html#getTransferable

Since I switched entirely to databases I do not copy around data. I link
them dynamically into my spreadsheets.

#17

Think of it as exposing (to some extent) the methods to get data to be pasted to the clipboard, and then inserting that data, but without actually using the clipboard, which means that it should be safer than using the clipboard (because another process will not clobber the clipboard at the same time you try to use it).

If I remember correct, you can also do something like this in a Write document.