Calc Basic ScriptForge CopyToCell doesn't always work

zvegelj · January 21, 2022, 9:25am

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
     	GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
     	Dim sDoc as Object
     	sDoc = CreateScriptService("Calc")
     rem Following doesn't work
     '	sDoc = CreateScriptService("SFDocuments.Calc", "MyStat.ods")
     	sDoc.CopyToCell("Sheet1.A4:P4","Sheet1.A3")
     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
  Next
  cSheet.getCellByPosition(3, 2).Value = cSheet.getCellByPosition(3, 3).Value
End Sub

My Version
Version: 7.2.5.2.0+
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
Thanks

Ratslinger · January 22, 2022, 1:46am

That is correct.

Is this what you want?

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

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

No answer for that.

zvegelj · January 22, 2022, 8:03am

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")
dDoc.Activate("Sheet1")
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.

Ratslinger · January 22, 2022, 5:06pm

Hello,

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
    GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
    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!

zvegelj · January 22, 2022, 5:29pm

Ratslinger:

Hello,

Sub Main
    Dim dDoc as Object 
Rem Set dDoc  = CreateScriptService("Document", "MyStat.ods")
    Dim ui as Variant
    GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
    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

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

Ratslinger · January 22, 2022, 5:43pm

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

Ratslinger · January 22, 2022, 6:54pm

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!

zvegelj · January 22, 2022, 7:47pm

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
Thanks