[Solved] Copying data from one sheet to another

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

[Solved] Copying data from one sheet to another

Post by Herb40 »

Suppose that I have SS oDoc loaded and visible on my monitor and want to use the transferable content feature to copy data from a sheet in oDoc to a sheet in another SS. I don't want the display to change when the select is executed. But Calc displays the selected range in the code below, even though a LockControllers() statement is executed before the select.

What is the proper way to prevent the display from changing here? I don't want to hide the entire oDoc, and setting oSheet.IsVisible=False is unsatisfactory, too.

Code: Select all

	oDoc.LockControllers()
	oRange = oSheet.getCellRangeByPosition(0, 6, 6, oRows-1)
	oDoc.CurrentController.select(oRange)	
	transContent = oDoc.CurrentController.getTransferable()
Thanks for your help.
Last edited by Herb40 on Thu Feb 16, 2017 1:13 am, edited 2 times in total.
OpenOffice 4.1.3 on Windows 10
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

[SOLVED] Copying data from one SS to another

Post by Herb40 »

I may have found a very nice solution to my problem, thanks to a post by FJCC that I found elsewhere. I never knew that you could do anything directly with a sheet's dataArray, other than getDataArray() and setDataArray(). Whether it can be used to move data from one SS to another, I still have to determine. Regardless, this appears to be the best way to move large amounts of data between sheets.

Code: Select all

	Dim Doc		as Object
	Dim Sheets	as Object
	Dim fSheet	as Object  ' from sheet
	Dim fRange	as Object
	Dim tSheet	as Object  ' to sheet
	Dim tRange	as Object
		
	Doc = ThisComponent
	Sheets = Doc.Sheets()
	fSheet = Sheets.getByName("ALPHA")
	tSheet = Sheets.getByName("BETA")
	fRange = fSheet.getCellRangeByName("A6:G350")
	tRange = tSheet.getCellRangeByName("A6:G350")
	tRange.DataArray = fRange.DataArray
This approach is fast and avoids the problem of having to make a copy of the data with getDataArray and then store it elsewhere with setDataArray. It also solves the problem I originally complained about in that it doesn't select the dataArrays as transferable content does.
Last edited by Herb40 on Thu Feb 16, 2017 1:22 am, edited 1 time in total.
OpenOffice 4.1.3 on Windows 10
Herb40
Posts: 134
Joined: Thu May 08, 2014 3:35 am

Re: [SOLVED] Copying data from one sheet to another

Post by Herb40 »

This technique does work in copying dataArrays from a sheet in a SS to a sheet in a different SS.
OpenOffice 4.1.3 on Windows 10
Post Reply