Building sketches as per XY data and additional technical info from excel

[L_Schwotzer]

Over the past few weeks, I have been redeveloping the design and repurposing stages for a new project in development within the company Vecta pty ltd I work within. In developing this solution I have turned to using the coordinate system embedded within Inventor 2020 as per an excel spreadsheet. However, with this came limitations as it simply can only create points within sketches as opposed to the solution we must achieve, in being circles of varying diameters within these locations. 

As per the following images, the sketch can only assess the XYZ coordinates as portrayed in the following excel sheet.

Instead of locating these center locations of the circles, is there some way I can manipulate the software to instead assess and sketch the circles as per their diameters and coordinates as listed within the excel sheet, all up creating the following sketch. 

From what I understand, iLogic may be the answer but with limited programming knowledge and no understanding on how to integrate this within Inventor to create sketches, I'm stuck as to where to next progress. 

[JhoelForshav · Accepted solution]

Hi @L_Schwotzer 

If the excelfile looks like in your picture, meaning X = A column, Y = B column and diameter = E column.

And the values start at row 2. Try this iLogic rule. Just set oFileName and oSheetName to the file and sheetname of your excel-file.

Dim oDoc As PartDocument = ThisDoc.Document
Dim oDef As PartComponentDefinition = oDoc.ComponentDefinition
Dim oSketchPlane As WorkPlane = oDef.WorkPlanes.Item("XY Plane")
Dim oSketch As PlanarSketch = oDef.Sketches.Add(oSketchPlane)
Dim oFileName As String = "C:\Temp\Test.xlsx" 'Excel file 
Dim oSheetName As String = "Sheet1" 'Sheet name
GoExcel.Open(oFileName, oSheetName)
Dim i As Integer = 2

Dim UoM As UnitsOfMeasure = oDoc.UnitsOfMeasure
While True
	Try
	Dim oX As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("A" & i)), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
	Dim oY As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("B" & i)), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
	Dim oDia As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("E" & i)), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
	Dim oPoint As Point2d = ThisApplication.TransientGeometry.CreatePoint2d(oX, oY)
	Dim oCircle As SketchCircle = oSketch.SketchCircles.AddByCenterRadius(oPoint, oDia / 2)
	oSketch.GeometricConstraints.AddGround(oCircle.CenterSketchPoint)
	oSketch.DimensionConstraints.AddDiameter(oCircle, ThisApplication.TransientGeometry.CreatePoint2d( _
	oCircle.CenterSketchPoint.Geometry.X - oDia, oCircle.CenterSketchPoint.Geometry.Y + oDia))
	i += 1
	Catch
		Exit While
	End Try
End While
GoExcel.Close
oDoc.Update

[L_Schwotzer]

Thank you very much, worked perfectly

[L_Schwotzer]

So expanding upon this solution, is there some way I can also allow the excel file to identify specific extrusion heights as per the circle diameters.

e.g. all circles of 10mm diameter to be extruded by 20mm, whilst those of 4mm diameter to be extruded by 6mm

[JhoelForshav]

@L_Schwotzer 

Sure. please attach a sample excel-file so I can see how you want to specify the different extrusion heights 🙂

[L_Schwotzer]

In following this revised table to which includes extrusion values, I was also hoping that these extrusion would be symmetric, versus the one directional default. As you can see with the excel sheet, the heights correspond to the hole diameter and don't vary beyond that detail. 

[JhoelForshav · Accepted solution]

@L_Schwotzer 

Try this 🙂

Dim oDoc As PartDocument = ThisDoc.Document
Dim oDef As PartComponentDefinition = oDoc.ComponentDefinition
Dim oSketchPlane As WorkPlane = oDef.WorkPlanes.Item("XY Plane")
Dim oSketch As PlanarSketch = oDef.Sketches.Add(oSketchPlane)
Dim oFileName As String = "C:\TEMP\Test.xlsx" 'Excel file 
Dim oSheetName As String = "Sheet1" 'Sheet name
GoExcel.Open(oFileName, oSheetName)
Dim i As Integer = 2
Dim oProfiles As New Dictionary(Of Double, ObjectCollection)

Dim UoM As UnitsOfMeasure = oDoc.UnitsOfMeasure
While True
	Try
	Dim oX As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("A" & i)), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
	Dim oY As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("B" & i)), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
	Dim oDia As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("C" & i)), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
	Dim oextDist As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("D" & i)), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
	Dim oPoint As Point2d = ThisApplication.TransientGeometry.CreatePoint2d(oX, oY)
	Dim oCircle As SketchCircle = oSketch.SketchCircles.AddByCenterRadius(oPoint, oDia / 2)
	oSketch.GeometricConstraints.AddGround(oCircle.CenterSketchPoint)
	oSketch.DimensionConstraints.AddDiameter(oCircle, ThisApplication.TransientGeometry.CreatePoint2d( _
	oCircle.CenterSketchPoint.Geometry.X - oDia, oCircle.CenterSketchPoint.Geometry.Y + oDia))
	
	If oProfiles.ContainsKey(oextDist)
		oProfiles.Item(oextDist).Add(oCircle)
	Else
		Dim oCol As ObjectCollection = ThisApplication.TransientObjects.CreateObjectCollection
		oCol.Add(oCircle)
		oProfiles.Add(oextDist, oCol)
	End If
	
	i += 1
	Catch
		Exit While
	End Try
End While
GoExcel.Close
Dim oExtrudes As ExtrudeFeatures = oDef.Features.ExtrudeFeatures
For Each oPair As KeyValuePair(Of Double, ObjectCollection) In oProfiles
Dim extDef As ExtrudeDefinition = oExtrudes.CreateExtrudeDefinition(oSketch.Profiles.AddForSolid(False, oPair.Value), PartFeatureOperationEnum.kJoinOperation)
extDef.SetDistanceExtent(oPair.Key, PartFeatureExtentDirectionEnum.kSymmetricExtentDirection)
oExtrudes.Add(extDef)
Next

oDoc.Update

Again it's important that the columns are correct:

A = X-pos

B = Y-pos

C = Diameter

D = Extrude distance

[L_Schwotzer]

Not entirely sure what's gone on, but all I've changed within the coding was the folder and file location, however the end result is shown in the following image. Is there something I have failed to do when copying and enacting the code, or is there some other issue?

[JhoelForshav]

Hi @L_Schwotzer 

The code works fine when I try it. Could you attach your actual excel-file (not just a screenshot) so I can investigate what the problem is? 🙂

[L_Schwotzer]

Sorry my mistake, I've run it again a few times and found an error on my behalf. Thank you very much for all the aid t seems to work perfectly now.

[L_Schwotzer]

So my final query is in relation to the following model images and the corresponding excel sheet which details the model. 

Similar to how the previous structures have come about, the cylinders will again be defined as per the technical data depicted within the excel spreadsheet. However, rather than a symmetric extrusion of the cylinder, I'm looking for these to be one directional and again with heights in relation to sizes, whilst a base plate is created underneath and extruded in the opposing direction and again defined within the spreadsheet. Any info or further iLogic configurations on how this goal may be achieved would be greatly appreciated.  

[JhoelForshav · Accepted solution]

@L_Schwotzer 

This should do it then 🙂

Dim oDoc As PartDocument = ThisDoc.Document
Dim oDef As PartComponentDefinition = oDoc.ComponentDefinition
Dim oSketchPlane As WorkPlane = oDef.WorkPlanes.Item("XY Plane")
Dim oSketch As PlanarSketch = oDef.Sketches.Add(oSketchPlane)
Dim oFileName As String = "C:\TEMP\Test.xlsx" 'Excel file 
Dim oSheetName As String = "Sheet1" 'Sheet name
GoExcel.Open(oFileName, oSheetName)
Dim i As Integer = 2
Dim oProfiles As New Dictionary(Of Double, ObjectCollection)

Dim UoM As UnitsOfMeasure = oDoc.UnitsOfMeasure
While True
	Try
		Dim oX As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("A" & i)), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
		Dim oY As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("B" & i)), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
		Dim oDia As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("C" & i)), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
		Dim oextDist As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("D" & i)), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
		Dim oPoint As Point2d = ThisApplication.TransientGeometry.CreatePoint2d(oX, oY)
		Dim oCircle As SketchCircle = oSketch.SketchCircles.AddByCenterRadius(oPoint, oDia / 2)
		oSketch.GeometricConstraints.AddGround(oCircle.CenterSketchPoint)
		oSketch.DimensionConstraints.AddDiameter(oCircle, ThisApplication.TransientGeometry.CreatePoint2d( _
		oCircle.CenterSketchPoint.Geometry.X - oDia, oCircle.CenterSketchPoint.Geometry.Y + oDia))

		If oProfiles.ContainsKey(oextDist)
			oProfiles.Item(oextDist).Add(oCircle)
		Else
			Dim oCol As ObjectCollection = ThisApplication.TransientObjects.CreateObjectCollection
			oCol.Add(oCircle)
			oProfiles.Add(oextDist, oCol)
		End If

		i += 1
	Catch
		Exit While
	End Try
End While

Dim oExtrudes As ExtrudeFeatures = oDef.Features.ExtrudeFeatures
For Each oPair As KeyValuePair(Of Double, ObjectCollection) In oProfiles
	Dim extDef As ExtrudeDefinition = oExtrudes.CreateExtrudeDefinition(oSketch.Profiles.AddForSolid(False, oPair.Value), PartFeatureOperationEnum.kJoinOperation)
	extDef.SetDistanceExtent(oPair.Key, PartFeatureExtentDirectionEnum.kPositiveExtentDirection)
	oExtrudes.Add(extDef)
Next

'Ground plate
Dim gX As Double = 0
Dim gY As Double = 0
Dim gDia As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("F2")), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
Dim gExt As Double = UoM.ConvertUnits(CDbl(GoExcel.CellValue("G2")), UoM.LengthUnits, UnitsTypeEnum.kDatabaseLengthUnits)
Dim oGroundCircle As SketchCircle = oSketch.SketchCircles.AddByCenterRadius(ThisApplication.TransientGeometry.CreatePoint2d(), _
gDia / 2)
oSketch.GeometricConstraints.AddGround(oGroundCircle.CenterSketchPoint)
oSketch.DimensionConstraints.AddDiameter(oGroundCircle, ThisApplication.TransientGeometry.CreatePoint2d( _
oGroundCircle.CenterSketchPoint.Geometry.X - gDia, oGroundCircle.CenterSketchPoint.Geometry.Y + gDia))
Dim gCol As ObjectCollection = ThisApplication.TransientObjects.CreateObjectCollection
gCol.Add(oGroundCircle)
Dim oGroundDef As ExtrudeDefinition = oExtrudes.CreateExtrudeDefinition(oSketch.Profiles.AddForSolid(False, gCol), PartFeatureOperationEnum.kJoinOperation)
oGroundDef.SetDistanceExtent(gExt, _
PartFeatureExtentDirectionEnum.kNegativeExtentDirection)
oExtrudes.Add(oGroundDef)
GoExcel.Close
oDoc.Update