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: @mikekaganski 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.
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.
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
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
@Lupp , ya, it is selecting exactly same range what i want. but unable to copy any thing. Not even from other place. It is just pasting which is already on its clipboard before running macro.
Forget the clipüboard. Better means available.
Quoting @HRM: “1 Selects hard-coded range ($Sheet1.$B$2:$D$4)”
Bad design: What will happen if someone decides to rename the sheet to “Project1_Records”?
Quoting @HRM: “3 Insert**(?)** three rows(?) below current position”
Good design? What if the data to copy are 4 rows, 5 columns next year?
Generally: Too much hard-coding.
@Lupp, & what if I use ms excel instead of Libreoffice?? You are loosing the point. The point is below simple macro code is unable to copy anything from anywhere.
dispatcher.executeDispatch(document, “.uno:Copy”, “”, 0, Array())Guys, I doesn’t got exact answer to my question. I think the problem is related to my PC only. Is it bug or else? how to find it? should I report bug?
But @Lupp’s answer give me another way. @peterwt, thanks for your effort.
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.
I am just talking about copy function, not about other things.
Do you mean dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array()) doesn’t work?
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.
Ya, got it. Thanks. At list doubt is cleared.
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.
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.
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.
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: 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!
yes, Win10x64