OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Transpose Marco

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Genl Smit
Newbie
Newbie


Joined: 15 Sep 2010
Posts: 1

PostPosted: Wed Sep 15, 2010 7:45 am    Post subject: Transpose Marco Reply with quote

Hello,

I have a large amount of PDF's (Invoices) that I need to copy and paste in to OO so that I can go crazy on it SUM.

I select the data on the PDF and the copy it into my OO sheet, then the TextImport Wizard pops up. And instead of allowing my to copy the info onto one Row it makes my paste it into a colum.

Then I need to Select the Cells (19 or them) and then Past Special -> Transpose

I was hoping to create a Marco to do the Transposing for my. But the MacroRecorder only seems to use "Absulote" cell references.

Please help me change the Marco below to do the following:
1) Select 19 Cells below the current active Cell
2) Then Transpose it from Colmn format into a Row

The Problem is prob the following to lines in the code below:
args2(0).Value = "$C$11:$C$29"

args4(0).Value = "$C$11"



Code:

REM  *****  BASIC  *****

Sub Main

End Sub


sub TransposeMacro
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Format"
args1(0).Value = 1

dispatcher.executeDispatch(document, ".uno:PasteSpecial", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$C$11:$C$29"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$C$11"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(5) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Flags"
args5(0).Value = "A"
args5(1).Name = "FormulaCommand"
args5(1).Value = 0
args5(2).Name = "SkipEmptyCells"
args5(2).Value = false
args5(3).Name = "Transpose"
args5(3).Value = true
args5(4).Name = "AsLink"
args5(4).Value = false
args5(5).Name = "MoveMode"
args5(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())


end sub
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group