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

Keyboard macros or custom scripts

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

Postby exxy19 » Mon Dec 03, 2018 12:35 am

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   Expand viewCollapse view
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
exxy19
 
Posts: 5
Joined: Mon Dec 03, 2018 12:15 am

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

Postby Villeroy » Mon Dec 03, 2018 12:56 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby exxy19 » Mon Dec 03, 2018 12:58 am

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
exxy19
 
Posts: 5
Joined: Mon Dec 03, 2018 12:15 am

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

Postby Villeroy » Mon Dec 03, 2018 1:14 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Lupp » Mon Dec 03, 2018 1:35 am

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   Expand viewCollapse view
helperVar = sourceRange.GetDataArray
and then
Code: Select all   Expand viewCollapse view
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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby exxy19 » Mon Dec 03, 2018 1:43 am

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
exxy19
 
Posts: 5
Joined: Mon Dec 03, 2018 12:15 am

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

Postby Lupp » Mon Dec 03, 2018 2:48 am

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Villeroy » Mon Dec 03, 2018 3:56 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby exxy19 » Mon Dec 03, 2018 4:41 pm

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
exxy19
 
Posts: 5
Joined: Mon Dec 03, 2018 12:15 am

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

Postby Villeroy » Mon Dec 03, 2018 8:10 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby exxy19 » Tue Dec 04, 2018 11:26 pm

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   Expand viewCollapse view
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
exxy19
 
Posts: 5
Joined: Mon Dec 03, 2018 12:15 am


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 2 guests