Page 1 of 1
[Solved] Calc, how to copy/paste cellvalues and not formulas
Posted: Mon Dec 03, 2018 12:35 am
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!
Re: Calc, how to copy / paste cellvalues and not formulas?
Posted: Mon Dec 03, 2018 12:56 am
by Villeroy
Hit the Insert key and then Enter. Simple as that.
Re: Calc, how to copy / paste cellvalues and not formulas?
Posted: Mon Dec 03, 2018 12:58 am
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?
Re: Calc, how to copy / paste cellvalues and not formulas?
Posted: Mon Dec 03, 2018 1:14 am
by Villeroy
Nobody needs a macro for this. SImply use the usual shortcuts.
Re: Calc, how to copy / paste cellvalues and not formulas?
Posted: Mon Dec 03, 2018 1:35 am
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.
Re: Calc, how to copy / paste cellvalues and not formulas?
Posted: Mon Dec 03, 2018 1:43 am
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.
Re: Calc, how to copy / paste cellvalues and not formulas?
Posted: Mon Dec 03, 2018 2:48 am
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.
Re: Calc, how to copy / paste cellvalues and not formulas?
Posted: Mon Dec 03, 2018 3:56 pm
by Villeroy
Every week? Even if I had to do this every hour, I wouldn't need a macro.
Re: Calc, how to copy / paste cellvalues and not formulas?
Posted: Mon Dec 03, 2018 4:41 pm
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.
Re: Calc, how to copy / paste cellvalues and not formulas?
Posted: Mon Dec 03, 2018 8:10 pm
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.
Re: Calc, how to copy / paste cellvalues and not formulas?
Posted: Tue Dec 04, 2018 11:26 pm
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