Copy won't work in Macro

English
, , , ,
#1

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
Call an extension in LibreWriter Macro
Macro to use relative references
Buttons lose execute action
#2

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

#3

sorry, I will update

#4

Please check again… I have done.

#5

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?

#6

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?

#7

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.

#8

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.

#9

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.

#10

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.

#11

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

#12

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

#13

@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.

#14

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.

#15

@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())


I am able to handle the cases like rename of sheet & 3 rows to 4rows & 5columns.
Can we talk about my exact concern? range selection is not an issue.

#16

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.

#17

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.

#18

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

#19

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.

#20

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

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.