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

Creating a macro - Writing a Script - Using the API

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

Postby sokolowitzky » Tue Sep 04, 2018 2:27 pm

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   Expand viewCollapse view
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
Last edited by robleyd on Wed Sep 05, 2018 12:39 am, edited 2 times in total.
Reason: Add green tick [robleyd, Moderator]
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 62
Joined: Mon Sep 15, 2014 7:34 pm

Re: Macro to disable warning of overwrite a data in cell

Postby Zizi64 » Tue Sep 04, 2018 3:17 pm

My first tip:

Do not use the Dispatcher; WRITE your macros (based on the API functions), but not record them.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8486
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to disable warning of overwrite a data in cell

Postby Lupp » Tue Sep 04, 2018 3:35 pm

+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   Expand viewCollapse view
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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Macro to disable warning of overwrite a data in cell

Postby sokolowitzky » Tue Sep 04, 2018 3:47 pm

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.
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 62
Joined: Mon Sep 15, 2014 7:34 pm

Re: Macro to disable warning of overwrite a data in cell

Postby Villeroy » Tue Sep 04, 2018 4:01 pm

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   Expand viewCollapse view
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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27305
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to disable warning of overwrite a data in cell

Postby sokolowitzky » Tue Sep 04, 2018 5:01 pm

@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 .
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 62
Joined: Mon Sep 15, 2014 7:34 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: MSN [Bot] and 11 guests