[Solved] VBA Connection

[PieroRa]

Hi to everybody, I'm newbie in this forum and Openoffice too.
I been able to transfer a code found in this forum to VBA (Autocad), in order to open a new Calc Document, and try to write or read something from it.
But I'm not able to find the part of code pointing to new sheet from Autocad VBA Application.
There are no problem in recalled function for retrieving GetLastUsedRow or GetLastUsedColumn, so starting from syntax used from both functions to access to spreadsheet I assumed to be able to use the syntax like as

obL_Sheet.Range("A1").Value = "PAPERINO"

but I got an error:
Error 438 "Object does not support this property or method"

So somebody could help me how to have access to Calc spreadsheet just opened by procedure, froma VBA external to Calc Macro ?

Code: Select all

Sub Main()
    'VARIABLES:
        Dim obL_Service_Manager As Object
        Dim obL_Core_Reflection As Object
        Dim obL_Desktop As Object
        Dim srL_Url As String
        Dim obL_Calc_Document As Object
        Dim obL_Sheet As Object
        Dim obL_Range_First_Column As Object
        Dim obL_Range_ToSort As Object
        Dim a1L_Arguments()
        Dim lnL_iLast_Row As Long
        Dim lnL_iLast_Column As Long
        'Dim csL_Sort_Field
       ' Dim obL_Sort_Field
        'Dim csL_Sort_Descriptor
        'Dim obL_Sort_Descriptor

    'PROCESS:
        Set obL_Service_Manager = CreateObject("com.sun.star.ServiceManager")
        Set obL_Core_Reflection = obL_Service_Manager.createInstance("com.sun.star.reflection.CoreReflection")
        Set obL_Desktop = obL_Service_Manager.createInstance("com.sun.star.frame.Desktop")

        'srL_Url = "file:///C:/Source/Data.ods"
       ' srL_Url = "file:///C:\Users\IO\Desktop\Data.ods"
        srL_Url = "private:factory/scalc"
'        C:\Users\IO\Desktop\Data.ods
        Set obL_Calc_Document = obL_Desktop.loadComponentFromURL(srL_Url, "_blank", 0, a1L_Arguments)
        Set obL_Sheet = obL_Calc_Document.Sheets.getByIndex(0)

        lnL_iLast_Column = GetLastUsedColumn(obL_Sheet)
        lnL_iLast_Row = GetLastUsedRow(obL_Sheet)
        obL_Sheet.Range("A1").Value = "PAPERINO"
End sub

Function GetLastUsedColumn(obL_Sheet) As Long
    Dim obL_Cursor As Object
    Set obL_Cursor = obL_Sheet.createCursor
    obL_Cursor.GotoEndOfUsedArea (True)
    GetLastUsedColumn = obL_Cursor.RangeAddress.EndColumn
End Function

Function GetLastUsedRow(obL_Sheet) As Long
    Dim obL_Cursor as Object
    Set obL_Cursor = obL_Sheet.createCursor
    obL_Cursor.GotoEndOfUsedArea (True)
    GetLastUsedRow = obL_Cursor.RangeAddress.EndRow
End Function

Thank you for support

[RoryOF]

The definitive texts on OpenOffice BASIC are those of Andrew Pitonyak, which may be downloaded from his site at
http://www.pitonyak.org/oo.php

The low level detail of connection to the innards of OpenOffice may be found by querying the OpenOffice API.

[JeJe]

Try

Code: Select all

            obL_Sheet.getCellRangeByName("A1").string = "PAPERINO"

There is a value property and a string property

https://wiki.openoffice.org/wiki/Docume ... s_directly

MRI can help you answer these questions. It can examine your object simply by calling

Code: Select all

MRI obL_Sheet 

and it will list what properties and methods are available to it.

https://extensions.openoffice.org/en/pr ... ction-tool

[PieroRa]

@JeJe
Thank you for your help, it's working now.
Bye

[PieroRa]

@JeJe
Hi, please could you help me a little bit more?
I been able to write to calc worksheet, but I'm not able to read value.
I tried to use the same reference,

obL_Sheet.getCellByPosition(0,0)

but doesn't work.
I saw other documentation on web including link you gave me but seems that the way it's that.
Any suggestion ?

Thank you

[JeJe]

Works for me. Your code in an OO Spreadsheet module creates a new Spreadsheet and this at the end gives a msgbox with "PAPERINO"

Code: Select all

obL_Sheet.getCellRangeByName("A1").string = "PAPERINO"
msgbox  obL_Sheet.getCellByPosition(0,0).string

[PieroRa]

Thank you,
I forgot to add .string to function.
Bye