[Solved] Calc: move, copy paste entire column

The Application Programming Interface and the OASIS Open Document Format

[Solved] Calc: move, copy paste entire column

Postby marco_lazzarini » Tue Jan 25, 2011 3:36 pm

How can i move one or more columns from one address to another?
(maybe by cutting & paste the columns) to achieve the same result of the following code for excel:

With osheet
.Columns(sFrom).Select ' sFrom can be "AA:AC"
.Application.Selection.Cut
.Columns(sTo).Select ' sTo can be "D:D"
.Application.Selection.Insert XlInsertShiftDirection.xlShiftToRight
End With

I just need to cut away one (or more) column from column, let's say "AA:AA"
and insert it in column "D:D" shifting right the already existing columns.
Thank everyone in advance for any help
Last edited by marco_lazzarini on Fri Jan 28, 2011 1:24 pm, edited 1 time in total.
OpenOffice.org 3.2.1, Win Vista
marco_lazzarini
 
Posts: 2
Joined: Tue Jan 25, 2011 12:25 pm

Re: Calc: move, copy paste entire column

Postby FJCC » Fri Jan 28, 2011 6:10 am

Here is one example. Notice that you have to track how the columns shift after you insert the new ones.
Code: Select all   Expand viewCollapse view
oSheet = ThisComponent.Sheets.getByName("Sheet1")
'insert three new columns
InsertRange = oSheet.getCellrangeByName("D1:F65536")
InsertRange_RangeAddress = InsertRange.RangeAddress
oSheet.insertCells(InsertRange_RangeAddress, com.sun.star.sheet.CellInsertMode.COLUMNS)

oSourceRange = oSheet.getCellrangeByName("AD1:AF65536") 'The insert above moves the columns that were AA:AC to AD:AF
oSourceRangeAddress = oSourceRange.RangeAddress
oTargetCell = oSheet.getCellrangeByName("D1")
oTargetCell_CellAddress = oTargetCell.CellAddress
oSheet.moveRange(oTargetCell_CellAddress, oSourceRangeAddress)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7540
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Calc: move, copy paste entire column

Postby marco_lazzarini » Fri Jan 28, 2011 1:22 pm

Thanks FJCC for your kyndly reply.
I found an alternative solution that has a little problem: you can only move one column per time.
Still i didn't find the way to move a range of contiguous columns.
Here it is:

Private Sub MoveColumn(ByRef sFrom As String, ByRef sTo As String, ByRef osheet As Object)
Dim oColumn As Object
Dim oColumnAddress As Object
Dim oTargetCol As Object
Dim oTargetColAddress As Object ' com.sun.star.Table.CellAddress
Dim iDsp As Integer

If osheet.Columns.getByName(sTo).getRangeAddress().StartColumn < osheet.Columns.getByName(sFrom).getRangeAddress().StartColumn Then
' If i move col from following to previuos address (i.e. swap right to left positions), following column (where source is) are renumbered. I need a displacement that keep in mind this
iDsp = 1
End If

' Determine column where to move (Target)
Set oTargetCol = osheet.Columns.getByName(sTo)
' Insert an empty col
osheet.getColumns.insertByIndex oTargetCol.getRangeAddress().StartColumn, 1
' get object "CellAddress" of target col
Set oTargetColAddress = osheet.getCellByPosition(oTargetCol.getRangeAddress().StartColumn - 1, 0).getCellAddress()

' Determine column to be moved (Source)
Set oColumn = osheet.Columns.getByIndex(osheet.Columns.getByName(sFrom).getRangeAddress().StartColumn + iDsp)
' get object "RangeAddress" source col
Set oColumnAddress = oColumn.getRangeAddress()

' move the col
osheet.moveRange oTargetColAddress, oColumnAddress
' delete the moved (now empty) col
osheet.getColumns.removeByIndex osheet.Columns.getByName(sFrom).getRangeAddress().StartColumn + iDsp, 1
End Sub
OpenOffice.org 3.2.1, Win Vista
marco_lazzarini
 
Posts: 2
Joined: Tue Jan 25, 2011 12:25 pm

Re: [Solved] Calc: move, copy paste entire column

Postby smartmarti » Sat Oct 12, 2013 8:56 pm

Did this work?
still confused how to make a macro for moving a column by copying and paste.. :(
Open office to 4.0.1
OSX version 10.8.4
smartmarti
 
Posts: 5
Joined: Sat Oct 12, 2013 8:52 pm

Re: [Solved] Calc: move, copy paste entire column

Postby smartmarti » Sat Oct 12, 2013 9:58 pm

I need to do something quite simple. I need to move the columns in the top 3 rows as the one in the 3 row below.
Any suggestion?
Attachments
test macro.ods
(8.46 KiB) Downloaded 245 times
Open office to 4.0.1
OSX version 10.8.4
smartmarti
 
Posts: 5
Joined: Sat Oct 12, 2013 8:52 pm

Re: [Solved] Calc: move, copy paste entire column

Postby FJCC » Sun Oct 13, 2013 5:31 am

I recorded a macro that does what I think you want. Referring to the attached file, that data are initially arranged like the example on Sheet1 and you want them to end up looking like the data on Sheet2. Run the macro ShuffleCols while Sheet1 is the active sheet and it will change to have the data arranged like Sheet2.
Attachments
ShuffleCols.ods
(10.38 KiB) Downloaded 491 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7540
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Calc: move, copy paste entire column

Postby smartmarti » Sun Oct 13, 2013 2:45 pm

IT WORKS!!!!!
thank you so much!!!!!! You are a genius...
:D I am so grateful... you saved my day
Open office to 4.0.1
OSX version 10.8.4
smartmarti
 
Posts: 5
Joined: Sat Oct 12, 2013 8:52 pm

Re: [Solved] Calc: move, copy paste entire column

Postby smartmarti » Sun Oct 13, 2013 3:17 pm

Your macro works wonderfully.

But I'd like to integrate that with another one I already have.
In the attached doc my starting point is Sheet 1 (lots of columns) so I wrote a macro to remove some of them (if you run Remove columns macro you'll get exactly what's on Sheet 2 now - the one we were starting on the previous post). And then if at that point you run your macro it doesn't work anymore.. it doesn't give me the expected result which should be the one in Sheet 3.
I have no idea how to fix this. I never did any programming in my life... it might be just a stupid correction to integrate both macros.. but I can't do by myself :crazy:

thanks agin for all your help!
Attachments
ShuffleCols adding another macro.ods
(10.92 KiB) Downloaded 166 times
Open office to 4.0.1
OSX version 10.8.4
smartmarti
 
Posts: 5
Joined: Sat Oct 12, 2013 8:52 pm

Re: [Solved] Calc: move, copy paste entire column

Postby FJCC » Sun Oct 13, 2013 4:23 pm

In your initial example the initial pattern was positioned starting in column E. After your RemoveCols macro that pattern starts in column A. I have recorded a new macro called ShuffleCols_2 that works after RemoveCols has run.
Attachments
ShuffleCols adding another macro_FJCC.ods
(12.01 KiB) Downloaded 306 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7540
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Calc: move, copy paste entire column

Postby smartmarti » Mon Oct 14, 2013 12:21 am

Amazing!!!!

I honestly don't know how to say thank you! This is going to be so useful!

thanks a lot again
Open office to 4.0.1
OSX version 10.8.4
smartmarti
 
Posts: 5
Joined: Sat Oct 12, 2013 8:52 pm


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 1 guest