interconnection between AutoCAD and LibreOffice Calc via vba

[jtm2020hyo]

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

[grobnik]

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.

[jtm2020hyo]

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

[grobnik]

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

[grobnik · Accepted solution]

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

[jtm2020hyo]

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/

[grobnik]

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.

[jtm2020hyo]

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

[grobnik]

@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

[grobnik · Accepted solution]

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

[jtm2020hyo]

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?

[grobnik]

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

[jtm2020hyo]

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.

[grobnik]

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