uno:PasteUnformatted skip text import dialog in calc

lovecraft22 · February 4, 2023, 5:47pm

Dear all,
I’m using this command in a macro to paste some data to a calc sheet:

dispatcher.executeDispatch(document, ".uno:PasteUnformatted", "", 0, Array())

and I was wondering if there was any way to automatically accept or skip the “Text import” dialog that pops up right after this line is executed.

Thanks

Villeroy · February 4, 2023, 5:53pm

menu:Sheet>Sheet from file…
Check the “Link” option.
Specify the import options.
Next time replace the csv file before you open the spreadsheet.

lovecraft22 · February 4, 2023, 7:03pm

Thanks. My macro is part of a bigger process which already leverages on your suggestion. What the macro does is to copy the data from the linked file sheet and paste it somewhere else.

Villeroy · February 4, 2023, 7:58pm

menu:File>New>Database…
Connect to existing database of type “Text”
Specify the import parameters and the directory of the text table(s). You may drop more similarly structured csv files in that directory. Each csv file will be represented as a table of a pseudo-database.
Register the database.
Save the database.
Queries allow you to present any subset of table columns and rows in any order of columns and rows.
In Calc (or Writer or Impress) you can drag any table or query onto a sheet cell which copies (and links) the data to the target range.
[Tutorial] Using registered datasources in Calc

sokol92 · February 5, 2023, 11:21am

You can try another way:

' ...
Dim props(0) As new com.sun.star.beans.PropertyValue
props(0).Name="Flags" : props(0).Value="SVD"  ' insert strings, numbers, dates
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, props())

jfn · February 5, 2023, 12:17pm

You might also use the getTransferable / insertTransferable methods from the document controller :

Sub PasteTransferable(ByRef pSrcDoc As Object, pSrcRange As Object, pTgtDoc As Object, pTgtRange As Object)
'Pastes a range into another one. The formatting is retained.
'Input:
'-- pSrcDoc: the source document object.
'-- pSrcRange: the range to be copied.
'-- pTgtDoc: the target document object.
'-- pTgtRange: the range in which the copy must be processed.
'
'Caution: this sub can only process visible documents.

	Dim lo_TgtSheet As Object
	Dim lo_Tfer As Object
	Dim lo_TLCell As Object
	
	pSrcDoc.CurrentController.select(pSrcRange)
	lo_Tfer = pSrcDoc.CurrentController.getTransferable()
	
	lo_TgtSheet = pTgtDoc.CurrentController.ActiveSheet
	lo_TLCell = lo_TgtSheet.getCellByPosition(pTgtRange.RangeAddress.StartColumn, pTgtRange.RangeAddress.StartRow)
	pTgtDoc.CurrentController.select(lo_TLCell)
	pTgtDoc.CurrentController.insertTransferable(lo_Tfer)

End Sub 'PasteTransferable

HTH,