How can I copy cell ranges between documents in a macro?

I want to copy a range of cells, including formats, from one open document to another (same as cut and paste) inside a macro. I can’t see any way of doing it as MoveRange only seems to work within a single document. Do I really have to copy the value/string/formula & format one by one or am I missing something?

Two approaches are described at [Solved] CopyRange Copy Cells to another Spreadsheet or file (View topic) • Apache OpenOffice Community Forum. The first way is the easiest, using the dispatcher to copy and paste.

Sub rangecopy
    Dim oDocA As Object, oDocB As Object, oSheetA As Object, Dummy(), oRangeA as object, targetcell as object
    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    oDocA = ThisComponent
    oFrameA = oDocA.CurrentController.Frame
    oSheetA = oDocA.getSheets.getByIndex(0)
    oRangeA = oSheetA.getCellRangeByName("C56:J56")
    oDocA.CurrentController.Select(oRangeA)
    oDispatcher.executeDispatch(oFrameA, ".uno:Copy", "", 0, Dummy())
    oDocB = StarDesktop.loadComponentFromURL ("file:///D:/DATI/prova/invoicelog.ods", "_blank",0, Dummy() )
    oSheetB = oDocB.getSheets.getByIndex(0)
    c = oSheetB.createCursor
    c.gotoEndOfUsedArea(false)
    LastRow = c.RangeAddress.EndRow +1
    targetcell= oSheetB.getCellByPosition(0,LastRow)
    oDocB.CurrentController.Select(targetcell)
    oFrameB = oDocB.CurrentController.Frame
    oDispatcher.executeDispatch(oFrameB, ".uno:Paste", "", 0, Dummy())
End Sub

The second way is to get and set the data array. However, this does not include formatting.

iNum = oDataLog.Sheets.getCount()
oSheet2 = oDataLog.Sheets.getByIndex( iNum-1 ) 'last sheet'
oCursor = oSheet2.createCursor() : oCursor.gotoEndOfUsedArea( true )
tRange = oCursor.RangeAddress : lTarget = tRange.EndRow + 1
oTarget = getCellRangeByPosition ( 2, lTarget, 9, lTarget ) 'Cn:Jn'
oTarget.setDataArray( oSource.getDataArray )

Villeroy gives a list of possibilities for using the API (in other words, not the dispatcher) at [Solved] Copy and Paste Syntax (View topic) • Apache OpenOffice Community Forum.

Thanks Jim. Sorry about the delay - been unwantedly busy :frowning:

Villeroy basically says that if there are different types of data in the cells (which there are in my case) you have to do it cell by cell. Or use the dispatcher. I’ll work out which is less damaging to my sanity in teh next few days.