Autodesk Inventor API – Part 3

By Allen Gager

Previous parts of this series: Introduction, Part 1 and Part 2.

Okay, now we get to the part where it gets a little bit harder. We need to access our Excel document and retrieve the values from the appropriate cells and supply those values so that Inventor can create a Material Definition. There are many ways to do this, some better than others. I know some of you out there that are experienced programmers would opt for a different method. My goal is to find existing code and come as close as I can to what we need to get the job done. Also, I do not know how the Excel document is formatted. This method will allow us to specify which cells we want to pull information from.

The first place I usually look is on the Autodesk Inventor Customization discussion group. This is another excellent resource.  Much of what you need is probably contained within these pages. There are lots of helpful, knowledgeable people on this site. Many of the Inventor API developers cruise the site and help those in need as well. Many times you will find exactly what you need, already done for you, or at least pointing you in the right direction. I did a quick search on this site and found a lot of references to Excel. But this time I didn’t find quite what I was looking for.  However, I did find another fellow who wanted to load Materials from an Excel sheet.

Tip: When doing a search in the discussion group, be sure to set the Date Range to All.  This will return results from long ago, which are probably still relevant.

The next stop is to do a Google search.  Now, when you do a Google search on something like “Excel VBA” you get more than a few results returned. Sometimes it helps to remove criteria from a search. For example, I am not interested in Dot NET code for this example. So I changed my search to read Excel VBA  -.net. By providing a space then a “-” in front of what I don’t want, Google will remove those items from the search results. This works on eBay searches too. You can combine the filters too: -.net  -.asp and so on.

Now sometimes it takes a bit of patience to get what you are looking for. After a little bit of trial and error, I just typed exactly what I wanted the program to do. This is my search string: “get value from excel vb -vb.net“. This search came back with this  page on Unagi Bay.  It was located halfway down the first page of the search results.  This sample code has pretty close to what we want.  Sometimes you just get lucky.

Option Explicit
Dim objApp, objWbs, objWorkbook, objSheet

Set objApp = CreateObject(“Excel.Application”)
Set objWbs = objApp.WorkBooks
objApp.Visible = False
Set objWorkbook = objWbs.Open(“[path to spreadsheet file]”)
Set objSheet = objWorkbook.Sheets(“Sheet1”)

MsgBox objSheet.Range(“C5”).Value

objWorkbook.Close False
objWbs.Close
objApp.Quit

Set objSheet = Nothing
Set objWorkbook = Nothing
Set objWbs = Nothing
Set objApp = Nothing

MsgBox “Done”

The above code is taken directly from the here.

Now it’s time to get into a little bit of programming.  The code we retrieved from Unagi Bay needs a little bit of work to integrate it into our other code. Because we are writing this code inside Inventor’s development environment, we need to tell Inventor about Excel. Otherwise Inventor will have no idea what an Excel object is or how to communicate with Excel. We can do this by setting a Reference. From the VBA Editor select Tools > References.  A dialog box will open up. Scroll down until you find Microsoft Excel XX.X Object Library. The XX.X will be a number related to the version of Excel you have on your system. In my case I am using Excel 2007 so my reference looks like this “Microsoft Excel 12.0 Object Library“. Select the check box, and then select OK.

Inventor will now have access to all of the Excel objects and functionality. In fact, we could reverse this and write this code in Excel and reference Inventor, then Excel would have access to the Inventor objects. But let’s not confuse things.

Before we get too much farther, we should probably look at the spreadsheet that we are going to be pulling this data from. I have two spreadsheet files. One is the original file from the gentleman who posed the question that got this series started. The other file is the version he translated into English for me. In this case Inventor will accept either file.

materials.turkish.xlsx and materials.english.xlsx.

After looking at the files we will need to account for several items. The first item is that there is not a value for every field in an Inventor Material. The second item is that Cells containing the values we want are dispersed throughout the file.

Let’s take a look at our code to create a Material. We want to combine the code we found to read the values in Excel and the code we are using to create the Material. Let’s take the first part that defines or Dimensions the variables to describe Excel and put that at the top.

Public Sub CreateMaterial()

‘ Set a reference to Excel
Dim objApp, objWbs, objWorkbook, objSheet

Set objApp = CreateObject(“Excel.Application”)
Set objWbs = objApp.WorkBooks
objApp.Visible = False
Set objWorkbook = objWbs.Open(“[path to spreadsheet file]”)
Set objSheet = objWorkbook.Sheets(“Sheet1”)

‘ Set a reference to the part document.
‘ This assumes a part document is active.
Dim oPartDoc As PartDocument
Set oPartDoc = ThisApplication.ActiveDocument

‘ Create a new material.
Dim oNewMaterial As Material
Set oNewMaterial = oPartDoc.Materials.Add(“My Material”, 11.37)

‘ Define the other properties of the material
oNewMaterial.LinearExpansion = 5.73
oNewMaterial.PoissonsRatio = 0.38
‘ Arbitrarily assigns the first render style in the render styles collection.
oNewMaterial.RenderStyle = oPartDoc.RenderStyles.Item(1)
oNewMaterial.SpecificHeat = 124.3
oNewMaterial.ThermalConductivity = 310.2
oNewMaterial.UltimateTensileStrength = 210.45
oNewMaterial.YieldStrength = 185.5
oNewMaterial.YoungsModulus = 76.37

‘ Close Excel
objWorkbook.Close False
objWbs.Close
objApp.Quit

Set objSheet = Nothing
Set objWorkbook = Nothing
Set objWbs = Nothing
Set objApp = Nothing

MsgBox “Done”

End Sub

For the value “[path to spreadsheet file]” put the path to your spread sheet. The line in my code looks like this.

Set objWorkbook = objWbs.Open(“C:UsersallengDocumentsDeelipmaterials.english.xlsx”)

Do the same with the Sheet Name

Set objSheet = objWorkbook.Sheets(“Bonded Refrakterler”)

We will put the rest of the code at the bottom after the Material is created. What we haven’t done is deal with the code that actually returns the Cell value.

MsgBox objSheet.Range(“C5”).Value

MsgBox is will display a Message Box every time a value is read.  So we will get rid of the MsgBox part.

objSheet.Range(“C5”).Value

Now we need a way to update the Range each time a Material is created. Because we know how the spreadsheet is laid out we don’t have to worry about updating the column each time, just the rows. We can create a counter to step through and over the rows. This means that we will have to build the Range value for each cell based on its column and row on the fly. We can do this with concatenation. Concatenation is the joining of strings. We will also need a way to loop through the rows. Again, there are different ways to do this. We know that the first Material name is located in Cell “C5”. We can create a loop by using a For Next loop.

For index = 5 to 40 means that index will start with a value of 5 and continue to loop and add 1 to the value of index until index equals 40, the end of our populated rows. You would adjust these values to meet the requirements of your spreadsheet.

Set oNewMaterial = oPartDoc.Materials.Add(objSheet.Range(“C” & CStr(index)).Value, objSheet.Range(“O” & CStr(index)))

If we look at the line of code above, we see that we are retrieving the value of the desired Cell. The problem is that index is an integer and the Range expects a string value. We temporarily convert index to a string using the Cstr() function.
For the first argument (Material Name) we are setting the Range to column “C” and whatever the current value of index is. The second argument is set to column “O” and the current value of index. Now this spreadsheet does not have values for the rest of the Material properties. So we will set the values to zero in the code. You would simply supply the following code for the other arguments.

objSheet.Range(“[Column]” & CStr(index)).Value

Our spreadsheet has a number of columns for the Thermal Conductivity property.  I have set the Range for column “T”.  Some additional decisions will need to be made by the owner to figure out which column to use when creating Materials. Looking at our spreadsheet sample we also need to account for the fact that there is no useable material data in rows 21 or 22. We need our program to skip over these rows. When the code reaches the statement “Next index”, index will be incremented by a value of one. When index is 20, it will be reset to 22 and then it will immediately be incremented by Next to the value of 23. This lets us step right over the rows that don’t have any valuable Material information.

Final Completed Code

Public Sub CreateMaterial()

Dim objApp, objWbs, objWorkbook, objSheet

Set objApp = CreateObject(“Excel.Application”)
Set objWbs = objApp.Workbooks
objApp.Visible = False
Set objWorkbook = objWbs.Open(“C:UsersallengDocumentsDeelipmaterials.english.xlsx”)
Set objSheet = objWorkbook.Sheets(“Bonded Refrakterler”)

‘ Set a reference to the part document.
‘ This assumes a part document is active.
Dim oPartDoc As PartDocument
Set oPartDoc = ThisApplication.ActiveDocument

‘ Create a new material.
Dim oNewMaterial As Material

Dim index As Integer

For index = 5 To 40

Set oNewMaterial = oPartDoc.Materials.Add(objSheet.Range(“C” & CStr(index)).Value, objSheet.Range(“O” & CStr(index)))

‘ Define the other properties of the material
oNewMaterial.LinearExpansion = 0
oNewMaterial.PoissonsRatio = 0
‘ Arbitrarily assigns the first render style in the render styles collection.
oNewMaterial.RenderStyle = oPartDoc.RenderStyles.Item(1)
oNewMaterial.SpecificHeat = 0
oNewMaterial.ThermalConductivity = objSheet.Range(“T” & CStr(index)).Value
oNewMaterial.UltimateTensileStrength = 0
oNewMaterial.YieldStrength = 0
oNewMaterial.YoungsModulus = 0

‘Skip over Rows 21 and 22
If index = 20 Then
index = 22
End If

Next index

‘ Close Excel

objWorkbook.Close False
objWbs.Close
objApp.Quit

Set objSheet = Nothing
Set objWorkbook = Nothing
Set objWbs = Nothing
Set objApp = Nothing

MsgBox “Done loading Materials”

End Sub

Create a new Part file and paste the code above into the Visual Basic Editor. Make sure to edit the path and file name for the spreadsheet. Some things to note:

1. There is no error checking in this routine.
2. The program will fail if the Material name already exists.
3. This code does not address the render style beyond taking the first element in the render styles collection. ( The answers can be found in Inventor Programming Help.)

While this series is not meant to teach you how to program, perhaps it will inspire you to look a little further at some of the resources available and to get you thinking about tasks that you do each day which might be improved using the Inventor API. I know many of you are thinking about the next step one might take with this example. You probably have already thought it would be great to have a dialog box display all the Materials in your Excel or Access data set and be able to search or filter them by different criteria. Then only load the one Material that you need. That functionality is certainly possible, and probably much more attainable than you might think. The pieces to do it are out there and available.

If you are out tooling around the net and you find some useful free code, be sure to leave the owner of the code a kind comment. That free code will most likely save you hours, if not days and contribute to your learning and enjoyment.  It may also strengthen and build your network of places to turn when you get stuck or need help. For that, I would like to thank all of those that have shared their experience and provided code samples, saving me countless hours of time throughout my career.

Go to Series Summary.