[Solved] VBA Connection

Java, C++, C#, Delphi, ??? - Using the UNO bridges

[Solved] VBA Connection

Postby PieroRa » Wed Sep 09, 2020 6:36 pm

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   Expand viewCollapse view
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
Last edited by robleyd on Thu Sep 10, 2020 12:49 am, edited 2 times in total.
Reason: Moved topic from Macros and UNO API forum to External Programs; Tagged {Solved]
OpenOffice 3.1 on Windows
PieroRa
 
Posts: 7
Joined: Tue Sep 08, 2020 7:53 pm

Re: VBA Connection

Postby RoryOF » Wed Sep 09, 2020 7:02 pm

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.
Apache OpenOffice 4.1.9 on Xubuntu 20.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32400
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: VBA Connection

Postby JeJe » Wed Sep 09, 2020 10:11 pm

Try

Code: Select all   Expand viewCollapse view
            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   Expand viewCollapse view
MRI obL_Sheet


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

https://extensions.openoffice.org/en/pr ... ction-tool
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1497
Joined: Wed Mar 09, 2016 2:40 pm

Re: VBA Connection

Postby PieroRa » Wed Sep 09, 2020 11:30 pm

@JeJe
Thank you for your help, it's working now.
Bye
OpenOffice 3.1 on Windows
PieroRa
 
Posts: 7
Joined: Tue Sep 08, 2020 7:53 pm

Re: [Solved] VBA Connection

Postby PieroRa » Thu Sep 10, 2020 12:30 pm

@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
OpenOffice 3.1 on Windows
PieroRa
 
Posts: 7
Joined: Tue Sep 08, 2020 7:53 pm

Re: [Solved] VBA Connection

Postby JeJe » Thu Sep 10, 2020 12:42 pm

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   Expand viewCollapse view
obL_Sheet.getCellRangeByName("A1").string = "PAPERINO"
msgbox  obL_Sheet.getCellByPosition(0,0).string

Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1497
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] VBA Connection

Postby PieroRa » Thu Sep 10, 2020 12:57 pm

Thank you,
I forgot to add .string to function.
Bye
OpenOffice 3.1 on Windows
PieroRa
 
Posts: 7
Joined: Tue Sep 08, 2020 7:53 pm


Return to External Programs

Who is online

Users browsing this forum: No registered users and 4 guests