Create 3d spline from excel worksheet using VBA

[Anonymous]

I need to automate the following action using VBA:

     In the user interface, with a Part file open, I select "Start 3D Sketch"

     In the Insert Panel, I select "Points" (which causes a File Open dialog box to open)

     Because I want a 3D Spline curve, I click on Options and select "Create Spline"

     I select an Excel workbook file that contains rows with X, Y, Z coordinate information, one row for each point

     The information in the Excel workbook is imported, the points are created, and a spline curve created through them

Can this action be accomplished using VBA, and if so, how.

Thanks.

command from the "Insert" panel

[WCrihfield]

I crafted a quickie example to test what you wanted done.

Unfortunately I'm not sure how to fully go through all the steps you specified exactly, which would mean interacting with multiple dialog boxes, but I did get pretty close to creating the final product without going that route.

I created a simple Excel document, formatted to work with the method you specified.  I only entered enough data to create 4 points, then saved it locally.  I used the routine you mentioned, and it created the points and spline just fine.

Then I went to work simulating the creation of the same geometry that process created using iLogic code.

I was easily able to create the 3D sketch, and the visible 3D sketch points points within that sketch.

However, creating the spline from those points, kept throwing errors.

I tried creating a couple different types of splines, and tried using either the sketched points or the mathematical points, but never got a successful outcome.

Is seems like this should work, because it seems fairly simple.

Then at last I tried drawing connected 3D lines between the points, allowing them to bend and specifying a radius, and that worked well, so I'm not quite sure why the splines aren't being created.

There is probably some tiny detail I'm missing, that is causing the errors.

Or perhaps it's just not liking the point locations I'm using, even though I've changed them several times while testing.

This is iLogic code, for simplicity, instead of VBA, but here is my code so far.

Dim oPDoc As PartDocument = ThisApplication.ActiveDocument
Dim oPDef As PartComponentDefinition = oPDoc.ComponentDefinition

Dim oXLFile As String = "S:\Engineering\SHARED\Tests\Points To Import.xlsx"
Dim oSheet As String = "Sheet1"
GoExcel.Open(oXLFile, oSheet)
GoExcel.DisplayAlerts = False
GoExcel.TitleRow = 2
GoExcel.FindRowStart = 3

Dim oTG As TransientGeometry = ThisApplication.TransientGeometry
Dim oTO As TransientObjects = ThisApplication.TransientObjects
Dim oCoords(2) As Double
Dim oPoint As Point
Dim oSkPt3d As SketchPoint3D
Dim oPoints As ObjectCollection = oTO.CreateObjectCollection
Dim oSketch3d As Sketch3D = oPDef.Sketches3D.Add()
For i As Integer = 3 To 10
	oCoords(0) = GoExcel.CellValue("A" & i)
	oCoords(1) = GoExcel.CellValue("B" & i)
	oCoords(2) = GoExcel.CellValue("C" & i)
	oPoint = oTG.CreatePoint(oCoords(0),oCoords(1),oCoords(2))
	oSkPt3d = oSketch3d.SketchPoints3D.Add(oPoint, True)
	oPoints.Add(oSkPt3d)
Next
'System.Threading.Thread.Sleep(1000)
'Dim oSpline As SketchSpline3D = oSketch3d.SketchSplines3D.Add(oPoints,SplineFitMethodEnum.kSmoothSplineFit)
Dim oSpline As SketchControlPointSpline3D = oSketch3d.SketchControlPointSplines3D.Add(oPoints)

'Dim oLine1 As SketchLine3D = oSketch3d.SketchLines3D.AddByTwoPoints(oPoints(1), oPoints(2), True, (2.54))
'Dim oLine2 As SketchLine3D = oSketch3d.SketchLines3D.AddByTwoPoints(oPoints(2),oPoints(3),True,(2.54))
'Dim oLine3 As SketchLine3D = oSketch3d.SketchLines3D.AddByTwoPoints(oPoints(3),oPoints(4),True,(2.54))

 I'm sure one of the others here on the forum will review this issue, see the problem, and fix it.

[Anonymous]

Hi WCrihfield,

First, thanks for your reply.  I was beginning to wonder if there would be any replies.

Second, I was hoping that the Autodesk object library contained a method or function that was the equivalent of clicking on the Points option, specifying that a spline curve should be created, and then selecting the .xlsx file.  Apparently not, or at least, you didn't find it either.  While I was waiting, I continued to try to solve the puzzle, and eventually decided to try to do it the hard way, by doing pretty much exactly what you did--create an ObjectCollection object, open the Excel file, do a do/while loop (instead of for/next, as my .xlsx files contain an unknown number of coordinate sets) to read through each row of the spreadsheet, create a SketchPoint3D object from each set of coordinates, and then add that SketchPoint3D object both to the 3D Sketch (oSketch3D in your code) and to an ObjectCollection object (oPoints).

Once all of the SketchPoint3D objects were added to the 3D Sketch (oSketch3D) and to the ObjectCollection (oPoints), I then tried to feed that ObjectCollection (oPoints) to the .add function of the SketchSplines3D collection of the Sketch3D object (oSketch3D), as such in your code:

Dim oSpline As SketchSpline3D = oSketch3d.SketchSplines3D.Add(oPoints,SplineFitMethodEnum.kSmoothSplineFit)

Which doesn't quite work.  But we're close.  I think this is the "tiny detail" you missed:

The SketchSplines3D.Add function requires a collection of FitPoints (transient geometry) as the first argument.  In your code, you add SketchPoint3D objects to the oPoints collection object.

The only thing that has to be done to your code is to modify the last statement in your For/Next loop.  Instead of:

     oPoints.Add(oSkPt3d)

Let it be:

     oPoints.Add(oPoint)

oPoints thereby becomes a collection of FitPoint objects instead of a collection of SketchPoint3D objects and the SketchSplines3d.Add function successfully creates oSpline and adds it to the oSketch3D object.

Now, can anyone explain why the SketchControlPointSpline3D.Add function will take a collection of either FitPoint objects or SketchPoint3D objects, but the SketchSpline3D.Add function only accepts a collection of FitPoints.  Am I missing something?

[WCrihfield]

I'm glad it's working for you.  I tried it both ways before posting it that last time, but couldn't get it to work for me, even though everything seemed to be in order.  I still can't get either way to work.  I created two different ObjectCollections this time. One for the Transient Point objects, and one for the SketchPoint3D objects.  I've tried using both collections as the variable within both the SketchSplines3d.Add() and the SketchControlPointSplines3D.Add() functions.  I've tried changing the sketch points from HoleCenter points to regular points (by changing True or False when creating them), but that didn't make any difference in either function.  I tried commenting out all the sketch point creation and its collection object, and only creating a sketch3D object with nothing in it, then creating the splines from the transient points, but that wouldn't work for either function either.  They both have very similar error messages, which interpreted to say that it ran into an error while trying to execute that last Add() function.  It creates the Sketch3d, Creates all the points and sketch points, creates the collections, and adds them into the collections, but that last line always errors out.  I can't quite understand why though.  Maybe it doesn't like my point locations, and has a hard time creating a spline to fit them.

Here's my code now.

Dim oPDoc As PartDocument = ThisApplication.ActiveDocument
Dim oPDef As PartComponentDefinition = oPDoc.ComponentDefinition
Dim oXLFile As String = "S:\Engineering\SHARED\Tests\Points To Import.xlsx"
Dim oSheet As String = "Sheet1"
GoExcel.Open(oXLFile, oSheet)
GoExcel.DisplayAlerts = False
GoExcel.TitleRow = 2
GoExcel.FindRowStart = 3
Dim oTG As TransientGeometry = ThisApplication.TransientGeometry
Dim oTO As TransientObjects = ThisApplication.TransientObjects
Dim oCoords(2) As Double
Dim oPoint As Point
Dim oSkPt3d As SketchPoint3D
Dim oPoints As ObjectCollection = oTO.CreateObjectCollection
Dim oSkPoints As ObjectCollection = oTO.CreateObjectCollection
Dim oSketch3d As Sketch3D = oPDef.Sketches3D.Add()
For i As Integer = 3 To 10
	oCoords(0) = GoExcel.CellValue("A" & i)
	oCoords(1) = GoExcel.CellValue("B" & i)
	oCoords(2) = GoExcel.CellValue("C" & i)
	oPoint = oTG.CreatePoint(oCoords(0), oCoords(1), oCoords(2))
	oPoints.Add(oPoint)
	oSkPt3d = oSketch3d.SketchPoints3D.Add(oPoint, True)
	oSkPoints.Add(oSkPt3d)
Next
System.Threading.Thread.Sleep(1000)
Dim oSpline As SketchSpline3D = oSketch3d.SketchSplines3D.Add(oPoints,SplineFitMethodEnum.kSmoothSplineFit)
'Dim oSpline As SketchControlPointSpline3D = oSketch3d.SketchControlPointSplines3D.Add(oPoints)

[Anonymous]

Weird.  I took the code from your latest post, pasted it into the iLogic rule editor window, made exactly two edits to the code (replaced the line identifying the Excel file to fit my circumstances, and replaced the last line of your For/Next loop to add the oPoint object to the oSkPoints collection, instead of oSkPt3d, and Voila, it worked.  The modified code is below.  I'll attach both the Excel file I used and the part file, which started out as a blank (mm) part file.

Dim oPDoc As PartDocument = ThisApplication.ActiveDocument
Dim oPDef As PartComponentDefinition = oPDoc.ComponentDefinition
'Dim oXLFile As String = "S:\Engineering\SHARED\Tests\Points To Import.xlsx"
Dim oXLFile As String = "S:\Test\XYZ_Points_to_Import.xlsx"
Dim oSheet As String = "Sheet1"
GoExcel.Open(oXLFile, oSheet)
GoExcel.DisplayAlerts = False
GoExcel.TitleRow = 2
GoExcel.FindRowStart = 3
Dim oTG As TransientGeometry = ThisApplication.TransientGeometry
Dim oTO As TransientObjects = ThisApplication.TransientObjects
Dim oCoords(2) As Double
Dim oPoint As Point
Dim oSkPt3d As SketchPoint3D
Dim oPoints As ObjectCollection = oTO.CreateObjectCollection
Dim oSkPoints As ObjectCollection = oTO.CreateObjectCollection
Dim oSketch3d As Sketch3D = oPDef.Sketches3D.Add()
For i As Integer = 3 To 10
	oCoords(0) = GoExcel.CellValue("A" & i)
	oCoords(1) = GoExcel.CellValue("B" & i)
	oCoords(2) = GoExcel.CellValue("C" & i)
	oPoint = oTG.CreatePoint(oCoords(0), oCoords(1), oCoords(2))
	oPoints.Add(oPoint)
	oSkPt3d = oSketch3d.SketchPoints3D.Add(oPoint, True)
	'oSkPoints.Add(oSkPt3d)
	oSkPoints.Add(oPoint)
Next
System.Threading.Thread.Sleep(1000)
Dim oSpline As SketchSpline3D = oSketch3d.SketchSplines3D.Add(oPoints,SplineFitMethodEnum.kSmoothSplineFit)
'Dim oSpline As SketchControlPointSpline3D = oSketch3d.SketchControlPointSplines3D.Add(oPoints)

[WCrihfield]

Yes. When I tested with your excel file, it worked.

After a very brief comparison, it clicked, and I finally realized why mine wasn't creating the spline.

I had changed my excel files values several times, including how many rows had data in them.

The number of loops I was telling it to do in the code didn't match the number of rows of filled in data available within my Excel file.  This wasn't a problem for creating the points, or sketch points, or collections, but was a problem for the spline creation.

Once I updated my code to match the number of rows of data available within my Excel file, it worked like a charm.

I knew it was going to be something really simple like that.

So I dug around to find the code I used to use to find the last row on a sheet that contained data, and I found it.

You have to access the Excel app the hard way (not so bad, just no intellisence type feedback) to use this though.

See below:

Dim oExcelApp = CreateObject("Excel.Application")
oExcelApp.DisplayAlerts = False
Dim oXLFile As String = "S:\Engineering\SHARED\Tests\Points To Import.xlsx"
Dim oSheet As String = "Sheet1"
Dim oWB = oExcelApp.Workbooks.Open(oXLFile)
Dim oWS = oWB.Sheets.Item(oSheet)
Dim oLastRowUsed As Integer = oExcelApp.ActiveSheet.UsedRange.Rows.Count
MsgBox("Last row being used is Row#:  " & oLastRowUsed)
oWB.Close
oExcelApp = Nothing

So, I adapted this technique into my current code to make it 'smarter', and prevent that error.

Now my code looks like this...and it works great.

Dim oPDoc As PartDocument = ThisApplication.ActiveDocument
Dim oPDef As PartComponentDefinition = oPDoc.ComponentDefinition
Dim oTG As TransientGeometry = ThisApplication.TransientGeometry
Dim oTO As TransientObjects = ThisApplication.TransientObjects
Dim oCoords(2) As Double
Dim oPoint As Point
Dim oSkPt3d As SketchPoint3D
Dim oSketch3d As Sketch3D = oPDef.Sketches3D.Add()

Dim oExcelApp = CreateObject("Excel.Application")
oExcelApp.DisplayAlerts = False
Dim oXLFile As String = "S:\Engineering\SHARED\Tests\Points To Import.xlsx"
Dim oSheet As String = "Sheet1"
Dim oWB = oExcelApp.Workbooks.Open(oXLFile)
Dim oWS = oWB.Sheets.Item(oSheet)
Dim oLastRowUsed As Integer = oExcelApp.ActiveSheet.UsedRange.Rows.Count
'MsgBox("Last row being used is Row#:  " & oLastRowUsed)
Dim oTitleRow As Integer = 2
Dim oFirstDataRow As Integer = 3

For i As Integer = oFirstDataRow To oLastRowUsed
	oCoords(0) = oWS.Range("A" & i).Value
	oCoords(1) = oWS.Range("B" & i).Value
	oCoords(2) = oWS.Range("C" & i).Value
	oPoint = oTG.CreatePoint(oCoords(0), oCoords(1), oCoords(2))
'	oPoints.Add(oPoint)
	oSkPt3d = oSketch3d.SketchPoints3D.Add(oPoint, True)
Next
Dim oSkPoints As ObjectCollection = oTO.CreateObjectCollection(oSketch3d.SketchPoints3D)
System.Threading.Thread.Sleep(1000)
Dim oSpline As SketchSpline3D = oSketch3d.SketchSplines3D.Add(oSkPoints,SplineFitMethodEnum.kSmoothSplineFit)
oWB.Close
oExcelApp = Nothing

[WCrihfield]

Also, FYI:  (you questioned this in one of your earlier posts)

See the two online help pages concerning the Add() function of both types of splines.

It appears that they can both accept an object collection of SketchPoint3D objects, but is a little unclear whether both can accept a collection of Point objects.  Even though you've tried it with the point collection, and you said it worked.

In fact, the SketchSplines3D.Add method even says that if you don't supply a SketchPoint3D objects, it will automatically create them.

SketchControlPointSplines3D.Add Method 

SketchSplines3D.Add Method 

[ltujxm1]

This works fine, I wonder what needs to be done to have condition to use existing points to create 3dspline instead to import from excel.

[WCrihfield]

Hi @ltujxm1.  When you say "existing points", do you mean WorkPoints, SketchPoint3D objects, 2d SketchPoint objects, vertices, or hardcoded coordinates within the code for specifying transient Point objects?

[ltujxm1]

Sorry to not be precise enough. I mean SketchPoint3D, e.g. points are imported from autocad using inventor import option in 3d sketch.  I have attached example (It contains your rule which I tried to configure, but too much for me) 

[WCrihfield]

Hi @ltujxm1.  Unfortunately, I was not able to use the part you uploaded, because it was probably created in a newer version of Inventor than mine (I'm using 2022.4.1 at the moment).  But I did create an iLogic rule you can try out.  This rule simply gets the first 3D sketch that it finds in the part, then gathers all 3D sketch points that may exist in that sketch to an object collection.  Then it tries to create a SketchSpline3D from those points.  There are 3 possible fit methods available to choose from, but I am specifying the 'smooth' one in this example.  The other two fit methods are an 'ACAD' version, and a 'Sweet' version.

Dim oPDoc As PartDocument = ThisDoc.Document
Dim oPDef As PartComponentDefinition = oPDoc.ComponentDefinition
If oPDef.Sketches3D.Count = 0 Then Exit Sub
Dim oSketch3D As Sketch3D = oPDef.Sketches3D.Item(1)
Dim oSP3Ds As SketchPoints3D = oSketch3D.SketchPoints3D
If oSP3Ds.Count = 0 Then Exit Sub
Dim oSPColl As ObjectCollection = ThisApplication.TransientObjects.CreateObjectCollection
For Each oSP3D As SketchPoint3D In oSP3Ds
	oSPColl.Add(oSP3D)
Next
Dim oSSp3D As SketchSpline3D = Nothing
Try
	oSSp3D = oSketch3D.SketchSplines3D.Add(oSPColl, SplineFitMethodEnum.kSmoothSplineFit)
Catch
	Logger.Error("Error creating SketchSpline3D.")
End Try

[ltujxm1]

Works great,  these points represent coil with pitch and diameter variation, after spline creation only used split spline, since this routine closes spline.  Thank you