[Solved] Copy using named ranges

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
kootzie
Posts: 10
Joined: Fri Feb 03, 2017 3:13 am

[Solved] Copy using named ranges

Post by kootzie »

There seems to be a gap between getting the cells of a named range:
oSrc = thisComponent.NamedRanges.getByName("NewCustomerTr").getReferredCells
oDest = thisComponent.NamedRanges.getByName("InsertCustomer").getReferredCells

and then using those to copy from oSrc to oDest

This function:
oSheet.copyRange( oAddress, oRange )

requires all the gory intestines to be explicitly defined:
oRange As New com.sun.star.table.CellRangeAddress

' A2:B3 on the first sheet
oRange.Sheet = 0
oRange.StartColumn = 1
oRange.StartRow = 1
oRange.EndColumn = 2
oRange.EndRow = 2

Dim oAddress As New com.sun.star.table.CellAddress

' A1 on the first sheet
oAddress.Sheet = 0
oAddress.Column = 0
oAddress.Row = 0

Surely there's a way to convert between the result of GetReferredCells and CellRangeAddress
Last edited by kootzie on Sun Feb 05, 2017 9:27 pm, edited 1 time in total.
OpenOffice 3.1 on Windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: copy using named ranges

Post by FJCC »

The oSrc obj has a RangeAddress property which you can use directly in the call of copyRange()

Code: Select all

oSheet.copyRange( oAddress, oSrc.RangeAddress )
If oDest is a single cell, then it will have a CellAddress property and you can use

Code: Select all

oSheet.copyRange( oDest.CellAddress, oSrc.RangeAddress )
If oDest is not a single cell you can use its getCellByPosition(0,0) method to get the top left cell of the range and use its CellAddress in the copy.
If oSrc and oDest are the same size, you can skip copyRange altogether and use

Code: Select all

oDest.DataArray = oSrc.DataArray
to copy the cell values and text, but not the formatting.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
kootzie
Posts: 10
Joined: Fri Feb 03, 2017 3:13 am

Re: copy using named ranges

Post by kootzie »

Yes, thank you. That semi worked.
When I used copyrange, I ended up with REF errors since I set up a TRANSPOSE array since I couldn't figure out how to do a paste-transpose which is what the existing VBA code does. This OO interface seems hypercomplex and inconsistent.

I just set up dest to be the same size as src and just did the second option dest.dataarray = src.dataarray.

Obviously there is some subtle difference in how the data is copied.
OpenOffice 3.1 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: copy using named ranges

Post by Zizi64 »

When I used copyrange, I ended up with REF errors since I set up a TRANSPOSE array since I couldn't figure out how to do a paste-transpose which is what the existing VBA code does.
"Transpose":
So far, this was not mentioned...

This OO interface seems hypercomplex and inconsistent.
My opinion: it is not hypercomplex and not inconsistent.
You must study the API functions. Start with Andrew Pitonyak' free books, and the online API descriptions and code snippet examples of the AOO ans LO.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: copy using named ranges

Post by Villeroy »

Record a macro with paste-special and "Transpose".
Or use the com.sun.star.sheet.FunctionAccess service with the TRANSPOSE function and then range.setDataArray(range.getDataArray()) which effectively converts formulas to values (strings and numbers).
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
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Copy using named ranges

Post by karolus »

Hallo

simple enough to do this stuff in python:

Code: Select all

def copytransposed():       
    doc = XSCRIPTCONTEXT.getDocument()
    name_ranges = doc.NamedRanges
    source_data = name_ranges.NewCustomerTr.ReferredCells.DataArray 
    transposed = tuple(zip( *source_data)) # transpose simply with python-runtime
    destination = name_ranges.InsertCustomer.ReferredCells
    destsheet = destination.Spreadsheet
    cursor = destsheet.createCursorByRange(destination)
    cursor.collapseToSize(len(transposed[0]), len(transposed))
    cursor.DataArray = transposed
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy using named ranges

Post by Villeroy »

karolus wrote:Hallo

simple enough to do this stuff in python:
Finally I understand what Python's built-in zip function does. It is named after the zipper (one from the left, one from the right).
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
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Copy using named ranges

Post by karolus »

Villeroy wrote:
karolus wrote:Hallo

simple enough to do this stuff in python:
Finally I understand what Python's built-in zip function does. It is named after the zipper (one from the left, one from the right).
The tricky bit is to grasp the the *lazy_unpacking-operator in front of `source_data`
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
kootzie
Posts: 10
Joined: Fri Feb 03, 2017 3:13 am

Re: copy using named ranges

Post by kootzie »

Zizi64 wrote:
When I used copyrange, I ended up with REF errors since I set up a TRANSPOSE array since I couldn't figure out how to do a paste-transpose which is what the existing VBA code does.
"Transpose":

So far, this was not mentioned...

The problem is not specific to Transpose. Any references to cells in the source will yield a #REF error
it looks like the =references are not dereferenced before copying
the raw "formula" is copied to the new sheet, and then it can't dereference it there - even though
there is data in the same source cell-range in the dest sheet as in the source sheet
I am pretty sure this is "not like VBA" which more closely emulates the spreadsheet copy/paste paradigm
BlahBlahCells.Copy and then
and do the sensible dereferencing

Also, even more bizarre, when I switch between copying from the =TRANSPOSE range in the source
to a range of cells doing a "manual transpose" i.e. cell-by-cell column-to-row references
The TRANSPOSE source will overwrite the #REF errors in the dest left by the "manual transpose" source
whereas the "manual transpose" source will not. I have to clear the row before it will transfer.
That just feels like layers of kludge-kruft. It shouldn't matter what is in the source cells
This OO interface seems hypercomplex and inconsistent.
My opinion: it is not hypercomplex and not inconsistent.
You must study the API functions. Start with Andrew Pitonyak' free books, and the online API descriptions and code snippet examples of the AOO ans LO.
I meant Hypercomplex/inconsistent relative to the Excel/VBA paradigm...
Back, at the turn of the century, I banged together a simple scale-ticket application for our local seed cleaning coop.
I was surprised as hell that they are still using it, and want it working now that the old computer died and they
don't have excel. I just hoped to do a straightline conversion of some basic operations, but it seems one has to get below the DNA-level to the sub-atomic scale in starbasic to replicate the most simple functionality
OpenOffice 3.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy using named ranges

Post by Villeroy »

This whole office suite with 4 macro languages has a footprint of 400 MB. Its UNO API is just a very thin layer whereas VBA is like a second user interface. UNO is not for amateurs.
In addition, you found a gap in the API. Paste-special with its options is not implemented.
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
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy using named ranges

Post by Zizi64 »

kootzie, please upload your example .ods file to see the exact structure of the source and the destination celrange. And please upload the original VBA code.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
kootzie
Posts: 10
Joined: Fri Feb 03, 2017 3:13 am

Re: Copy using named ranges

Post by kootzie »

Zizi64 wrote:kootzie, please upload your example .ods file to see the exact structure of the source and the destination celrange. And please upload the original VBA code.
OK, thanks!
Attachments
vscpt3NoDat2.ods
New OO file, selectively copy/pasted/data-stripped half-assed macro-translated
(17.58 KiB) Downloaded 233 times
ScaleTicket_kk_2017NoDat2.xls
Original Excel file stripped down for size
(56.5 KiB) Downloaded 232 times
OpenOffice 3.1 on Windows 7
kootzie
Posts: 10
Joined: Fri Feb 03, 2017 3:13 am

Re: Copy using named ranges

Post by kootzie »

I ended up just using the old macro code as a guide to re-record new macros and fiddle around to make them functional.
Thanks for the help
OpenOffice 3.1 on Windows 7
Post Reply