Copy selected range of cells to clipboard with macro

[ScottCLO]

trying to write a macro to automatically select a range of cells and copy them to my clipboard, as if i pressed ctrl-c.

Sub copy_1
	oCtrl = ThisComponent.CurrentController
	dim range As Variant
	range = Array("A", "B", "C", "D")
	dim last_row As integer
	last_row = 3
	for row = 3 to 100
		rem Scann Document for blank line
		for i = 0 to 3
			dim test as Variant
			test = oCtrl.ActiveSheet.GetCellRangeByName (range(i) + CStr(row))
			If test.value <> 0 or test.string <> "" Then 
				last_row = row
			end if
		next
	next
	oCtrl.Select(oCtrl.ActiveSheet.GetCellRangeByName("A1:"+"F"+last_row))
	
	copy() -- I need something that will work here.
End Sub

I got the selection part of the code down I just need to figure out how to send the copy command. Any ideas?

[joshua4]

Just so you know, too, if your purpose is to paste values (not formats) back into the same sheet, another sheet, or even another Calc document, then it is more straightforward to get ranges and use getDataArray() and setDataArray() from the UNO API rather than mess with the clipboard.

Just so you can see it, here’s a partial example, just so you can see–it’s not functional on its own:

'Copy values from one sheet to another (or another non-intersecting location on same sheet)
'	Copied range is from specified top-left to automatic bottom right of used range on SourceSheet
'	Copy of range starts at the specificed top-left on TargetSheet
Function CopySheetValues(SourceSheet As Object, SourceStartColumn As Long, SourceStartRow As Long, TargetSheet As Object, TargetStartColumn As Long, TargetStartRow As Long)
	Dim SourceRange As Object
	Dim TargetRange As Object
	
	SourceRange = UsedRange(SourceSheet, SourceStartColumn, SourceStartRow)

	With SourceRange.getRangeAddress()
		TargetRange = TargetSheet.getCellRangeByPosition( _
			TargetStartColumn, TargetStartRow, TargetStartColumn + .EndColumn - .StartColumn, TargetStartRow + .EndRow - .StartRow)
	End With
	TargetRange.setDataArray(SourceRange.getDataArray())
	
End Function

[ScottCLO]

I should have stated in my original post what my goal was. I’m copying the spreadsheet into the system clipboard and pasting it into a note taking program with HTML formatting.

Thanks for the insight though, I might find this useful in the future as I struggle with the uno api documentation and Franky53’s book. I get all the information is in there but its 1600 pages and makes my brain melt, i’m not a software engineer. The only way things seem to make since to me is specific examples in context. I’m sure that’s frustrating to allot of people so I appreciate the time and patients of everyone on this forum.

[Lupp]

It*s not relevant in what way you got the (single) SheetCellRange.
To create a copy in the system’s clipboard you need to select that range then, and to execute the .uno:Copy command using the DispatchHelper.
Record any Copy action to see how that looks in Basic. (Very raw. You may wan t to apply a bit of cosmetics.)
If you want to not change the selection for the user, you need to save the previous selection to a variable, and to select it again after the :Copy command.

If you don’t need to use the system clipboard, but want to insert the copied contents (formats/… whatever) somewhere else into a LibreOffice component, you can avoid the Dispatcher, and use XTransferable instead.

[ScottCLO]

@Lupp

To create a copy in the system’s clipboard you need to select that range then, and to execute the .uno:Copy command using the DispatchHelper.

Thanks, this works as expected.

REM  *****  BASIC  *****

Sub copy_1
	oCtrl = ThisComponent.CurrentController
	dim range As Variant
	range = Array("A", "B", "C", "D")
	dim last_row As integer
	last_row = 3
	for row = 3 to 100
		rem Scann Document for blank line
		for i = 0 to 3
			dim test as Variant
			test = oCtrl.ActiveSheet.GetCellRangeByName (range(i) + CStr(row))
			If test.value <> 0 or test.string <> "" Then 
				last_row = row
			end if
		next
	next
	oCtrl.Select(oCtrl.ActiveSheet.GetCellRangeByName("A1:"+"F"+last_row))
	
	dim Args()
	document = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") 
	dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Args())
	
End Sub