Create a list of center coordinates of holes and save as excel file

[sgajjar12F792R]

i want to create a list of coordinates of the centers of all holes in my .ipt and save the list as an Excel file. i am familiar with Excel VBA. But, not very expert in inventor iLogic. i tried to search online and created a code like below. 

This code doesn't give any error even the Excel file is also created. But, it is always blank. I am not able to find what exactly is causing the problem. i don't want to go with the hole table command because it only gives .CSV file.

Also, extend this code if possible to run a global macro in that Excel file.

If this task can be done with the "Holefeature" object instead of Edges, that is also fine.

Any help would be much appreciated. 

Dim partDoc As PartDocument
    partDoc = ThisApplication.ActiveDocument

    ' Get the component definition of the part
    Dim partDef As PartComponentDefinition
    partDef = partDoc.ComponentDefinition

    ' Get all circular edges in the part
    Dim edges As Edges
    edges = partDef.SurfaceBodies.Item(1).Edges
    
    Dim holeList As New List(Of String)
    
    For Each edge As Edge In edges
        If Edge.GeometryType = kCircularEdgeGeometry Then
			Dim cen As Point = Edge.Geometry.center
			Dim holeInfo As String = cen.X & "," & cen.Y & "," & cen.Z
            holeList.Add(holeInfo)
        End If
    Next
	
	Dim excelApp As Object = CreateObject("Excel.Application")
Dim excelWorkbook As Object = excelApp.Workbooks.Add()
Dim excelSheet As Object = excelWorkbook.Sheets(1)

excelSheet.Cells(1, 1).Value = "X"
excelSheet.Cells(1, 2).Value = "Y"
excelSheet.Cells(1, 3).Value = "Z"

For i As Integer = 0 To holeList.Count - 1
    Dim holeInfoArray As String() = holeList(i).Split(",")
    excelSheet.Cells(i + 2, 1).Value = holeInfoArray(0)
    excelSheet.Cells(i + 2, 2).Value = holeInfoArray(1)
    excelSheet.Cells(i + 2, 3).Value = holeInfoArray(2)
Next

excelWorkbook.SaveAs("C:\HoleInfo.xlsx")
excelWorkbook.Close()
excelApp.Quit()

[richterBKSAC · Accepted solution]

Hi there,

i think you have to edit line 15 of your code to:

If Edge.GeometryType = kCircleCurve Then

at least in my test part the excel file isn't blank anymore.

[Andrii_Humeniuk]

Hello. I hope the following code will help you.

You need to add your code for recording information in Excel to the "ExportHoleInfo" function.

Sub main
	If TypeOf ThisDoc.Document Is PartDocument Then
		Dim oPartDoc As PartDocument = ThisDoc.Document
		Dim oTG As TransientGeometry = ThisApplication.TransientGeometry	
		Dim oDef As PartComponentDefinition = oPartDoc.ComponentDefinition
		For Each oBody As SurfaceBody In oDef.SurfaceBodies
			Dim dLengths As New List(Of Double)
			Dim minBox As OrientedBox = GetBoxBody(oBody)
			Dim oPlaneMax, oPlaneMid As Plane
			dLengths.AddRange({minBox.DirectionOne.Length, minBox.DirectionTwo.Length, minBox.DirectionThree.Length })
			dLengths.Sort()
			If minBox.DirectionOne.Length = dLengths.Item(2) Then
				oPlaneMax = oTG.CreatePlane(minBox.CornerPoint, minBox.DirectionOne)
			Else If minBox.DirectionTwo.Length = dLengths.Item(2) Then
				oPlaneMax = oTG.CreatePlane(minBox.CornerPoint, minBox.DirectionTwo)
			Else If minBox.DirectionThree.Length = dLengths.Item(2) Then
				oPlaneMax = oTG.CreatePlane(minBox.CornerPoint, minBox.DirectionThree)
			End If
			If minBox.DirectionOne.Length = dLengths.Item(1) Then
				oPlaneMid = oTG.CreatePlane(minBox.CornerPoint, minBox.DirectionOne)
			Else If minBox.DirectionTwo.Length = dLengths.Item(1) Then
				oPlaneMid = oTG.CreatePlane(minBox.CornerPoint, minBox.DirectionTwo)
			Else If minBox.DirectionThree.Length = dLengths.Item(1) Then
				oPlaneMid = oTG.CreatePlane(minBox.CornerPoint, minBox.DirectionThree)
			End If
			If IsNothing(oPlaneMax) Or IsNothing(oPlaneMid) Then Exit Sub
			ExportHoleInfo(oBody.Faces, oPlaneMax, oPlaneMid)
		Next
	End If
End Sub

Private Function GetBoxBody(oBody As SurfaceBody) As OrientedBox
	Dim transBRep As TransientBRep = ThisApplication.TransientBRep
	If combinedBodies Is Nothing Then
		combinedBodies = transBRep.Copy(oBody)
	Else
		transBRep.DoBoolean(combinedBodies, oBody, BooleanTypeEnum.kBooleanTypeUnion)
	End If
	Return combinedBodies.OrientedMinimumRangeBox
End Function

Private Function ExportHoleInfo(oFaces As Faces, oPlaneMax As Plane, oPlaneMid As Plane)
	Dim oMeasTool As MeasureTools = ThisApplication.MeasureTools
	Dim oDef As PartComponentDefinition = oFaces.Item(1).Parent.Parent
	Dim oPartDoc As PartDocument = oDef.Document
	Dim uom As UnitsOfMeasure = oPartDoc.UnitsOfMeasure
	Dim iHole As Integer = 1
	For Each oFace As Face In oFaces
		If oFace.SurfaceType = SurfaceTypeEnum.kCylinderSurface Then
			Dim oCylinder As Cylinder = oFace.Geometry			
			Dim dMaxSize As Double = uom.ConvertUnits(oMeasTool.GetMinimumDistance(oCylinder.BasePoint, oPlaneMax), "cm", uom.LengthUnits)
			Dim dMinSize As Double = uom.ConvertUnits(oMeasTool.GetMinimumDistance(oCylinder.BasePoint, oPlaneMid), "cm", uom.LengthUnits)
			Dim dDiameter As Double = uom.ConvertUnits(oFace.Geometry.Radius * 2, "cm", uom.LengthUnits)
			MessageBox.Show("Max distance = " & dMaxSize & vbCrLf & "Min distance = " & dMinSize, "Hole №" & iHole & " d" & dDiameter)
			iHole += 1
		End If
	Next
End Function

[WCrihfield · Accepted solution]

Hi guys.  I might as well throw my version of the code in here too, just for the sake of having a variety to choose from.  There are many ways to do something like this.  I usually tend to use much more code in my Excel related rules, but for the sake if simplicity, I just copied some of the original poster's code for that part, then modified the middle part of it a little, to show another way of thinking about that process.  I just attached the text file, instead of copy & paste to code window, to keep it clean.

[J-Camper · Accepted solution]

@sgajjar12F792R,

Here is an example using HoleFeatures instead of looking for circular edges:

Sub Main
	Dim partDoc As PartDocument = TryCast(ThisApplication.ActiveDocument, PartDocument)
	If IsNothing(partDoc) Then Logger.Debug("Rule not run with part file active.") : Exit Sub
	
	Dim holeList As New List(Of Point)
	
	For Each myHoleFeature As HoleFeature In partDoc.ComponentDefinition.Features.HoleFeatures
		If myHoleFeature.Suppressed Then Continue For
		For Each holePoint As SketchPoint In myHoleFeature.HoleCenterPoints
			holeList.Add(holePoint.Geometry3d)
		Next
	Next
	
	If holeList.Count < 1 Then Logger.Debug("No holes found in part") : Exit Sub
	Call ExportHoleToExcel(holeList)
	
End Sub 

Sub ExportHoleToExcel(holeList As List(Of Point))

	Dim excelApp As Object = CreateObject("Excel.Application")
	Dim excelWorkbook As Object = excelApp.Workbooks.Add()
	Dim excelSheet As Object = excelWorkbook.Sheets(1)
	
	Dim oRow As Integer = 1
	excelSheet.Cells(oRow, 1).Value = "X"
	excelSheet.Cells(oRow, 2).Value = "Y"
	excelSheet.Cells(oRow, 3).Value = "Z"

	For Each oPoint In holeList
		oRow +=1
		excelSheet.Cells(oRow, 1).Value = oPoint.X.ToString
	    excelSheet.Cells(oRow, 2).Value = oPoint.Y.ToString
	    excelSheet.Cells(oRow, 3).Value = oPoint.Z.ToString
	Next

	excelWorkbook.SaveAs("C:\Temp\HoleInfo.xlsx")
	excelWorkbook.Close()
	excelApp.Quit()
	
End Sub

[sgajjar12F792R]

Thanks @richterBKSAC . After this correction code is returning expected output.

I wonder how a small error can affect the whole code 😐.

[sgajjar12F792R]

Thanks @WCrihfield, Your version of code is also working fine for my part. But, when there are multiple faces, code process one face only and in some case we might need holes information of other faces also.

[sgajjar12F792R]

Thanks @J-Camper. This looks good. An added advantage of holefeature is that you can derive other properties and parameters as well. Just need to update holelist and "ExportHoleToExcel" function accordingly. i will try to implement this in my code to get hole diameters also.

Thanks again!

[sgajjar12F792R]

@J-Camper, while working on your code, I observed that there are linear pattern of hole in my part. But, Holefeature doesn't count them and only returns original hole info only. is there any way to all holes in part?

[J-Camper]

@sgajjar12F792R,

Patterning holes will definitely complicate the workflow I presented.  I have modified the code i originally posted to look for rectangular pattern features that pattern a hole feature. The main changes are listed here:

• I collected the name of each holefeature and a separate hole list for each stored inside a namevaluemap
• Then I ran the whole namevaluemap through a function that looks for the rectagularpatterns with a holefeature as the parent feature.
• Once I found a pattern, I ran the corresponding holelist through a hole duplicating function
• This hole duplicator normalizes the wide variety of input object types into usable validated objects for processing
• at this point it is a simple matter of creating a copy of the original point, then translating that point and saving that new point to a list, stacking on and translating each point for as many times as needed.
• once you have your list of new points, just merge the new list and the original to contain all the desired points
• I left the export function alone

I did not program anything for the "Follow path" option for rectangular pattern because, that would be an even bigger task.  I tried to leave useful break point debug messages as there was some limited documentation as to what type of objects can be used in parts of a pattern definition.

The rule got much longer so I am uploading a txt file this time.  Let me know if you have any questions, or if this is not working as intended.

[WCrihfield]

OK.  I wasn't sure what your needs were.  I assumed it was a something like a flat sheet metal part where primarily 2D data (or data from one/top face) was the primary target, like for profiling & drilling type CAM scenario.  Making that code work for all faces is actually much simpler, and requires at least 4 fewer lines of code.  I altered that code to remove those lines of code, then reattached the code as a text file again.

However, if you always use HoleFeatures for all of your holes, and never just extrude cut circles, that would certainly greatly simplify the task.  In my past experience, working with a team of designers, and with a lot of pre-existing designs, my solutions tend to need to be much more robust than these.  Sometimes holes were created with HoleFeatures, sometimes by extruding cutting a simple circle sketch, sometimes by subtracting another cylindrical body from the one I am inspecting using Boolean operations, sometimes by extruding a large,  complex, shared, master sketch, through multiple operations.  I personally like to use the HoleFeature any time the hole will be manufactured by drilling, tapping, counterbore, countersink, spotface, etc.  But will often use other means to form larger circular cutouts, because I know that they will be manufactured differently, and that I will not want to use a HoleThreadNote on it in the drawing.  So, in my case, I like to combine multiple possible approaches, just because of the variety of designs I encounter.  Simply looking for internal circular edges doesn't always mean each one will be for a hole either, because it could be for a cylindrical protrusion or boss too.  And when I am designing a pattern of holes, I always try to create the pattern of hole centers within the sketch used by the HoleFeature, instead of patterning a single HoleFeature, to help the Quantity aspect of the HoleThreadNotes I will later place in the drawings.  If you just use a rectangular feature pattern to pattern a single hole feature, the quantity will be off, but if the pattern was done by sketch centers, then that sketch used as the basis for a HoleFeature (creates multiple holes, one for each center point), that makes the smart quantity come out correct in the HoleThreadNote.  But as mentioned before, due to multiple designers (some not doing things that way), and previously created designs where things were not done that way, that single strategy can not always be relied upon by design automation code, because of the variations in the design process.

[sgajjar12F792R]

Thank you so much for your prompt response.

[sgajjar12F792R]

Yes, you're right. My primary requirement is data from one face only. But, my part is a box-type structure that has few holes on sides also and every time code was returning hole data of one side face only. Which, I was not able to change in your previous code.

But, it is very helpful that your code can handle different types of features. Also, using pattern feature for sketch is a nice idea and I will surely implement that. Thank you so much for your response.