[Solved] VBA Connection
Posted: 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 ?
Thank you for support
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