topic Re: Make assembly via excel in Inventor Programming Forum

Make assembly via excel

berry.lejeune — Fri, 18 Nov 2022 10:02:25 GMT

Hello all,

Working at a company where we make gravestones, I'm the one drawing them and then they go to shop and overthere all is made. In the process of making it quicker to work I've made some assemblies which are driven by a part that has a sketch in it and this sketch I linked to a form so that in my assembly environment I can call up this form via iLogic, fill out all the dimensions and then update the assembly and the complete stone is ready for production.

All the base part are always the same. The only things that change are the headstone and the plate (red arrows)

I know I can link all the parameters to an excel file and from there I can drive the model. But is there also a way that in the excel file I can choose the headstone and the plate so my assembly is immediatly correct.

Now I still go into my assembly, and replace the headstone and plate manually.

We have about 350 headstones and 150 plates.

This would be handy for me but also for my boss for when I'm not in the office and we have customers, he quickly can show how the stone would look like.

In an ideal world I would make the excel file, fill out all the dimensions (like I do now in my form) and the maybe from a dropdown list I would be able to choose the correct headstone and plate

Then do the update in Inventor and the complete assembly would be ready.

If this should work, I know it will take some time to get all the headstone and plates in all the excel files and link them somehow to Inventor, but in the end it'll pay off.

Thanks

Re: Make assembly via excel

Daan_M — Fri, 18 Nov 2022 11:27:37 GMT

Goedemiddag,

Parts;

This is definently possible.

An easy way of doing this while staying within the Inventor UI, is making iParts. Try doing the following;

Under the 'Manage' Tab, there should be an option called 'create iPart', here you can select all the parameters you want to predifine per gravestone. After completing this you can simply select your gravestone and it will be drawn according to the specific values. This can be done through the Excel spreadsheet or within the iPart UI table, ofcourse Excel is more flexible.

Watch the link for a tutorial;

https://www.youtube.com/watch?v=Lm0DYRpQzzQ

Assembly;

This is also possible. You need to write some iLogic to import the parts you want into an assembly. In addition you can use iMates to make sure all the parts end up in the right position.

I recently did this with electric motor assemblies consisting of 7 parts which all have over 20 subvariants, so the combinations are endless. I gave iMates to the 7x20 = 140 parts and now i can make whatever combination i want with the click of a button, ofcourse defining the iMates still takes some time but in the long run it does pay off.

Re: Make assembly via excel

TechInventor20 — Fri, 18 Nov 2022 11:20:07 GMT

Hi,

Simple way is to have all the 350 stones at one place in the same folder.

Then name them exactly like you have in excel. That way you could say.

;edit

I did not test the code, currently not in inventor to test it. Hope I set you up in the right direction.

Dim Datasheet as string = "Your excel location + excel name"
Dim Sheet as string = "Name of sheet"
Dim Headstone_Excel as string = GoExcel.CellValue(Datasheet, Sheet, "A1")
Dim File_locations as string = "The location of your files"

Dim oDoc As AssemblyDocument = ThisDoc.Document 
Dim oComps As ComponentOccurrences = oDoc.ComponentDefinition.Occurrences 
For Each oComp As ComponentOccurrence In oComps 'Go true all components

Dim checking_value as string = Left(oComp.name,2)
If checking_value = "rs" then
    If oComp.name = Headstone_excel then
         '"do nothing"
    Else
         Components.Delete(oComp & ":1")
         Dim ass = Components.Add(Headstone_excel & ":1", File_locations  & Headstone_excel & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)
    End if
Else
    Dim ass = Components.Add(Headstone_excel & ":1", File_locations  & Headstone_excel & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)
End if

'Now put all the parameters in.

Parameter.value("The part in assembly", "d20") = 200 ' example 'Parameter.value("rs - 01:1", "Height")
'etc.

Re: Make assembly via excel

berry.lejeune — Mon, 21 Nov 2022 08:29:35 GMT

Goedemorgen,

Creating the iMates shouldn't be an issue. That'll figure out with some tutorials. As I go along the daily business I can add those to all of my parts. The only issue I have is the iLogic code to place the parts in my assembly.
I've got several iLogic's running now but all of them I got from here and then the only thing what I needed to do is change some locations and names in them and then they work. But starting to write one is not happening with me

Re: Make assembly via excel

berry.lejeune — Mon, 21 Nov 2022 09:01:14 GMT

@TechInventor20

I changed some of the parameters in the code but I get a fault message on some of the lines (8-15)

Re: Make assembly via excel

TechInventor20 — Mon, 21 Nov 2022 09:14:56 GMT

Hi,

Change this:

For Each oComp As ComponentOccurrence In oComps 'Go true all components

Dim checking_value as string = Left(oComp.name,2)
If checking_value = "rs" then
    If oComp.name = Headstone_excel then
         '"do nothing"
    Else
         Components.Delete(oComp & ":1")
         Dim ass = Components.Add(Headstone_excel & ":1", File_locations  & Headstone_excel & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)
    End if
Else
    Dim ass = Components.Add(Headstone_excel & ":1", File_locations  & Headstone_excel & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)
End if

to this:

For Each oComp As ComponentOccurrence In oComps 'Go true all components

Dim checking_value as string = Left(oComp.name,2)
If checking_value = "rs" then
    If oComp.name = Headstone_excel then
         '"do nothing"
    Else
         Components.Delete(oComp.name)
         Dim ass = Components.Add(Headstone_excel & ":1", File_locations  & Headstone_excel & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)
    End if
Else
End if
Next

Re: Make assembly via excel

berry.lejeune — Mon, 21 Nov 2022 10:29:32 GMT

@TechInventor20

I changed a couple of small things because the code couldn't find my parts.

Dim Datasheet As String = "I:\Workspace\Bibliotheek\Gielkens\Templates grafstenen\Test excel\test.xlsx"
Dim Sheet As String = "test"
Dim Headstone_Excel As String = GoExcel.CellValue(Datasheet, Sheet, "F3")
Dim File_locations As String = "I:\Workspace\Bibliotheek\Gielkens\Ruggen\"

Dim oDoc As AssemblyDocument = ThisDoc.Document 
Dim oComps As ComponentOccurrences = oDoc.ComponentDefinition.Occurrences 
For Each oComp As ComponentOccurrence In oComps 'Go true all components

Dim checking_value As String = Left(oComp.Name,2)
If checking_value = "Rs" Then
    If oComp.Name = Headstone_Excel Then
         '"do nothing"
    Else
         Components.Delete(oComp.Name)
         Dim ass = Components.Add(Headstone_Excel & ":1", File_locations  & Headstone_Excel & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)
    End If
Else
End If
Next

Now just one more thing. When I let the code run it places my headstone from the library with the headstone i requested in my excel list 😃

Is there now a way that the headstone will come on a defined place in the assembly. Now the current headstone is deleted and the new one (defined in excel) is just put down without any constraints

In my assembly the new headstone should just replace the existing one

Re: Make assembly via excel

TechInventor20 — Mon, 21 Nov 2022 11:31:38 GMT

Then maybe this code will work the way you wan't it. I set the red part of the code off function and the green part is new.

Dim Datasheet As String = "I:\Workspace\Bibliotheek\Gielkens\Templates grafstenen\Test excel\test.xlsx"
Dim Sheet As String = "test"
Dim Headstone_Excel As String = GoExcel.CellValue(Datasheet, Sheet, "F3")
Dim File_locations As String = "I:\Workspace\Bibliotheek\Gielkens\Ruggen\"

Dim oDoc As AssemblyDocument = ThisDoc.Document 
Dim oComps As ComponentOccurrences = oDoc.ComponentDefinition.Occurrences 
For Each oComp As ComponentOccurrence In oComps 'Go true all components

Dim checking_value As String = Left(oComp.Name,2)
If checking_value = "Rs" Then
    If oComp.Name = Headstone_Excel Then
         '"do nothing"
    Else
         'Components.Delete(oComp.Name)
         'Dim ass = Components.Add(Headstone_Excel & ":1", File_locations  & Headstone_Excel & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing) 
         Component.Replace(oComp.Name, File_locations & Headstone_Excel & ".ipt", True)
    End If
Else
End If
Next

Veel succes!

Groet

Re: Make assembly via excel

berry.lejeune — Thu, 24 Nov 2022 07:21:56 GMT

Thanks @TechInventor20 ,

with the latest addition you gave me, the existing headstone and plate are automatically replaced in my model when I chang the excel sheet and they stay in place 👍

I've got several variations of complete gravestons and will now incorperate these codes in all of them.

Just one last thing (I hope 😀) Normally with design assistant I copy an existing assembly to a new project folder (with new names) and there I start to work on my base part with all the dimensions in.

I think with this system now, I will first make my complete assembly and then do via the design assistant a copy to the project folder, but will the excel file also copy to this new folder or will the link be broken?

Re: Make assembly via excel

TechInventor20 — Mon, 28 Nov 2022 11:17:43 GMT

What you could do is make a copy of the excel and put it in the same folder as the assembly.

you could say that excel sheet is located at that place

Dim Datasheet As String 
if Thisdoc.path = "" then
Datasheet = "I:\Workspace\Bibliotheek\Gielkens\Templates grafstenen\Test excel\test.xlsx"
Else
Datasheet = Thisdoc.Path
End if