# Copy won't work in Macro [closed]

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 ----------------------------------------------------------------------
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).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 reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-10-13 11:29:11.774991

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

( 2017-08-19 07:55:14 +0200 )edit

sorry, I will update

( 2017-08-19 08:06:51 +0200 )edit

Please check again.. I have done.

( 2017-08-19 08:18:07 +0200 )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?

( 2017-08-19 09:28:22 +0200 )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.

( 2017-08-19 09:57:02 +0200 )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.

( 2017-08-19 10:00:42 +0200 )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.

( 2017-08-19 11:32:41 +0200 )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? ...

( 2017-08-19 11:39:48 +0200 )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

( 2017-08-19 11:52:53 +0200 )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

( 2017-08-19 11:54:06 +0200 )edit

Sort by » oldest newest most voted

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
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
pSource   = pSource.GetCellRangeByPosition(0, 0, theWidth - 1, theHeight - 1)
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.

more

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

( 2017-08-19 14:21:38 +0200 )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.

( 2017-08-19 14:34:29 +0200 )edit

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

( 2017-08-19 18:02:25 +0200 )edit

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

( 2017-08-19 18:40:26 +0200 )edit

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

more

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.

( 2017-08-19 18:00:59 +0200 )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.

( 2017-08-19 18:16:53 +0200 )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!

( 2017-08-19 18:27:16 +0200 )edit

yes, Win10x64

( 2017-08-19 18:34:26 +0200 )edit