Copy/Transpose

Creating a macro - Writing a Script - Using the API

Copy/Transpose

Postby det » Wed Aug 16, 2017 8:05 pm

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   Expand viewCollapse view
'     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
det
 
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Copy/Transpose

Postby Zizi64 » Wed Aug 16, 2017 8:52 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.5
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
Zizi64
Volunteer
 
Posts: 8094
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy/Transpose

Postby det » Thu Aug 17, 2017 6:29 am

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

Re: Copy/Transpose

Postby Zizi64 » Thu Aug 17, 2017 7:28 am

How do I paste without a format?


Code: Select all   Expand viewCollapse view
oCellRange.setDataArray(outDataArray)

The command setDataArray will not format the target cells.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.5
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
Zizi64
Volunteer
 
Posts: 8094
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy/Transpose

Postby Villeroy » Thu Aug 17, 2017 8:50 am

copyTransposedRangeSelection.ods
Transpose cell values by Basic code
(20.08 KiB) Downloaded 85 times
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: 26874
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy/Transpose

Postby det » Thu Aug 17, 2017 5:54 pm

I entered your Line into the code but get error "Object Variable not set".
Here my Code again:
Code: Select all   Expand viewCollapse view
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
det
 
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Copy/Transpose

Postby Zizi64 » Thu Aug 17, 2017 5:59 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.5
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
Zizi64
Volunteer
 
Posts: 8094
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy/Transpose

Postby det » Thu Aug 17, 2017 9:20 pm

Here is the Code
Code: Select all   Expand viewCollapse view
Codes.ods
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Copy/Transpose

Postby Zizi64 » Thu Aug 17, 2017 9:29 pm

det wrote:Here is the Code
Code: Select all   Expand viewCollapse view
Codes.ods



:?: :?: :?: :?: :?: :crazy:
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.5
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
Zizi64
Volunteer
 
Posts: 8094
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy/Transpose

Postby det » Thu Aug 17, 2017 9:30 pm

Sorry, I just figured out how to attach a file. Here it is.
Det
Attachments
Codes.ods
(13.64 KiB) Downloaded 68 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

Postby Zizi64 » Thu Aug 17, 2017 9:42 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.5
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
Zizi64
Volunteer
 
Posts: 8094
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy/Transpose

Postby RoryOF » Thu Aug 17, 2017 9:45 pm

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.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29101
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Copy/Transpose

Postby det » Thu Aug 17, 2017 10:35 pm

Sorry, I am Beginner. Does this work?
Attachments
Codes.ods
(14.96 KiB) Downloaded 62 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

Postby det » Fri Aug 18, 2017 1:40 am

RoryOF wrote:I embedded your previous code into [|Code] tags. Write your code (or Paste it) into your posting, then either place
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   Expand viewCollapse view
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
det
 
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 2 guests