[Solved] Copy using named ranges
[Solved] Copy using named ranges
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
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
Re: copy using named ranges
The oSrc obj has a RangeAddress property which you can use directly in the call of copyRange()
If oDest is a single cell, then it will have a CellAddress property and you can use
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
to copy the cell values and text, but not the formatting.
Code: Select all
oSheet.copyRange( oAddress, oSrc.RangeAddress )
Code: Select all
oSheet.copyRange( oDest.CellAddress, oSrc.RangeAddress )
If oSrc and oDest are the same size, you can skip copyRange altogether and use
Code: Select all
oDest.DataArray = oSrc.DataArray
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: copy using named ranges
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.
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
Re: copy using named ranges
"Transpose":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.
So far, this was not mentioned...
My opinion: it is not hypercomplex and not inconsistent.This OO interface seems hypercomplex and 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.
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.
Re: copy using named ranges
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).
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy using named ranges
Hallo
simple enough to do this stuff in python:
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
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Copy using named ranges
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).karolus wrote:Hallo
simple enough to do this stuff in python:
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy using named ranges
The tricky bit is to grasp the the *lazy_unpacking-operator in front of `source_data`Villeroy wrote: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).karolus wrote:Hallo
simple enough to do this stuff in python:
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: copy using named ranges
I meant Hypercomplex/inconsistent relative to the Excel/VBA paradigm...Zizi64 wrote:"Transpose":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.
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
My opinion: it is not hypercomplex and not inconsistent.This OO interface seems hypercomplex and 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.
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
Re: Copy using named ranges
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy using named ranges
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.
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.
Re: Copy using named ranges
OK, thanks!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.
- 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
Re: Copy using named ranges
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
Thanks for the help
OpenOffice 3.1 on Windows 7