[Solved] Calc, how to copy/paste cellvalues and not formulas

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
exxy19
Posts: 5
Joined: Mon Dec 03, 2018 12:15 am

[Solved] Calc, how to copy/paste cellvalues and not formulas

Post by exxy19 »

Hello, I'm quite new to this and have been trying to steal code snippets from here and there with some success.

Now I am trying to copy values from cells in one sheet to cells in another sheet but I get a reference error in the target cells since the source cells contains formulas with references to other cells in that sheet.

I only want to paste the values, not formulas, does anyone have an idea?

Code: Select all

Sub Sort_copy_to_statistics

Dim LRowindex,LColindex,maxit,i,getcol,setcol as long
Dim oDoc As Object
dim oSheet as object
Dim oRange
Dim o



LColindex = LastColNum 'Call to function that gets last column index that has content
LRowindex = LastRowNum  'Call to function that gets last row index that has content
Print "Nummer "& LRowindex  

oDoc = ThisComponent
oSheet = oDoc.Sheets().getByName("Inventering")
oRange = oSheet.getCellRangeByPosition(0,0,1,LRowindex)
ThisComponent.CurrentController.select(oRange)
o = ThisComponent.CurrentController.getTransferable()

oSheet = oDoc.Sheets().getByName("Veckostatistik")
oRange = oSheet.getCellRangeByPosition(0,0,1,LRowindex)
ThisComponent.CurrentController.select(oRange)
ThisComponent.CurrentController.insertTransferable(o)

'Copy to statistik
maxit = 0
i = 0

Do while maxit < LColindex
	oSheet = oDoc.Sheets().getByName("Inventering")
	getcol = 8+6*i
	setcol = 2+i
	oRange = oSheet.getCellRangeByPosition(getcol,0,getcol,LRowindex)
	ThisComponent.CurrentController.select(oRange)
	o = ThisComponent.CurrentController.getTransferable()
	oSheet = oDoc.Sheets().getByName("Veckostatistik")
	oRange = oSheet.getCellRangeByPosition(setcol,0,setcol,LRowindex)
	ThisComponent.CurrentController.select(oRange)
	ThisComponent.CurrentController.insertTransferable(o)
	i= i+1
	maxit = getcol +2
Loop


End Sub
There are some print commands in the code but they are only there to debug during development.

Everything works except I get the cell references that doesn't work on the new sheet.

I would really appreciate some help.

Cheers!
Last edited by exxy19 on Tue Dec 04, 2018 11:27 pm, edited 1 time in total.
Confused Noob
OpenOffice 4.1.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc, how to copy / paste cellvalues and not formulas?

Post by Villeroy »

Hit the Insert key and then Enter. Simple as that.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
exxy19
Posts: 5
Joined: Mon Dec 03, 2018 12:15 am

Re: Calc, how to copy / paste cellvalues and not formulas?

Post by exxy19 »

Thanks, that helped a lot :-) or maybe not actually.
So where should I put that in my code?

I have read most of the basic guide but it doesn't say anything about how to filter cell content.
I guess I can always get this by iterating cell by cell collecting only the string I think but that will slow down the processing quite a lot.

So it seems like I completely missed what you were trying to explain.
Am I supposed to do that after running the code or what?
Confused Noob
OpenOffice 4.1.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc, how to copy / paste cellvalues and not formulas?

Post by Villeroy »

Nobody needs a macro for this. SImply use the usual shortcuts.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Calc, how to copy / paste cellvalues and not formulas?

Post by Lupp »

Generally you would surely do it as Villeroy suggested: Select source range > Copy (Ctrl+C) > Go to first cell of target range > Paste special (Ctrl+Shift+V) with the appropriate options.

If there should be an urgent reason to do it by user code, there is no sheet method like .copyRange allowing to make selections concerning the transformation of contents or operations. There is, however, a command .uno:InsertContents which is capable of doing what you want. To learn how to provide it with arguments (there are up to 9, I think), record a macro and edit it thoroughly to meet your needs. The only alternative I see is to first

Code: Select all

helperVar = sourceRange.GetDataArray
and then

Code: Select all

targetRange.SetDataArray(helperArray)
where the second array must be exactly equally dimensioned as the first one. This is not quite as efficient for large ranges as you might expect.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
exxy19
Posts: 5
Joined: Mon Dec 03, 2018 12:15 am

Re: Calc, how to copy / paste cellvalues and not formulas?

Post by exxy19 »

I do need to do this since the document is growing with data every week and the source data will change so everything needs to be updated each week and every sixth column should be copied to the other sheet.

If it's just have been for one single event I wouldn't have bothered trying to do it with a macro.
Confused Noob
OpenOffice 4.1.5 on Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Calc, how to copy / paste cellvalues and not formulas?

Post by Lupp »

Well, I won't easily understand the every-sixth-coulumn-deisgns, but if this is what you need, you will have to use the dispatcher command. It's not difficult.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc, how to copy / paste cellvalues and not formulas?

Post by Villeroy »

Every week? Even if I had to do this every hour, I wouldn't need a macro.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
exxy19
Posts: 5
Joined: Mon Dec 03, 2018 12:15 am

Re: Calc, how to copy / paste cellvalues and not formulas?

Post by exxy19 »

There will be roughly 240 rows and eventually the maximum amount of columns that Calc can handle, If you want to do that for me, be my guest.
So far you have only insulted me, If it bothers you so much that I would like to automate a very tedious task, please look elsewhere and do not disturb the people that would actually like to help me.

Just because I'm new to ooBasic dosn't mean I don't know anything about writing code as you assumed, since the documentation isn't as good as for a commercial product I went here for help but due to your attitude I'm considering buying in to Microsoft Office after all.
I'm guessing that you think that I'm a looser due to that but I can't stand the hostile attitude you have have displayed towards someone(me) that asks a polite question trying to learn more.

A sincere thanks to the persons that actually helped, I think I know how to proceed now and I hope it will not be too slow when the sheet fills up.
If I'm allowed to be around after this I will post a running result of my initial code if I get it to work.
Confused Noob
OpenOffice 4.1.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc, how to copy / paste cellvalues and not formulas?

Post by Villeroy »

If you would know the usual navigation keys for all kinds of spreadsheets and a lot of other programs you could jump to the right place within a second and paste.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
exxy19
Posts: 5
Joined: Mon Dec 03, 2018 12:15 am

Re: Calc, how to copy / paste cellvalues and not formulas?

Post by exxy19 »

I managed to solve it!

I found a comment in the Pitonyak book: OpenOffice.org Macros Explained which I can recommend to all noobs.

I just changed the "Transferable" commands to getDataArray and setDataArray to transfer both values and strings.

See the change in the last part of the code, in the Do While Loop where I commented out the old commands for variable "o" and added the new commands just below.

Code: Select all

Sub Sort_copy_to_statistics
' Kopierar artnr o namnkolumner från Inventering till Veckostatistik samt skapar nya kulomnrubriker för nästa v.
Dim LRowindex,LColindex,maxit,i,getcol,setcol as long
Dim oDoc As Object
dim oSheet as object
Dim oRange
Dim o


LColindex = LastColNum 'Call to function that gets last column index that has content
LRowindex = LastRowNum 'Call to function that gets last row index that has content
 
oDoc = ThisComponent
oSheet = oDoc.Sheets().getByName("Inventering")
oRange = oSheet.getCellRangeByPosition(0,0,1,LRowindex)
ThisComponent.CurrentController.select(oRange)
o = ThisComponent.CurrentController.getTransferable()

oSheet = oDoc.Sheets().getByName("Veckostatistik")
oRange = oSheet.getCellRangeByPosition(0,0,1,LRowindex)
ThisComponent.CurrentController.select(oRange)
ThisComponent.CurrentController.insertTransferable(o)

'Kopiera till statistik
maxit = 0
i = 0

Do while maxit < LColindex
	oSheet = oDoc.Sheets().getByName("Inventering")
	getcol = 8+6*i
	setcol = 2+i
	oRange = oSheet.getCellRangeByPosition(getcol,0,getcol,LRowindex)
	ThisComponent.CurrentController.select(oRange)
	'o = ThisComponent.CurrentController.getTransferable()
	o = oRange.getDataArray()
	
	oSheet = oDoc.Sheets().getByName("Veckostatistik")
	oRange = oSheet.getCellRangeByPosition(setcol,0,setcol,LRowindex)
	ThisComponent.CurrentController.select(oRange)
	'ThisComponent.CurrentController.insertTransferable(o)
	oRange.setDataArray(o)
	i= i+1
	maxit = getcol +2
Loop


End Sub
Confused Noob
OpenOffice 4.1.5 on Windows 10
Post Reply