Function to return Calc Document containing a Spreadsheet

[gurfle]

EDIT: Based on this, here seems to be THE way to do it:

Code: Select all

function WorkBookof(oWS as Object) as Object
  WorkBookof=oWS.getDrawPage().getForms().getParent()
End Function

and forget the kluge shown below .

After a little discussion on this topic here I came up with this somewhat awkward and limited solution to what appears to be, in the words of B Marcelly, "A missing facility of the API"

I am not versed enough at searching the OOo Feature/Bug tracking system to tell if adding a feature like this is something that is already an issue there. Does anyone here know?

If not, what do folks here think about proposing such a feature. In the work I do, I know it would be very handy to add the Calc Document containing a Spreadsheet as a property of ScTableSheetObj.

Code: Select all

function WorkBookof(oWS as Object) as Object
  ' Until the OOo API includes a feature to discover the Document (Calc "Workbook") containing a Spreadsheet
  ' This Function, inspired by JohnV's contribution to the discussion in
  ' http://user.services.openoffice.org/en/forum/viewtopic.php?f=45&t=33177#p152538
  ' could be used with the following
  ' Limitations:  Assumes that the Cell Z100 is modifyable
  Dim sDocURL as String, oDoc as Object, oFrame as Object, oDispatcher as Object
  oWS.getCellRangeByName("Z100").setformula("=CELL(""filename"";$A$1)")
  sDocURL=oWS.getCellRangeByName("Z100").getstring
  sDocURL=mid(sDocURL,2,instr(sDocURL,"#")-3)
  oDoc=stardesktop.loadComponentFromURL(sDocURL,"_default",0,Array())
  oFrame=oDoc.CurrentController.Frame
  oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
  oDispatcher.executeDispatch(oFrame, ".uno:Undo", "", 0, Array())
  WorkBookof=oDoc
End Function

[Villeroy]

No need to use a cell

Code: Select all

srv = createUnoService("com.sun.star.sheet.FunctionAccess")
sDocURL = srv.callFunction("CELL", Array("filename"))

But what if the file has not been stored yet?

Just another alternative I read on oooforum.org:

Code: Select all

Sub Snippet(Optional oInitialTarget As Object)
  Dim oSpreadsheet As Object
  Dim oDrawPage As Object
  Dim oForms As Object
  Dim oParent As Object

  oSpreadsheet = oInitialTarget.getSpreadsheet()
  oDrawPage = oSpreadsheet.getDrawPage()
  oForms = oDrawPage.getForms()
  
  oParent = oForms.getParent()
End Sub

Above Basic code has been generated by the MRI extension starting at the current cell as oInitialTarget. The form's parent gives the document model.