topic Re: interconnection between AutoCAD and LibreOffice Calc via vba in VBA Forum

interconnection between AutoCAD and LibreOffice Calc via vba

jtm2020hyo — Tue, 08 Sep 2020 01:41:42 GMT

is there any method to create an interconnection between AutoCAD and LibreOffice Calc via VBA? I mean similar to AutoCAD and Excel using VBA.

Re: interconnection between AutoCAD and LibreOffice Calc via vba

grobnik — Tue, 08 Sep 2020 11:23:53 GMT

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.

Re: interconnection between AutoCAD and LibreOffice Calc via vba

jtm2020hyo — Tue, 08 Sep 2020 14:43:36 GMT

...but how can I draw a line since LibreOffice Calc to AutoCAD?

Re: interconnection between AutoCAD and LibreOffice Calc via vba

grobnik — Tue, 08 Sep 2020 15:06:17 GMT

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

Re: interconnection between AutoCAD and LibreOffice Calc via vba

grobnik — Wed, 09 Sep 2020 20:48:56 GMT

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

Re: interconnection between AutoCAD and LibreOffice Calc via vba

jtm2020hyo — Thu, 10 Sep 2020 07:22:33 GMT

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]:

Re: interconnection between AutoCAD and LibreOffice Calc via vba

grobnik — Thu, 10 Sep 2020 07:38:52 GMT

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.

Re: interconnection between AutoCAD and LibreOffice Calc via vba

jtm2020hyo — Thu, 10 Sep 2020 08:31:00 GMT

if it's not much of a problem, could you share the link where you was requesting help?

Re: interconnection between AutoCAD and LibreOffice Calc via vba

grobnik — Thu, 10 Sep 2020 08:37:38 GMT

@jtm2020hyo

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

Re: interconnection between AutoCAD and LibreOffice Calc via vba

grobnik — Thu, 10 Sep 2020 12:31:37 GMT

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

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

Re: interconnection between AutoCAD and LibreOffice Calc via vba

jtm2020hyo — Thu, 10 Sep 2020 14:58:20 GMT

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?

Re: interconnection between AutoCAD and LibreOffice Calc via vba

grobnik — Thu, 10 Sep 2020 15:09:21 GMT

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

Re: interconnection between AutoCAD and LibreOffice Calc via vba

jtm2020hyo — Thu, 10 Sep 2020 15:33:43 GMT

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.

Re: interconnection between AutoCAD and LibreOffice Calc via vba

grobnik — Fri, 11 Sep 2020 10:22:55 GMT

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 @Anonymous 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