is there any method to create an interconnection between AutoCAD and LibreOffice Calc via VBA? I mean similar to AutoCAD and Excel using VBA.
Solved! Go to Solution.
Solved by grobnik. Go to Solution.
Solved by grobnik. Go to Solution.
hi @jtm2020hyo ,
I'm interest to the argument too and I found this site
https://ask.libreoffice.org/en/question/18/visual-basic-to-calc/
where it's indicated how to have access to Open Office Calc.
Original version VBA:
Dim oExcel As Excel.Application Dim oBook As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oApp As Excel.Application
oExcel = CreateObject("Excel.Application")
oApp = GetObject(, "Excel.Application")
oBook = oExcel.Workbooks.Add(Type.Missing)
oSheet = oBook.Worksheets(1)
If this statement means to create a new empty Excel file, then the equivalent in LibreOffice BASIC is
sURL = "private:factory/scalc"
objCalc = StarDesktop.loadComponentFromURL(sURL, "_blank", 0, Array())
It will create an empty Calc file, acessible by objCalc object.
Sincerely I never tested, in addition seems that code shall be developed inside Calc application by Macro tools.
So I'll try to do some tests and I'll investigate more, in my case the requirements it's to write into Calc application from Autocad (like Excel), so I have to find the way to create an object linked to Open Office Calc.
I read also about OLE Object, but again I never tested.
I'll keep you informed, please do the same if you have news on the argoument.
Thank you
Hi @jtm2020hyo after a lot of test and investigation I found the way to open a new empty Calc Spreadsheet and write on it in a specific cell address.
I don't know if this can help you, but below VBA code written inside Autocad VBA development area.
Let us know
Bye
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
'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 = "private:factory/scalc"
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.getCellRangeByName("A1").String = "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
thanks a lot for your effort MR. @grobnik
...
obL_Sheet.getCellRangeByName("A1").String = "PAPERINO"
...
I need both, write in Calc since AutoCAD, and draw a line since Calc to AutoCAD, this help a bit.
... are you writing in Base and UNO? if yes, could help me to translate this code to Base + UNO, please?
Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Option Explicit
Sub DrawRectangle()
Dim AutocadApp As Object
Dim AutocadDoc As Object
Dim RectArray(0 To 9) As Double
Dim Rectangle As Object
'****** Launch Autocad application****
On Error Resume Next
Set AutocadApp = GetObject(, "Autocad.application")
On Error GoTo 0
If AutocadApp Is Nothing Then
Set AutocadApp = CreateObject("Autocad.application")
AutocadApp.Visible = True
End If
''****Point 1****
RectArray(0) = 0
RectArray(1) = 0
''****Point 2****
RectArray(2) = ActiveSheet.Range("C6")
RectArray(3) = 0
''****Point 3****
RectArray(4) = ActiveSheet.Range("C6")
RectArray(5) = ActiveSheet.Range("C7")
''****Point 4****
RectArray(6) = 0
RectArray(7) = ActiveSheet.Range("C7")
''****Point 5****
RectArray(8) = 0
RectArray(9) = 0
''****Draw rectangle****
On Error Resume Next
Set AutocadDoc = AutocadApp.ActiveDocument
On Error GoTo 0
If AutocadApp Is Nothing Then
Set AutocadDoc = AutocadApp.Document.App
End If
Set Rectangle = AutocadDoc.modelspace.addlightweightpolyline(RectArray)
AutocadApp.ZoomExtents
Set Rectangle = Nothing
Set AutocadApp = Nothing
Set AutocadDoc = Nothing
End Sub
executing this in calc leave error:
BASIC runtime error. '1'
Type: com.sun.star.lang.WrappedTargetRuntimeException Message: [automation bridge] unexpected exception in IUnknownWrapper::invoke ! Message : [automation bridge]:
related link:
https://ask.libreoffice.org/en/question/264919/draw-lines-in-autocad-since-libreoffice-calc/
Hi @jtm2020hyo ,
thank you for your reply, the above macro has been developed inside AUTOCAD VBA, more friendly for my knowledge.
I don't know Libreoffice development tools, even if seems very similar to VBA, with some exception of syntax, as well the use of API Standard.
The effort for writing above macro was very big, I used also the Libreoffice forum for help, I'm suggesting you to register you will find a lot of support.
For next step I'll try to read also from Calc document, so you can drawn a line into Autocad, and I will use for a next future.
Bye.
if it's not much of a problem, could you share the link where you was requesting help?
this is the first for documentation
https://wiki.openoffice.org/wiki/Documentation/BASIC_Guide/Cells_and_Ranges (link related to cells range, but containing a lot of info).
Second one it's the forum
https://forum.openoffice.org/en/forum/index.php?sid=a162064ae6f3fab8f70b89f7ba879f85
More others for macro, documentation, sample and so on.
http://www.pitonyak.org/oo.php
and it will list what properties and methods are available to it, but inside OpenOffice development area..
https://extensions.openoffice.org/en/pr ... ction-tool
Bye
Hi @jtm2020hyo ,
here below your code perfect working. Again as previously indicated the code has been developed inside Autocad VBA development ambient, because I know better such area, and in my opinion should be better.
I created a Calc file Named MyRect.ods (See zip file attached) and put into cells C6 and C7 some value 100 and 200 for example.
Of course the path and file name could be changed here:
srL_Url = "file:///C:\Users\IO\Desktop\MyRect.ods"
The trick for reading cell value it's inside:
obL_Sheet.getCellRangeByName("C7").Value
or
obL_Sheet.getCellByPosition(Row, Col).Value
if contents it's a string change .Value with .String
See attached image of a rectangle.
Let me know, including the address where to delivery the invoice 🙂 (I'm joking, of course).
Bye
Sub DrawRectangle()
'VARIABLES:
' Dim AutocadApp As Object
' Dim AutocadDoc As Object
Dim RectArray(0 To 9) As Double
Dim Rectangle As Object
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 a1L_Arguments()
'****** Launch Autocad application**** NO NECESSARY ALREADY INSIDE AUTOCAD
' On Error Resume Next
' Set AutocadApp = GetObject(, "Autocad.application")
' On Error GoTo 0
'If AutocadApp Is Nothing Then
'Set AutocadApp = CreateObject("Autocad.application")
'AutocadApp.Visible = True
'End If
'****** Create link with OpenOffice Calc application and open a specific Calc sheet containing the rectangle coordinates****
'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")
'****** Use below Code for a New empty Calc Sheet ******
'srL_Url = "private:factory/scalc"
'Set obL_Calc_Document = obL_Desktop.loadComponentFromURL(srL_Url, "_blank", 0, a1L_Arguments)
'Set obL_Sheet = obL_Calc_Document.Sheets.getByIndex(0)
'****** Use below Code for open a specific Calc Sheet ******
srL_Url = "file:///C:\Users\IO\Desktop\MyRect.ods"
Set obL_Calc_Document = obL_Desktop.loadComponentFromURL(srL_Url, "_blank", 0, a1L_Arguments)
Set obL_Sheet = obL_Calc_Document.Sheets.getByIndex(0)
''****Point 1****
RectArray(0) = 0
RectArray(1) = 0
''****Point 2****
RectArray(2) = obL_Sheet.getCellRangeByName("C6").Value
RectArray(3) = 0
''****Point 3****
RectArray(4) = obL_Sheet.getCellRangeByName("C6").Value
RectArray(5) = obL_Sheet.getCellRangeByName("C7").Value
''****Point 4****
RectArray(6) = 0
RectArray(7) = obL_Sheet.getCellRangeByName("C7").Value
''****Point 5****
RectArray(8) = 0
RectArray(9) = 0
''****Draw rectangle****
'On Error Resume Next
'Set AutocadDoc = AutocadApp.ActiveDocument
'On Error GoTo 0
'If AutocadApp Is Nothing Then
'Set AutocadDoc = AutocadApp.Document.App
'End If
Set Rectangle = ThisDrawing.ModelSpace.AddLightWeightPolyline(RectArray)
Application.ZoomExtents
'AutocadApp.ZoomExtents
'Set Rectangle = Nothing
'Set AutocadApp = Nothing
'Set AutocadDoc = Nothing
End Sub
cool, work perfectly, good idea calling to LO Calc since AutoCAD VBA.
... it is possible to do the inverse? I mean, draw since LO Calc VBA-Base-UNO to AutoCAD?
Hi @jtm2020hyo , it may be, but as explained my experience it's more oriented on Autocad side, so this is the main reason because I started in this way Autocad Vs Calc.
In addition the core it's Autocad where you have to drawn objects, so I'm never seeing reasons for starting from OpenOffice and ending to Autocad.
Could me explain reasons because do you want to do the opposite ? Probably you already have some procedure, more complex than drawing a rectangle, written on VBA-Base-UNO ?
Bye
yes, there is a reason...
https://www.youtube.com/c/MustafaAbdelBaset/videos
this channel created a tool named "Circuit Pro", and is awesome for electrical plan design and use excel and encrypted vlx, the problem is than he is not updating or fixing their code and I want to use their excel VBA modules but since Calc to draw in AutoCAD because he already has a lot of modules. I choice calc and not excel just because is open source and free.
Check their channel, has very good tools, highly recommended.
Hi to Everybody,
following the @jtm2020hyo suggestions I'm trying to do the opposite of the above code, developed in Autocad VBA, and perfectly working.
So I'm trying to use OpenOffice Calc for Windows, OOBasic, and linking with Autocad in order to pass the coordinates for drawing an object for example, or passing to Autocad dimensional data coming from a calculation obtained with OpenOffice Calc.
Here below the code, able to retrive data from OpenOffice Calc worksheet (same as Excel just more commercial product), open Autocad, but the procedure hangs whenever I'm trying to execute for drawing object,
Set Rectangle = AutocadDoc.modelspace.addlightweightpolyline(RectArray)
I tried also with a simple text I got the same error (see attached picture with error message).
I guess it's not dependent from Autocad but from the way as the OLE link will be established inside OpenOffice.
I tried to debug the Object created in BASIC, using a tools called MRI but too complicate for my product's knowledge
Perhaps somebody of group, or somebody of Autodesk company has some knowledge of both products, and could help us in this issue (perhaps @Lena could help us).
I asked the help into OpenOffice forum but of course the didn't know Autocad Application and they are not able to help us.
Bye
Sub Main
Dim AutocadApp As Object
Dim AutocadDoc As Object
Dim RectArray(0 To 9) As Double
Dim Rectangle As Object
Dim oFactory, oApp, oSess, oFolder
Dim oSheet As Object
'****** Launch Autocad application****
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getByIndex(0)
oCell = oSheet.getCellByPosition(2, 5)
oCell1 = oSheet.getCellByPosition(2, 6)
Q=oCell.getValue()
R=oCell1.getValue()
''****Point 1****
RectArray(0) = 0
RectArray(1) = 0
''****Point 2****
RectArray(2) = Q
RectArray(3) = 0
''****Point 3****
RectArray(4) = Q
RectArray(5) = R
''****Point 4****
RectArray(6) = 0
RectArray(7) =R
''****Point 5****
RectArray(8) = 0
RectArray(9) = 0
oFactory = createUnoService("com.sun.star.bridge.oleautomation.Factory")
oApp = oFactory.createInstance("Autocad.application")
oApp.Visible = True
''****Draw rectangle****
Set AutocadDoc = oApp.ActiveDocument
Set Rectangle = AutocadDoc.modelspace.addlightweightpolyline(RectArray)
End Sub