Macro to copy cell doesnt work consistently

[byslexic]

LibreOffice Version: 7.6.7.2 (X86_64) / LibreOffice Community
Build ID: dd47e4b30cb7dab30588d6c79c651f218165e3c5
CPU threads: 8; OS: Windows 10.0 Build 22631;

This macro to copy a cell’s contents sometimes works, sometimes doesn’t.
I have 10 sheets in my workbook. The macro copies a cell in Sheet1 to the clipboard. Where am I going wrong? Where may it work differently depending on which sheet is currently selected or which cell? I’d like it to work the same no matter the current sheet or cell when the macro is called.
Thank you in advance.
(Edit -updated code for simplicity)

Sub BCopyToClipboard()
    Dim oSheet As Object, oCell As Object, oDispatcher As Object, oFrame As Object
    
    oSheet = ThisComponent.Sheets.getByName("Sheet1")
    ThisComponent.getCurrentController().setActiveSheet(oSheet)
    
    oCell = oSheet.getCellRangeByName("F1")
    
    ThisComponent.getCurrentController().select(oCell)

    oFrame = ThisComponent.getCurrentController().getFrame()
    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    
    oDispatcher.executeDispatch(oFrame, ".uno:Copy", "", 0, Array())
End Sub

[Villeroy]

Your code works for me. It always copies F1 from Sheet1.

[byslexic]

Thank you for investigating.
This now appears to be a possible bug with the Macro Editor.
I am now testing using the latest LO Version: 24.2.5.2 (X86_64) /
Build ID: bffef4ea93e59bebbeaf7f431bb02b1a39ee8a59

I’ve attached a sample ODS file with the macro included, and the pages with which it is intended to work.
DEMO3.ods (130.1 KB)

All sheets are blank except for the relevant ones, PM and PMhelpr.
The Copy button on sheet PM is linked to the macro as written above (with “Sheet1” replaced by “PMhelpr”).
Steps to reproduce:

• Load workbook, go to sheet PM
• Check or uncheck several boxes, to alter the numbers appearing in C17, which are mirrored in PMhelpr.F1 (the cell being copied).
• Click the Copy button, which will run the macro and always correctly copy the contents of PMhelpr.F1 to the clipboard. Verify by checking or unchecking other boxes to change the displayed values being copied to the clipboard, and pasting the clipboard into a handy empty space.
• Now open the Macro Editor and select the Demo3 document; navigate to the Sub BCopyToClipboard()
• place the cursor inside the text of the Sub, and Run the macro by clicking the green Run button, or press F5.
• Now change the values displayed again, then in the Macro Editor, click the green button again
• The clipboard will not have updated with the new values.

Further, I have had instances where sometimes stepping through the macro with the “Step Into” button will work, and sometimes not. There are also instances when the green Run button will work, but most often does not.

I will be very curious to know if anyone else encounters this behavior.
Thanks again in advance.

[Villeroy]

Sub BCopyToClipboard()
    Dim oSheet As Object, oCell As Object, oDispatcher As Object, oFrame As Object, view as Object
	view = ThisComponent.CurrentController
    oSheet = ThisComponent.Sheets.getByName("Sheet1")
    oCell = oSheet.getCellRangeByName("F1")
	view.select(oCell)

    oFrame = view.getFrame()
	oFrame.activate()
	oFrame.ContainerWindow.toFront()
    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    
    oDispatcher.executeDispatch(oFrame, ".uno:Copy", "", 0, Array())
End Sub

I’m not sure if frame.activate() or ContanerWindow.toFront makes the difference. Therefore I do both.

[fpy]

@byslexic

sometimes works, sometimes doesn’t

can u be more specific / descriptive ?

code looks ok. setActiveSheet probably useless.