[Solved-Partially] Simple copy to clipboard for Calc

[aivavia]

Comrads, please help me. I'm newbie in LO/OO (coz I used MSO before for a long period), but I have to work with OO right now by the situation.
So I got a big trouble with absolutely simple macro, which is 5 strings long in MSO, but absolutely ununderstandable in LO.
So, the situation. I have a CALC workbook with a lot of spreadsheets, and I need simple macro for copying values and formats from inactive spreadsheet (without changing window focus) to clipboard. Just to paste in some other workbooks as "values and formats". Nothing more.
ChatGPT offers me thousands scripts, and no one is workable at all.
I really cant understand, what is difficult to copy values to clipboard.
The only variant I made to work is

Code: Select all

Sub copy2clipboard()
 Dim oDoc As Object
    Dim oSheet As Object
    Dim oSourceRange As Object
    Dim oDispatcher As Object

    oDoc = ThisComponent
    oSheet = oDoc.Sheets.getByName("for_reports")
    oSourceRange = oSheet.getCellRangeByName("B2:AS270")

    'oDoc.CurrentController.Select(oSourceRange)

    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    oDispatcher.executeDispatch(oDoc.CurrentController.Frame, ".uno:Copy", "", 0, Array())
End Sub

But it changes my working spreadsheet focus to "for_reports" sheet. It is annoying.
Please help to newbie, don't push me to "READ THE MANUAL", coz my English is not very well, just post the right macro code, and I will try to understand, I swear.

[Zizi64]

I have a CALC workbook with a lot of spreadsheets, and I need simple macro for copying values and formats from inactive spreadsheet (without changing window focus) to clipboard.

Can you do it manually? I think it is impossible to COPY something to clipboard without selecting it. The command "copy" always works with the actual selection.

Of coure you can read informations of the the actual selection/view BEFORE the macro selects the desired cell range and uses the command Copy, and you can store that information temporarly, and finally you can restore the original state (the original view) AFTER copying the desired cell range.


You must study the API of the AOO/LO for this task. Start Andrew Pitonyak's free macro programming books.
https://www.pitonyak.org/oo.php

API: Application Programming Interface.
I suggest you to use one of the excellent Object Inspection Tools: MRI or XrayTool. Then you will able to list the existing properties of the programming objects of the AOO/LO.

[JeJe]

This will change the focus back - change Sheet3 to the name of the sheet you were on.

Code: Select all

thiscomponent.currentcontroller.select Thiscomponent.sheets.getbyname("Sheet3")

[aivavia]

think it is impossible to COPY something to clipboard without selecting it.

After studying tons of different examples here on the forum, I came to the same conclusion. However, this is not possible only for AOO LO, Microsoft Excel copes with this task easily and naturally. And this is a very annoying feature, but I believe there is a way to make that.

This will change the focus back - change Sheet3 to the name of the sheet you were on.

Thank you, I will use this crutch for a while until there is a better way to do it.

[aivavia]

This will change the focus back - change Sheet3 to the name of the sheet you were on.

Code: Select all

thiscomponent.currentcontroller.select Thiscomponent.sheets.getbyname("Sheet3")

Changed this line to

Code: Select all

thiscomponent.currentcontroller.setActiveSheet Thiscomponent.sheets.getbyname("Sheet3")

And it works more better, than Select. Thank you.
One more trouble appeared. This method copies all cell data (formulas and color marks). It doesn't suit to me. I need only values and value formats. Sure I can paste as "values and formats" but color-marked cells are unacceptable in final document to paste.

[JeJe]

Use

Code: Select all

osourcerange.getDataArray

And .setDataArray for inserting

[aivavia]

Use

Code: Select all

osourcerange.getDataArray

And .setDataArray for inserting

I cant use .setDataArray coz I should paste data to another document, and the area to paste from the clipboard is not defined by strict positions (which is the mandatory for setDataArray method).

[JeJe]

Use .setDataArray to put the data on an empty sheet then there won't be any other formatting and you can copy it from there.

[aivavia]

Use .setDataArray to put the data on an empty sheet then there won't be any other formatting and you can copy it from there.

I used a temporary sheet for this method. Here is the code

Code: Select all

Sub CopyCellsToClipboard()
    Dim oDoc As Object
    Dim oSheet As Object
    Dim oSourceRange As Object
    Dim oTempSheet As Object
    Dim oTempRange As Object
    Dim oDispatcher As Object

    oDoc = ThisComponent
    oSheet = oDoc.Sheets.getByName("report")
    oSourceRange = oSheet.getCellRangeByName("B2:AS270")

    ' make temp sheet
    oTempSheet = oDoc.createInstance("com.sun.star.sheet.Spreadsheet")
    oDoc.Sheets.insertByName("TempSheet", oTempSheet)
    oTempSheet = oDoc.Sheets.getByName("TempSheet")

    ' copy data to temp sheet
    oTempRange = oTempSheet.getCellRangeByName("B2:AS270")
    oTempRange.setDataArray(oSourceRange.getDataArray())

    ' change formulas to values
    oTempRange.FormulaArray = oTempRange.DataArray

    ' select temp range
    oDoc.CurrentController.Select(oTempRange)

    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    oDispatcher.executeDispatch(oDoc.CurrentController.Frame, ".uno:Copy", "", 0, Array())

    ' remove temporary sheet
    oDoc.Sheets.removeByName("TempSheet")
End Sub

It is less suitable, than the macro from my initial post. Sorry to say that.

[JeJe]

What program/where are you pasting it and what format options do you have for pasting it there?

[aivavia]

I'm pasting it in different Calc documents, formats are mixed. There are digits and dates and time only and "text only" formats. And the place to paste differs each time, that's why I paste with "CTRL+SHIFT+V" values and formats.
Tried to make a macro to "paste" only values and formats for quick action, but got a fiasco.
Here what I made for "paste". I think its unusable.

Code: Select all

sub Macro1
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 = "$A$1"

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

rem ----------------------------------------------------------------------
dim args2(5) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Flags"
args2(0).Value = "SVDT"
args2(1).Name = "FormulaCommand"
args2(1).Value = 0
args2(2).Name = "SkipEmptyCells"
args2(2).Value = false
args2(3).Name = "Transpose"
args2(3).Value = false
args2(4).Name = "AsLink"
args2(4).Value = false
args2(5).Name = "MoveMode"
args2(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args2())
end sub

This code breaks my document at all. (merged cells, formats etc)

[Zizi64]

Please upload one otr two sample file(s) here to demonstrate the data types and formatting strucrures of the Source and the Target cell ranges what you want to use.