Copy/Transpose

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Copy/Transpose

Post by det »

I know how to copy and paste special (transpose,without format) a range by "hand".
How can I do this by a macro? I could record the macro, but I was told not to and rather write my own code.
I got as far as to copy the range to another sheet and cell.
But how do I transpose the range without Formulas formats?
Det
Here is my Code:

Code: Select all

'     xxxxxxxx Copy a Range to same Sheet and to another Sheet  xxxxx

 Dim oSheet
Dim oRangeAddress 'The range to copy
Dim oCellAddress 'Destination address
oSheet = ThisComponent.Sheets(2)
oRangeAddress = oSheet.getCellRangeByName("A1:B5").getRangeAddress()
oCellAddress = oSheet.getCellByPosition(2, 7).getCellAddress() 
oSheet.copyRange(oCellAddress, oRangeAddress)
oSheet = ThisComponent.Sheets(3)
oCellAddress = oSheet.getCellByPosition(0, 9).getCellAddress() 
 oSheet.copyRange(oCellAddress, oRangeAddress)
Last edited by RoryOF on Thu Aug 17, 2017 9:21 pm, edited 1 time in total.
Reason: Added [Code] tags [RoryOF, Moderator]
OpenOffice 4.1.5 on Mac Sierra 10.13.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy/Transpose

Post by Zizi64 »

Here is an example code with usage of the Dispatcher (Recorded macro):

http://140.211.11.67/en/forum/viewtopic ... 9&p=286397


or:

https://ask.libreoffice.org/en/question ... o-problem/
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.
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Copy/Transpose

Post by det »

I looked through the recommended sites, but could not find how to transpose without a format. I guess I have to go through a loop and paste each cell separately.
I can do this (I have seen some code), then the final question becomes:
How do I paste without a format? Thanks for any help. Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy/Transpose

Post by Zizi64 »

How do I paste without a format?

Code: Select all

oCellRange.setDataArray(outDataArray)
The command setDataArray will not format the target cells.
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/Transpose

Post by Villeroy »

copyTransposedRangeSelection.ods
Transpose cell values by Basic code
(20.08 KiB) Downloaded 289 times
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
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Copy/Transpose

Post by det »

I entered your Line into the code but get error "Object Variable not set".
Here my Code again:

Code: Select all

Sub Main
 Dim oSheet
Dim oRangeAddress 'The range to copy
Dim oCellAddress 'Destination address
Dim oCellRange
Dim oCellCopyRange
Dim oCellPaste
oSheet = ThisComponent.Sheets(2)
'oCellCopyRange= oSheet.getCellRangeByPosition("0,0:3,3") does not work
oCellpaste=oSheet.getCellByPosition(4,4)
' oSheet.copyRange(oCellAddress, oRangeAddress)
oRangeAddress = oSheet.getCellRangeByName("A1").getRangeAddress()
oCellAddress = oSheet.getCellByPosition(2, 7).getCellAddress()
oCellRange= oSheet.getCellByPosition(2, 7)
  'xxxx The Copy is done here xxx 
oCellRange.setDataArray(outDataArray)  
oSheet.copyRange(oCellAddress, oRangeAddress)
oSheet = ThisComponent.Sheets(3)
oCellAddress = oSheet.getCellByPosition(0, 9).getCellAddress() 
 oSheet.copyRange(oCellAddress, oRangeAddress)
End Sub
Last edited by RoryOF on Thu Aug 17, 2017 9:22 pm, edited 1 time in total.
Reason: Added [Code] tags [RoryOF, Moderator]
OpenOffice 4.1.5 on Mac Sierra 10.13.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy/Transpose

Post by Zizi64 »

Please use the CODE tag in this forum for the code lists, and please upload your ods document with the embedded macro 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.
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Copy/Transpose

Post by det »

Here is the Code

Code: Select all

Codes.ods
OpenOffice 4.1.5 on Mac Sierra 10.13.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy/Transpose

Post by Zizi64 »

det wrote:Here is the Code

Code: Select all

Codes.ods

:?: :?: :?: :?: :?: :crazy:
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.
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Copy/Transpose

Post by det »

Sorry, I just figured out how to attach a file. Here it is.
Det
Attachments
Codes.ods
(13.64 KiB) Downloaded 246 times
OpenOffice 4.1.5 on Mac Sierra 10.13.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy/Transpose

Post by Zizi64 »

What is this???

When I wrote that "embed the code into a document", then I meant: Embed (copy) the code list into a Module of the Standard directory of the document. The "frames" in the Spreadsheet are unusable.

Basic Directiries and Modules.png
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
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Copy/Transpose

Post by RoryOF »

I embedded your previous code into [|Code] tags. Write your code (or Paste it) into your posting, then either place [quote][code] [/code][/quote] tags about it, or if using the Full report or Post Reply screens, select it and press the Code button
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Copy/Transpose

Post by det »

Sorry, I am Beginner. Does this work?
Attachments
Codes.ods
(14.96 KiB) Downloaded 233 times
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Copy/Transpose

Post by det »

RoryOF wrote:I embedded your previous code into [|Code] tags. Write your code (or Paste it) into your posting, then either place

Code: Select all

 
tags about it, or if using the Full report or Post Reply screens, select it and press the Code button
Thank you, i got a lot to learn here is a sample. I'll see if it worked.

Code: Select all

Sub Main
 Dim oSheet
Dim oRangeAddress 'The range to copy
Dim oCellAddress 'Destination address
Dim oCellRange
Dim oCellCopyRange
Dim oCellPaste
oSheet = ThisComponent.Sheets(2)
'oCellCopyRange= oSheet.getCellRangeByPosition("0,0:3,3")
oCellpaste=oSheet.getCellByPosition(4,4)
' oSheet.copyRange(oCellAddress, oRangeAddress)
oRangeAddress = oSheet.getCellRangeByName("A1").getRangeAddress()
oCellAddress = oSheet.getCellByPosition(2, 7).getCellAddress()
oCellRange= oSheet.getCellByPosition(2, 7)
  'xxxx The Copy is done here xxx 
'oCellRange.setDataArray(outDataArray)  
oSheet.copyRange(oCellAddress, oRangeAddress)
oSheet = ThisComponent.Sheets(3)
oCellAddress = oSheet.getCellByPosition(0, 9).getCellAddress() 
 oSheet.copyRange(oCellAddress, oRangeAddress) 
End Sub
OpenOffice 4.1.5 on Mac Sierra 10.13.1
Post Reply