Macro to copy formulas only in Calc does work only sometimes

English
,
#1

I’m using Calc 25.2.2.2 on Windows 11.
I’m trying to write a basic macro that should actually copy all formulas from one row to a different row, but only the formulas. Additionally the relative references in the formulas need to be updated.
The manual workflow is: select source row, copy, select first cell of target row, paste only formulas.
This manual workflow gives the wanted result.

I have already tried a lot of different macro options using the API or the dispatcher. Just copying the formula property from one cell to the other won’t update the references.
Actually I thought this should work:

Sub InsertRow
	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	oSheet=thiscomponent.getcurrentcontroller.activesheet
	oSel = ThisComponent.getCurrentSelection()
	nRow = oSel.CellAddress.Row
	If nRow = 0 Then Exit Sub
	
	' Insert new row below
       oSheet.Rows.insertByIndex(nRow + 1, 1)
       
    	' Copy formulas
    	Dim oSource As Object, oTarget As Object
    	oSource = oSheet.getCellRangeByPosition(0, 1, oSheet.Columns.Count-1, 1) ' source row
    	oTarget = oSheet.getCellByPosition(0, nRow+1) ' target cell
    
    	ThisComponent.CurrentController.Select(oSource)
    	dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
    	ThisComponent.CurrentController.Select(oTarget)
	dispatcher.executeDispatch(document, ".uno:PasteOnlyFormula", "", 0, Array())
End Sub

It seems that there is a problem with the Ranges. Unlike as the manual workflow the above macro does only paste into the selected cell.
When I adjust the sorce and target to be like:

        oSource = oSheet.getCellRangeByPosition(0, 1, oSheet.Columns.Count-1, 1) ' source row
    	oTarget = oSheet.getCellRangeByPosition(0, nRow+1, oSheet.Columns.Count-1, nRow+1) ' target row

it oddly copies the formula of the first column into all cells of the target row.

I also tried to copy the formulas cell by cell with a for loop, but even here the result is that always only the formula of the first cell is pasted in all cells, so it seems that although I can see that different source cells are selected for copying, actually only the very first copy does happen.

To completely mess up my mind all of this only is working for a few runs and then it stops working completely and nothing is copied or pasted any more.

Any help is appreciated.

#3

Copy the .Tokens property.

#7

There is no .Tokens property at a CellRange. Only a single cell has the Tokens. What is it for?

#9

In your case ( uno:PasteOnlyFormula), the size of the target range should be the same as the source range. For example:

    	oSource = oSheet.getCellRangeByPosition(0, 0, 10, 0)
    	oTarget = oSheet.getCellByPosition(0, nRow+1, 10, nRow+1)
#10

The .Tokens property contains the formula in its technical representation. The formula shown when editing is derived from it. The structure contains the needed information for the automatic adaption of relative addresses.

#11

why ? :thinking:
works ok as is (for me)

#12

OK, the .Tokens property gives a partial success. The tokens are copied and the formula in the new cell has the correct references. I guess the uno:PasteOnlyFormula pastes the formula string, what seems at least a little problematic.

Now the only thing left here is how to deal with a CellRange. There is no TokensArray on a cellRange, only ArrayTokens what seems to be something different.
Do I really have to loop over all cells, check if they are a formula and then do the copy one by one?
Or is there a better way in one go?

#14

The standard way is to use the .uno:InsertContents command, which has options for copying various properties.
There are usage examples on the forum.

#15

@sokol92 I tried uno:InsertContents first. It has the same problems as uno:PasteOnlyFormula in regards to the range and to the reliability.

#16

Let’s check together.
Open attached file and execute the Test macro.
The formula in cell C2 should be =A2+B2.
TestInsertContents.ods (9.0 KB)

#17

.ArrayTokens is for array formulas.
foRgs = mysorce.queryFormulaCells …
For Each foRg In foRgs …

See API documentation about cell cursors.

1 Like
#18

Thanks sokol92. I tried your test file and it worked without any problems. So I tried your makro using uno:PasteOnlyFormula instead of the uno:InsertContents and that worked too. Then I tried both versions with my spreadsheet and the original macro and that worked too.
So I wondered why did it not work for me the first time. The only difference of your solution to my try is that you are using ThisComponent.CurrentController as the first argument to the dispatch command and I have been using ThisComponent.CurrentController.Frame. Could this be the reason?

For reference of people coming across this later, here is the working solution:

Sub InsertRow
	On Error Goto ErrorHandler
	
    Dim oSource as Object, oTarget as Object, oDisp as Object, oCtrl as Object
  	Dim props(0) As New com.sun.star.beans.PropertyValue
  	
  	oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
  	oCtrl=ThisComponent.CurrentController
   
    oSheet = ThisComponent.getCurrentController.ActiveSheet
    oSel = ThisComponent.getCurrentSelection()
    nRow = oSel.CellAddress.Row + 1
    If nRow <= 1 Then Exit Sub

    ' Insert new row below
    oSheet.Rows.insertByIndex(nRow, 1)
    
    ' Copy formulas from active row
    oSource = oSheet.getCellRangeByPosition(0, 1, oSheet.Columns.Count-1, 1)
    oTarget = oSheet.getCellRangeByPosition(0, nRow, oSheet.Columns.Count-1, nRow)
    
    ' Select and copy the source range
    oCtrl.Select oSource
    oDisp.executeDispatch(oCtrl, ".uno:Copy", "", 0, Array()) 
   
    ' Select the target range
    oCtrl.Select oTarget

  	' Paste only the formulas
  	oDisp.executeDispatch(oCtrl, ".uno:PasteOnlyFormula", "", 0, Array())
    
    dim endPos(0) as new com.sun.star.beans.PropertyValue
	endPos(0).Name = "ToPoint"
	endPos(0).Value = "I" + (nRow + 1)
	
	oDisp.executeDispatch(oCtrl, ".uno:GoToCell", "", 0, endPos())

    
    Exit Sub
	ErrorHandler:
	    MsgBox "Error " & Err & ": " & Error$ & " (Line " & Erl & ")"
End Sub
#19

I don’t think so.
Let’s continue our exploration.
Here’s a version with the .Frame added.
Works fine for me.

TestInsertContents2.ods (10.3 KB)

#2

based on what ?

how about Macros/Basic/Calc/Ranges - The Document Foundation Wiki #Copy ?
How to copy a row? - #15 by Lupp ?

#4

Based on the API documentation and the recorded dispatcher commands.

The problem with copyRange is that it copies the whole data and formatting. I want to copy only the formulas. And as I have written, relative references in formulas needs to be updated.

#5

but then it’s only row 2 ? :thinking:

sounds like false negative - Wikipedia

how do you run it ?

#6

Yes, only row two as the source is the intention. Like row two is the template and its formulas should be copied to a different row.

The macro is run from a button.
Running the macro itself is not the problem, it runs all the times. The command oSheet.Rows.insertByIndex(nRow + 1, 1) also works all the times. Just the copy/paste stuff seems to be unreliable. I never managed to get it to work completely, but with alterations I can e.g. copy/paste the formula of one cell. So I tried that with a loop. Ran the macro with the loop maybe 10 times and then the next time the copy/paste failed. No changes in the macro before the fail. I have read that there may be problems with empty cells in the copy range but also this didn’t change.

#8

you may test them separately,

in the end more likely :

#13

Thanks for the suggestion. I don’t have clipboard management in place, also it works 100% when not run from a macro. The only difference between the manual workflow and the macro should be the execution speed, but I also checked that with some wait times.

But on the question you linked, I found another concept I wasn’t aware of: named expressions.
A named expression can solve some of my problems and is the superior solution.
So I have a solution but the initial problem that the dispatcher way of copy/pasteOnlyFormula is not working remains unsolved.

Support for extension dropped; new onboard function replaces extension functionality

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.