HowTo CopyToCell without confirmation dialog

[zvegelj]

I have a basic macro in LO Calc 7.3 to insert range and copy cells into, and when I fire this macro CopyToCell triggers dialog asking if I want to replace values in cells (A6:D6)

Sub AddNewDate
	GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
	Set dDoc = CreateScriptService("Document", "MyTable.ods")
	cTdy = dDoc.GetValue("sh1.A5")
	dDoc.ShiftDown("sh1.A6:D6")		
	dDoc.CopyToCell("sh1.A7:D7", "sh1.A6")
EndSub

I didn’t see any argument in the reference manual for CopyToCell, that could suppress this dialog.

[Gbp]

See under Tools->Options->LibreOfficeCalc->General,
if it is marked “show overwrite warning when pasting data”, uncheck it.

[Ratslinger]

@zvegelj
My test shows no problem with the code (provided a correction is made EndSub s/b End Sub).
There is also a line:

cTdy = dDoc.GetValue("sh1.A5")

.
that does nothing in that code.
.
Now if you look at the logic of the code:

dDoc.ShiftDown("sh1.A6:D6")

.
inserts a blank area (probably). With that being the case, the dialog should not appear. It seems the code you are using may be different than what is posted here.
.
I mention probably here because it is known that new columns ( and I seem to recall new rows, including partials?) are not really new at all but rather a copy and wipe. Problem is not all data gets wiped. This is probably very remote in this case. See tdf#109412

[zvegelj]

@zvegelj

Sub AddNewDate
	GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
	Set dDoc = CreateScriptService("Document", "MyTable.ods")
	cTdy = dDoc.GetValue("sh1.A5")
	dDoc.ShiftDown("sh1.A6:D6")		
	dDoc.CopyToCell("sh1.A7:D7", "sh1.A6")
End Sub

inserts a blank area (probably). With that being the case, the dialog should not appear. It seems the code you are using may be different than what is posted here.
.
I mention probably here because it is known that new columns ( and I seem to recall new rows, including partials?) are not really new at all but rather a copy and wipe. Problem is not all data gets wiped. This is probably very remote in this case. See tdf#109412

Thanks for info, I haven’t noticed your reply earlier. You are right I had more code, but mostly just copying cells, but I missed the line that is giving me confirmation message and that is
dDoc.CopyToRange("sh1.A8", "sh1.A6:A7")
Also variable 'cTdy 'is used later in the code, for this example is not needed.
I need to populate formulas, as they get repeated or skip a line when doing insert

[zvegelj]

@Gbp

See under Tools->Options->LibreOfficeCalc->General,
if it is marked “show overwrite warning when pasting data”, uncheck it.

Thanks @Gbp, that could be a solution, although I like this dialog when doing copy/replace manually so I double check every time. I thought there is something I could do within the code.

[joshua4]

Depending on what you need copied (do you need formats, etc.) have you also considered just assigning using getDataArray and setDataArray straight from UNO?

[zvegelj]

No I don’t have an experience with UNO, are you kind enough to post an example to copy range of cells to another range.
I do want to copy everything, formulas and formats, and values as a result. Thanks

[joshua4]

A range of solutions can be seen at Macro to Copy Cell values, formats, and image - #11 by mauricio.

Here is a solution based on one by @mauricio that seems to “just work” and makes perfectly good sense:

Option Explicit

Sub Test()
	CopyAll "Sheet1","C9","Sheet2","J6"
End Sub


Sub CopyAll(SourceSheetName As String, SourceCellName As String, TargetSheetName As String, TargetCellName As String)
	Rem Based on https://ask.libreoffice.org/t/macro-to-copy-cell-values-formats-and-image/45980/11 by mauricio
	Dim Doc As Object
	Dim Source As Object
	Dim TargetSheet As Object
	Dim Target As Object
	
	Doc = ThisComponent
	
	Source = Doc.Sheets.getByName(SourceSheetName).getCellRangeByName(SourceCellName)
	TargetSheet = Doc.Sheets.getByName(TargetSheetName)
	Target = TargetSheet.getCellRangeByName(TargetCellName).getCellByPosition(0,0)
	TargetSheet.copyRange(Target.CellAddress, Source.RangeAddress)
	
End Sub

The trick is to feed copyRange what it wants, a source range address but a target cell address (since whatever the source range size is, it just plops down with its upper left corner at the target cell).