Ask Your Question

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

asked 2018-03-02 18:14:43 +0200

ptoye gravatar image

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-03-02 20:19:52 +0200

Jim K gravatar image

updated 2018-03-02 20:20:18 +0200

Two approaches are described at 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("")
    oDocA = ThisComponent
    oFrameA = oDocA.CurrentController.Frame
    oSheetA = oDocA.getSheets.getByIndex(0)
    oRangeA = oSheetA.getCellRangeByName("C56:J56")
    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
    LastRow = c.RangeAddress.EndRow +1
    targetcell= oSheetB.getCellByPosition(0,LastRow)
    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

edit flag offensive delete link more


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

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.

ptoye gravatar imageptoye ( 2018-03-04 19:56:35 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-02 18:14:43 +0200

Seen: 1,717 times

Last updated: Mar 02 '18