Ask Your Question
0

Copy won't work in Macro

asked 2017-08-19 07:50:47 +0100

HRM gravatar image

updated 2017-08-19 08:26:37 +0100

I have recorded a macro which copy from cell range & insert below same number of rows as in cell rage then paste the cell range in newly inserted rows. But the macro is paste the text which is copied before running it & while running.

Macro:

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

Sub Main

End Sub


sub Test
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 = "ToPoint"
args1(0).Value = "$Sheet1.$B$2:$D$4"

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

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

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

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$Sheet1.$B$5:$D$7"

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 = "SVD"
args5(1).Name = "FormulaCommand"
args5(1).Value = 0
args5(2).Name = "SkipEmptyCells"
args5(2).Value = false
args5(3).Name = "Transpose"
args5(3).Value = false
args5(4).Name = "AsLink"
args5(4).Value = false
args5(5).Name = "MoveMode"
args5(5).Value = 4

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

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$Sheet1.$B$2:$D$4"

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

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

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$Sheet1.$B$2"

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


end sub
edit retag flag offensive close merge delete

Comments

Unless you provide the macro in question, no one will be able to help you.

Mike Kaganski gravatar imageMike Kaganski ( 2017-08-19 07:55:14 +0100 )edit

sorry, I will update

HRM gravatar imageHRM ( 2017-08-19 08:06:51 +0100 )edit

Please check again.. I have done.

HRM gravatar imageHRM ( 2017-08-19 08:18:07 +0100 )edit

And now you need to clarify the intended algorithm.

Currently, your macro does the following steps:

  1. Selects hard-coded range ($Sheet1.$B$2:$D$4)
  2. Copies it to clipboard.
  3. Inserts one row below current position
  4. Goes to another hardcoded range ($Sheet1.$B$5:$D$7)
  5. Inserts from clipboard there
  6. Returns to $Sheet1.$B$2:$D$4
  7. Clears it
  8. Goes to $Sheet1.$B$2

What exactly are your intended steps? Where is the cursor when you run the macro? What is in clipboard?

Mike Kaganski gravatar imageMike Kaganski ( 2017-08-19 09:28:22 +0100 )edit

Actually I am working on another document which is our project log. We update our project log with new project details & make space at the top to add new project.

Above macro is just an example.
& 3rd step is INSERT 3 ROWS BELOW CURRENT POSITION. (for correction)

My actual problem is step 2, which is not working.
Instead it pasting the content which is on the clipboard before running the macro.
My cursor will be anywhere.

HRM gravatar imageHRM ( 2017-08-19 09:57:02 +0100 )edit

Even if i select the desired cell range before running the macro, it won't work.
But its only work if I manually copy the content & then start macro.

HRM gravatar imageHRM ( 2017-08-19 10:00:42 +0100 )edit

Quoting @HRM: "Above macro is just an example."
Yes. And it is a good example for the inaptness of recorded macros in such a case.

The premier point now: @Mike Kaganski involved with your question and listed the steps your recorded macro does thoroughly.
Why don't you list the intended steps as thoroughly?
Anyone trying to help here would need to get it clear. Otherwise he (f/m) will only waste time.

Help the helper if you want to get useful advice.

Lupp gravatar imageLupp ( 2017-08-19 11:32:41 +0100 )edit

In fact the relevant issue with recorded macro often is the "1. Selects hard-coded range ... For what reason was exactly $B$2:$D$4) selected when you recorded the macro? The action of selecting is never recorded! What range, in generalising terms should be put in this place? Is it the 'CurrentSelection' or a range otherwise defined? ...
Be precise about your intentions, please.

Lupp gravatar imageLupp ( 2017-08-19 11:39:48 +0100 )edit

My Exact requirement is:
1 Copy whatever text in Cell B2:D4
2 Insert 3 rows below
3 Paste in Cell B5:D7
4 Clear Cell B2:D4
5 Go to Cell B2

HRM gravatar imageHRM ( 2017-08-19 11:52:53 +0100 )edit

In other words
1 Selects hard-coded range ($Sheet1.$B$2:$D$4)
2 Copies it to clipboard. (Which is not working)
3 Inserts three row below current position
4 Goes to another hardcoded range ($Sheet1.$B$5:$D$7)
5 Inserts from clipboard there
6 Returns to $Sheet1.$B$2:$D$4
7 Clears it
8 Goes to $Sheet1.$B$2

HRM gravatar imageHRM ( 2017-08-19 11:54:06 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2017-08-19 13:29:59 +0100

Lupp gravatar image

updated 2017-08-19 13:38:10 +0100

Study the famous texts by Andrew Pitonyak if you don't understand the following code based on your current state of knowledge.

Sub callHelper()
REM In case of constants used instead of parameters for items that may change with time, 
REM a callHelper can avoid the need of maintenace measures in many places.
REM In this simple example only the constant address of the source-range needs maintenance.
REM Additional as-if parametrisation might be useful concerning probably wanted rows above and below the inserted rows.  
theDoc = ThisComponent
theSheet = theDoc.CurrentSelection.Spreadsheet
theSource= theSheet.GetCellRangeByName("B2:D4")
insertAndSomethingAsDescribedBy_HRM(theDoc, theSheet, theSource)
End Sub

Sub insertAndSomethingAsDescribedBy_HRM(pDoc, pSheet, pSource)
theWidth  = pSource.Columns.Count
theHeight = pSource.Rows.Count
pSheet.Rows.InsertByIndex(pSource.RangeAddress.EndRow + 1, theHeight)
pSource   = pSource.GetCellRangeByPosition(0, 0, theWidth - 1, theHeight - 1)
theStartTarget = pSheet.GetCellByPosition(pSource.RangeAddress.StartColumn, pSource.RangeAddress.EndRow + 1)
pSheet.CopyRange(theStartTarget.CellAddress, pSource.RangeAddress)
pDoc.CurrentController.Select(pSource)
pSource.ClearContents(com.sun.star.sheet.CellFlags.VALUE OR _
                      com.sun.star.sheet.CellFlags.STRING OR _
                      com.sun.star.sheet.CellFlags.ANNOTATION OR _
                      com.sun.star.sheet.CellFlags.HARDATTR OR _
                      com.sun.star.sheet.CellFlags.OBJECTS)
theFSel = pSource.GetCellByPosition(0, 0)
pDoc.CurrentController.Select(theFSel)                      
End Sub

I don't think the way you described the actions is actually practical. Nonetheless I followed the steps.
My advice: Simply insert the new rows above the data and select the new input position. No copy needed at all.

edit flag offensive delete link more

Comments

I am just talking about copy function, not about other things. Do you mean dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array()) doesn't work?

HRM gravatar imageHRM ( 2017-08-19 14:21:38 +0100 )edit

Your answer is solved my problem. But my mind is stuck on the question. Why copy is not working. I saw it working on youtube & on other source. I have followed same methods. but not worked for me.

HRM gravatar imageHRM ( 2017-08-19 14:34:29 +0100 )edit

@HRM:
See my comment on the answer by @peterwt .

Lupp gravatar imageLupp ( 2017-08-19 18:02:25 +0100 )edit

Ya, got it. Thanks. At list doubt is cleared.

HRM gravatar imageHRM ( 2017-08-19 18:40:26 +0100 )edit
0

answered 2017-08-19 17:30:37 +0100

peterwt gravatar image

It does state in the Options that Macro Recording "may be limited" and there are some things that don't work. In your case however there is no problem with the recording but it is the sequence of events which results it the Macro not working.

The sequence needs to be first to select the cell range to be copied and then insert the 3 rows below before copying the range. Then select the target range and paste. I have included a sample showing this working. It is the unmodified Macro recorded. In your macro just move the InsertRowsAfter before the Copy. For anyone reading this note that the feature to insert rows after is not available in earlier versions of LO.

C:\fakepath\MacRec.ods

edit flag offensive delete link more

Comments

In fact the recorded macro posetd in the question (after editing) works for me the way the OQer told later he intended. The sequence is correct so far.
Therefore I suspected the OQer actually wanted to work with variable ranges.

For what reason the recorded macro failed on the OQer's system I cannot tell. Tthe advice to write real code and some hints how to do so should not be bad anyway.

Lupp gravatar imageLupp ( 2017-08-19 18:00:59 +0100 )edit

Thanks,
But it wasn't help. I run your document/macro. but still it is not copying anything.
I think .uno:copy is not working in my PC.
Thanks for your effort.

HRM gravatar imageHRM ( 2017-08-19 18:16:53 +0100 )edit

@HRM: What's you OS?, OS version? Did you call the macro by clicking a button?
Your issue may be related to bug tdf#70883 Seems all the reporters and commenters were on Win systems.
(Appendix:)
Just tested on Win10 with LibO V5.4.0.3. The bug proved present!

Lupp gravatar imageLupp ( 2017-08-19 18:27:16 +0100 )edit

yes, Win10x64

HRM gravatar imageHRM ( 2017-08-19 18:34:26 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-08-19 07:50:47 +0100

Seen: 76 times

Last updated: Aug 19