Calc Basic ScriptForge CopyToCell doesn't always work

I’m trying to insert a new row, copy formats from the row bellow, update formula in one column, and copy some cells from row bellow. If I try to call ScriptForge library, I don’t seem to have any methods to do so apart from CopyToCell or CopyToRange, and this only works if I run macro from withing the spreadsheet, now within window with macros.
1.) Is there a way to select particular sheet with ScriptForge lib?
I took basic example code from help and it looks like this:

   Sub Main
     	Dim sDoc as Object
     	sDoc = CreateScriptService("Calc")
     rem Following doesn't work
     '	sDoc = CreateScriptService("SFDocuments.Calc", "MyStat.ods")
     End Sub

2.) So to do remaining operations I used ThisComponent. Is there a way to do it elegantly with calling one lib only and less operations? (just capy range and clear unwanted values)

Sub Main
  Dim cDoc as Object
  Dim cSheet as Object
  cDoc = ThisComponent
  cSheet = cDoc.Sheets.GetByName("Sheet1")
  cSheet.Rows.insertByIndex(2, 1)
  cSheet.Rows(2).OptimalHeight = True
  cSheet.getCellRangeByPosition(0,2,0,4).FillAuto(2, 1)
  For i = 1 To 9
      cSheet.getCellByPosition(i, 2).CellStyle = cSheet.getCellByPosition(i, 3).CellStyle
  cSheet.getCellByPosition(3, 2).Value = cSheet.getCellByPosition(3, 3).Value
End Sub

My Version
Build ID: 20(Build:2)
CPU threads: 12; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-GB (en_US.UTF-8); UI: en-US
Calc: threaded

That is correct.

Is this what you want?

Set ui = CreateScriptService("UI")
Set oDoc = ui.GetDocument(ui.ActiveWindow)

ScriptForge is somewhat new and have not seen much benefit from it. Best to stick with uno API.

No answer for that.

1 Like

Thanks for explanation
I’ve noticed that I can Activate sheet with “Document” or “UI” Service but then I can’t seem to use properties and methods from “Calc” service namely CopyToCell, SetValue, SetArray…
For example I’d like to populate 3 cells

Dim dDoc as Object 
Set dDoc  = CreateScriptService("Document", "MyStat.ods")
dCel = dDoc.Range("B3:D3")
dDoc.SetValue(dCel,  Array (10, 12, 15))

Not sure if defining variable for Range should be String or Object, I tried both but it gives an error. SF lib seems very useful and efficient for writing a code, but having tough time with simple tasks.


Your code errors for me. Instead I did this:

Sub Main
    Dim dDoc as Object 
Rem Set dDoc  = CreateScriptService("Document", "MyStat.ods")
    Dim ui as Variant
    Set ui = CreateScriptService("UI")
    Set dDoc = ui.GetDocument(ui.ActiveWindow)
Rem dDoc is Active Sheet
Rem can also change Active Sheet
Rem dDoc.Activate("Sheet4")
    dDoc.SetValue("B3:D3", Array(10, 12, 15))
Rem Use next line instead of previous to go to a different sheet
Rem    dDoc.SetValue("Sheet4.B3:D3", Array(10, 12, 15))
End Sub

There are a lot of options and testing is mandatory. This is relatively new with little (and poor) documentation.

I strongly disagree with that. It is a kludge. If your are to learn something, go with the uno API. It pays off in the long haul!

1 Like

Yes it works this way, when starting from spreadsheet window, otherwise I specify name of the file, to run directly from the basic macro window.
I just couldn’t got Range to work, as is a property of SF_Calc service

1 Like

Already explained cannot run from Basic IDE. The library is not built to operate in that fashion.

Edit: I stand corrected. As stated:

Set dDoc  = CreateScriptService("Document", "MyStat.ods")

works from Basic IDE. Thank You!

That is because in SetValue it mus be a String. And dDoc.Range("B3:D3") is an object. Don’t know if SF has any conversion methods for this. Again, a kludge bit of software.

1 Like

The documentation specifies:

Additionally, the .Sheet and .Range properties return a reference that may be used as argument of a method called from another instance of the Calc service.

Testing shows this to work:

dDoc.CopyToRange(dDoc.Range("Sheet4.D4:F8"), "A11:C19")

Docs not always clear - test, test, test!

You are Right now is working with me also. I’ve read that documentation, and tested numerous of time with defining Range by defining dDoc via Calc, UI, Document Service…
It looks like Range returns as object so for methods like dDoc.ClearAll() wouldn’t work as it expects string for the range, whereas CopyToCell or CopyToRange accepts Range reference or String