Author Topic: Length Calculation from Autocad to Excel  (Read 12205 times)

0 Members and 1 Guest are viewing this topic.

orcan

  • Guest
Length Calculation from Autocad to Excel
« on: September 12, 2014, 10:59:50 AM »
Hey all,

Through my searches on internet i found the swamp with many helpful articles and members.
First of all i did not want to open a new thread but i could not find an exact solution for my problem.
Basically i need to open dwg files inside a folder and calculate each drawings total length then write it down to excel.
it should look like something below,
dwg1=1200 mm
dwg2=700 mm
and so on.
drawings may contains polylines or multilines.

Thanks in advance.


orcan

  • Guest
Re: Length Calculation from Autocad to Excel
« Reply #1 on: September 13, 2014, 09:01:01 AM »
i am sure answers is hiding somewhere.

MexicanCustard

  • Swamp Rat
  • Posts: 705
Re: Length Calculation from Autocad to Excel
« Reply #2 on: September 15, 2014, 09:41:01 AM »
Total length of what?  What's in these drawings that needs the length calculated?

Do you need the solution in VBA?  Do you write VBA and if so, post what you've tried in an attempt to solve this?
Revit 2019, AMEP 2019 64bit Win 10

orcan

  • Guest
Re: Length Calculation from Autocad to Excel
« Reply #3 on: September 15, 2014, 02:13:27 PM »
that should be done by excel vba

i did not write my own codes just tried some other users codes,

they do not fit what i wanted and seems very complicated to be redesigned by my self

process is like that

a certain folder will be choosed in excel than all dwg files  adresses will be obtained by excel

than excel will make the autocad open those files and calculate the total length of the lines

finally autocad will publish those files on excel.

The goal of the project is to calculate total length of those parts to be cut by plasma cutter.

hope i could explain better


RICVBA

  • Newt
  • Posts: 62
Re: Length Calculation from Autocad to Excel
« Reply #4 on: September 16, 2014, 10:00:03 AM »
I attach an excel file with VBA code I quickly derived from one of mine to try and match your needs
It references AutoCAD 2010 type libraries
If you have a different AutoCAD version you have to update the references via Tools-> References tab in the VBA IDE and selecting the proper reference (something like "AutoCAD 20xx Type Library")

and pray it works!

bye

orcan

  • Guest
Re: Length Calculation from Autocad to Excel
« Reply #5 on: September 16, 2014, 12:57:16 PM »
wow it simply works like magic!

thanks mate now my journey begins through every single letter of your code :)

p.s. for the different version from autocad 2010  users out there

first i had to deactivate the missing libraries from tools>references.

RICVBA

  • Newt
  • Posts: 62
Re: Length Calculation from Autocad to Excel
« Reply #6 on: September 16, 2014, 01:25:05 PM »
Quote
thanks mate
you're welcome

Quote
now my journey begins through every single letter of your code
that's the attitude I hoped to meet and encourage
I think you can have the macro treat MLines too with very little effort
bye

orcan

  • Guest
Re: Length Calculation from Autocad to Excel
« Reply #7 on: September 17, 2014, 08:51:55 AM »
i was trying a very simple code

open an autocad file

draw a square

explode it to get lines instead of polylines

select all

and finally write down the line lengths on excel.

everythinf seems fine until typing down the lengths.

Could you please have a look at it.

Sub Filtresiz()

Dim LWline As AcadLine
Dim SetseC As AcadSelectionSet
Dim strDrawing As String
Dim LwlengtH As Double

    On Error Resume Next
    Set Graphics = GetObject(, "AutoCAD.Application")
    If Err.Description > vbNullString Then
        Err.Clear
        Set Graphics = CreateObject("AutoCAD.Application")
    End If
   
    Graphics.Visible = True
   
    AppActivate "autocad"
   
Graphics.ActiveDocument.SendCommand ("rectang" & vbCr & "0,0" & vbCr & "1000,1000" & vbCr & "_.zoom _e" & vbCr & "_ai_selall" & vbCr & "_explode" & vbCr)
   
Graphics.ActiveDocument.SendCommand ("_ai_selall" & vbCr)

Set ssetObj = Graphics.ActiveDocument.SendCommand("_ai_selall" & vbCr)
           
Graphics.WindowState = acMax

If SetseC.Count > 0 Then
   
  For Each LWline In SetseC

     With LWline

     LwlengtH = .legth

     End With

  Cells(18, 18).Value = Cells(18, 18).Value + LwlengtH

  Next LWline
 
End If
 
    On Error GoTo 0

End Sub

RICVBA

  • Newt
  • Posts: 62
Re: Length Calculation from Autocad to Excel
« Reply #8 on: September 17, 2014, 10:09:54 AM »
1) why have you to explode a polyline in lines? Polylines have Lenght property as well

2) to better find errors you must be backed by the default error managing
so place
Code: [Select]
On Error GoTo 0just after
Code: [Select]
    On Error Resume Next
    Set Graphics = GetObject(, "AutoCAD.Application")
    If Err.Description > vbNullString Then
        Err.Clear
        Set Graphics = CreateObject("AutoCAD.Application")
    End If
code block, instead of by the end of your code.
this way after taking advantage of the temporary suppression of default error warning management, you set it back so that you are still able to trace errors as and where they occur
and you'll find out that the actual bad instruction is
Code: [Select]
Set ssetObj = Graphics.ActiveDocument.SendCommand("_ai_selall" & vbCr) and you have to think about it. Read carefully the online AutoCAD ActiveX and VBA Reference "SelectionSet object " topic to find out if you're properly coding what is needed to build, fill and use a selection set. You can also dig into my code for hints.
And since I don't use SendCommand method I'm not sure it returns an object that you can assign to a selecstionset object. But you can surely build and fill a selectionset with all drawing elements via VBA code

3) once you're done with your selectionset object you can use it in subsequent code. but you have to refer to it properly: everyone wants to be called by its actual name!

moreover
4) in
Code: [Select]
LwlengtH = .legththere are sintax errors. it must be
Code: [Select]
LwlengtH = .Length
5) I don't use
Code: [Select]
If Err.Description > vbNullString Then so I'm not sure it works properly
I always use
Code: [Select]
If Err then

6) as a general rule I'd recommend using "Option Explicit" at the very beginning of each module, so that you're forced to declare every variable and thus making sure you're taking care of their proper treatment.
in this case, two declarations would be missing
Code: [Select]
Dim Graphics As AcadApplication
Dim ssetObj As AcadSelectionSet

orcan

  • Guest
Re: Length Calculation from Autocad to Excel
« Reply #9 on: September 18, 2014, 08:04:55 AM »
wow Ric

That helped too much.

for the filter method

you have used some codes like below

'setting filtering critera
    gpCode(0) = 0
    dataValue(0) = "LWPOLYLINE"
   
    'selecting LWPolylines
    ssetObj.SelectOnScreen gpCode, dataValue

this works perfect,but with this method user have to make the selection.

What i am trying to do is autocad to select everything on screen and send lenghts info to excel.

İs it possible and if it is so do you know the codes for this process.

Thanks in advance.

RICVBA

  • Newt
  • Posts: 62
Re: Length Calculation from Autocad to Excel
« Reply #10 on: September 18, 2014, 01:33:51 PM »
it's possible
but let me warn you first: not every object supports "length" property. thus not filtering the selectionset may result in unexpected errors

if you want to go on selecting everything anyway, look into my code more carefully: I did not use "SelectOnScreen" method
Also look through the online AutoCAD ActiveX and VBA Reference "SelectionSet object " topic, you'll find:

Select Method

Selects objects and places them into a selection set.

Signature
object.Select Mode[, Point1][, Point2][, FilterType][, FilterData]

Object
 SelectionSet
 The object this method applies to.

Mode
 AcSelect enum; input-only
 acSelectionSetWindow
 acSelectionSetCrossing
 acSelectionSetPrevious
 acSelectionSetLast
 acSelectionSetAll

Point1
 Variant (array of doubles); input-only; optional
 The 3D WCS coordinates, or array of coordinates, specifying Point1. See the mode definitions for the proper use of Point1.

Point2
 Variant (three-element array of doubles); input-only; optional
 The 3D WCS coordinates specifying Point2. See the mode definitions for the proper use of Point2.

FilterType
 Integer; input-only; optional
 A DXF group code specifying the type of filter to use.

FilterData
 Variant; input-only; optional
 The value to filter on.

Remarks
This method supports the filtering mechanism.

The following selection modes are available:
 Window
  Selects all objects completely inside a rectangular area whose corners are defined by Point1 and Point2.

 Crossing
  Selects objects within and crossing a rectangular area whose corners are defined by Point1 and Point2.

 Previous
  Selects the most recent selection set. This mode is ignored if you switch between paper space and model space and attempt to use the selection set.

 Last
  Selects the most recently created visible objects.

 All
  Selects all objects.

For more selection mode options, see the SelectByPolygon, SelectAtPoint, and SelectOnScreen methods.



and you'll also find the following example

Sub Example_Select()
    ' This example adds members to a selection set, first by crossing and
    ' then by filtering for circles.
   
    ' Create the selection set
    Dim ssetObj As AcadSelectionSet
    Set ssetObj = ThisDrawing.SelectionSets.Add("SSET")
   
   
    ' Add all object to the selection set that lie within a crossing of (28,17,0) and
    ' (-3.3, -3.6,0)
    Dim mode As Integer
    Dim corner1(0 To 2) As Double
    Dim corner2(0 To 2) As Double
   
    mode = acSelectionSetCrossing
    corner1(0) = 28: corner1(1) = 17: corner1(2) = 0
    corner2(0) = -3.3: corner2(1) = -3.6: corner2(2) = 0
    ssetObj.Select mode, corner1, corner2
   
    ' Add all the Circles to the selection set that lie within the crossing of (28,17,0) and
    ' (-3.3, -3.6,0) by filtering from the current drawing
    Dim gpCode(0) As Integer
    Dim dataValue(0) As Variant
    gpCode(0) = 0
    dataValue(0) = "Circle"
   
    Dim groupCode As Variant, dataCode As Variant
    groupCode = gpCode
    dataCode = dataValue
   
    ssetObj.Select mode, corner1, corner2, groupCode, dataCode
   
End Sub


orcan

  • Guest
Re: Length Calculation from Autocad to Excel
« Reply #11 on: September 18, 2014, 02:17:35 PM »
Thanks Ric that helped much

About slecting everything on Autocad screen i found another solution

   'setting filtering critera
    gpCode(0) = 0
   dataValue(0) = "LINE"
   
    'selecting LWPolylines
    ssetObj.Select acSelectionSetAll, , , gpCode, dataValue

with your advice i used filter and acselectionall made autocad select everything automatically

and get them into a set.

cheers.

RICVBA

  • Newt
  • Posts: 62
Re: Length Calculation from Autocad to Excel
« Reply #12 on: September 18, 2014, 03:34:08 PM »
That's it! Congratulations.

tecnica2

  • Guest
Re: Length Calculation from Autocad to Excel
« Reply #13 on: November 04, 2016, 01:19:42 PM »
hi all,

after a several hours searching on internet I found this huge topic. It's almost what I need.
I run the program and everything runs fine. But I need to make some changes to the program and I'm new to vba programming. So I'm begging a litle help, considering that the most of hard work is already done at this point.

First of all, I need to open dxf files instead of dwg. I've tried to change this but I failed.

On dxf file I have continuous lines and center(x2) lines.
Continuous lines are for plasma cutting and center(x2) lines are for bending lines.

So I need to separate them, not only in length counting but also in number counting.

Something like:
dxf1:
total cutting lines length=600mm
number of cutting lines=7

total bending lines length= 200
number of bending lines=3


I apreciate any help you can provide me.
Thanks in advance