[Solved] Macro to disable warning of overwrite data in cell

[sokolowitzky]

Dear All,

Once again I'm posting a question for a very simple matter, but again, I've been searching for this for hours, in the forum, in google, in pitonyak etc.
I could not find a way to copy a cell(containing a formula) and paste it into multiple cells(ranges).
So I decided using macro recorder. But now, it pops up overwriting prompt. Normally this is something I prefer to keep. I know that I can disable/enable it from the tools>options...
But I want to disable this pop up while macro commands are working.

I've found some codes to disable some warning messageges, but I could not find one to disable overwrite a cell warning.

Could anyone please kindly honor me some guidance?

Code: Select all

sub fromheretothere

dim document   as object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "from"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "to"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

end sub

[Zizi64]

My first tip:

Do not use the Dispatcher; WRITE your macros (based on the API functions), but not record them.

[Lupp]

+1 @Zizi64
And: How would you think to get the source range and the target cell ("from" and "to" in your pseudo-code) in?
Passing parameter values is a fundamental problem with using macros in general, and even much more with recorded macros.
In addition most likely there isn't a parameter for the .uno:Paste command making it skip the alert. (I don't know for sure. Didn't know good documentation.)

Code: Select all

Global sourceRg As Object, targetC As Object                    REM Not recommendable "in production"!

Sub getRangeToCopy()
sourceRg = ThisComponent.Sheets(0).getCellRangeByName("b2:d11") REM Only for demo!
End Sub
Sub getTargetToPasteTo()
targetC = ThisComponent.Sheets(1).GetCellRangeByName("f7")      REM Only for demo!
End Sub

Sub myCopyPaste()
ThisComponent.Sheets(2).copyRange(targetC.CellAddress, sourceRg.RangeAddress)
REM Any existing sheet can do this. The document itself, however, doesn't know the method.
End Sub

Of course, this is only a demo and not at all usable code!
You need to run the first two Sub before the third one can work.

[sokolowitzky]

Thanks a lot. But the macro you have written, as I understand, copies a range(more than once cell) to a point. This is helpful as well.
But unfortunately, it's not what I'm looking for. I try to paste only one cell into many cells.
By the way, I could do this with some loop (like, for i = 0 to 3) but I don't want to use loops, they take time in long sheets.

[Villeroy]

Paste one top-left cell, drag it down and to the right.
http://www.openoffice.org/api/docs/comm ... eries.html with FillMode.SIMPLE

Code: Select all

oSheet.copyRange(addrTargetCell, addrSourceRange)
oTargetRange.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM, com.sun.star.sheet.FillMode.SIMPLE,0,0,0)
oTargetRange.fillSeries(com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.SIMPLE,0,0,0)

[sokolowitzky]

@villeroy
thank you. it's a bit confusing to use((0, 0, 0) part), that's why I looked for another solution. But seems like there is no basic code to mimic what copy cell to range does.
I will stay with this .