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

SOLVED:duplicate rows - macro not translating data correctly

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
davemorley
General User
General User


Joined: 28 Jul 2012
Posts: 12

PostPosted: Sun Jul 29, 2012 4:39 pm    Post subject: SOLVED:duplicate rows - macro not translating data correctly Reply with quote

Hi all.

I have a macro (below) that is doing mostly what I need but I am getting formula translation errors.

I would have thought that if I have a formula in a cell that reads =AD9*$AK$31 ($AK$31 being absolute) that when the row is duplicated the formulae should remain as =AD9*$AK$31. But it is changing the formula to =AD8*$AK$30. (of course changing to AD8 is correct, but the $AK$31 should stay as is?)

Is this expected behaviour? How do i get around this?

Also, the macro uses a simple "InsertRows" statement. This places the new row above the current selected one. Can I change this behaviour to add the new row below the current one?

Code:

sub rowduplicate
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Sel"
args1(0).Value = false
dispatcher.executeDispatch(document, ".uno:GoToStartOfRow", "", 0, args1())
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Sel"
args2(0).Value = true
dispatcher.executeDispatch(document, ".uno:GoToEndOfRow", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoToStartOfRow", "", 0, args2())
end sub


Thanks for any advise.


Last edited by davemorley on Sun Jul 29, 2012 5:47 pm; edited 1 time in total
Back to top
View user's profile Send private message
davemorley
General User
General User


Joined: 28 Jul 2012
Posts: 12

PostPosted: Sun Jul 29, 2012 5:37 pm    Post subject: Reply with quote

Ive worked out adding the row below the current one;


dispatcher.executeDispatch(document, ".uno:GoToEndOfRow", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoToStartOfRow", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

But still have the formula translation issues.

Any advise?
Back to top
View user's profile Send private message
davemorley
General User
General User


Joined: 28 Jul 2012
Posts: 12

PostPosted: Sun Jul 29, 2012 5:46 pm    Post subject: Reply with quote

SOLVED:

Sorry for the bandwidth.

I ended up replacing the absolute reference using the "name" function

So I named the cell "AK31" to "costWeighting"

Then the actual cell formula became


AD9*costWeighting

Duplicating the row with the macro now works as expected.

thanks
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 Macros and API 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