Insert multiple components from excel

[kpk7VYPK]

Hi, I have the following code, which does what I want it to do, but is there a way to write it so that I can do one line of the 'component.add' code and it finds all the cells in the excel sheet that contain data and insert them all without having to specify the row number?

Dim sFile As String = "C:\VaultWorkspace\Designs\PROJ Skiold\Personlige\KPK\"& cstr (iProperties.Value("Project", "Part Number")) & " DK250S Config.xlsx"
GoExcel.Open(sFile, "Test")
Dim FirstCol = "A"
Dim FirstRow = "2"

Dim componentA = Components.Add("" , "CO_D" & CStr(GoExcel.CellValue(sFile, "Test", FirstCol & FirstRow)) & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)
Dim componentB = Components.Add("", "CO_D" & CStr(GoExcel.CellValue(sFile, "Test", FirstCol & (1+FirstRow))) & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)
Dim componentC = Components.Add("", "CO_D" & CStr(GoExcel.CellValue(sFile, "Test", FirstCol & (2+FirstRow))) & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)
Dim componentD = Components.Add("", "CO_D" & CStr(GoExcel.CellValue(sFile, "Test", FirstCol & (3+FirstRow))) & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)

[FINET_Laurent · Accepted solution]

Hi @kpk7VYPK,

Sure, here is a small code that does just this : 

Dim sFile As String = "C:\VaultWorkspace\Designs\PROJ Skiold\Personlige\KPK\"& CStr (iProperties.Value("Project", "Part Number")) & " DK250S Config.xlsx"
GoExcel.Open(sFile, "Test")

Dim FirstCol = "A"
Dim FirstRow = "2"

For i As Integer = 0 To 999
	Dim val As String = CStr(GoExcel.CellValue(sFile, "Test", FirstCol & (FirstRow + i)))
	If val Is Nothing Then Exit For
	
	Components.Add("" , "CO_D" & val & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)

Next

Does this suits your needs?

Kind regards,

FINET L.

[kpk7VYPK]

Thanks so much! Been trying to figure this out for ages!

[kpk7VYPK]

Ok here is another puzzle on the same code. I have UCS points on each part for constraining and had previously written this code. But now the components arent specified by name. Is there a way to make this work?

Constraints.AddUcsToUcs("UcsToUcs1", componentA , "UCS_turnsection", componentB, "UCS_turnsection", xOffset := 0, yOffset := 0, zOffset := 0)
Constraints.AddUcsToUcs("UcsToUcs2", componentB, "UCS_drivesection", componentC, "UCS_turnsection", xOffset := 0, yOffset := 0, zOffset := 0)
Constraints.AddUcsToUcs("UcsToUcs3", componentC, "UCS_drivesection", componentD, "UCS_drivesection", xOffset := 0, yOffset := 0, zOffset := 0)

[FINET_Laurent]

Hi @kpk7VYPK,

Note you can mark multiple replies as "solution" on your topic. Here is a code that would do it : 

Dim sFile As String = "C:\VaultWorkspace\Designs\PROJ Skiold\Personlige\KPK\"& CStr (iProperties.Value("Project", "Part Number")) & " DK250S Config.xlsx"
GoExcel.Open(sFile, "Test")

Dim FirstCol = "A"
Dim FirstRow = "2"
Dim prevComp As ManagedComponentOccurrence

For i As Integer = 0 To 999
	Dim val As String = CStr(GoExcel.CellValue(sFile, "Test", FirstCol & (FirstRow + i)))
	If val Is Nothing Then Exit For
	
	Dim newComp = Components.Add("" , "CO_D" & val & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)
	
	If i <> 0 Then Constraints.AddUcsToUcs("UcsToUcs1", prevComp, "UCS_turnsection", newComp, "UCS_turnsection", xOffset := 0, yOffset := 0, zOffset := 0)	
	prevComp = newComp
	
Next

Does this suits your needs?

Kind regards,

FINET L.

[kpk7VYPK]

Thanks, I am running into this error:

[FINET_Laurent · Accepted solution]

@kpk7VYPK,

Weird, the code is running fine on my end. I added the declaration of "newComp" on line 6:

Dim sFile As String = "C:\VaultWorkspace\Designs\PROJ Skiold\Personlige\KPK\"& CStr (iProperties.Value("Project", "Part Number")) & " DK250S Config.xlsx"
GoExcel.Open(sFile, "Test")

Dim FirstCol = "A"
Dim FirstRow = "2"
Dim prevComp, newComp As ManagedComponentOccurrence

For i As Integer = 0 To 999
	Dim val As String = CStr(GoExcel.CellValue(sFile, "Test", FirstCol & (FirstRow + i)))
	If val Is Nothing Then Exit For
	
	newComp = Components.Add("" , "CO_D" & val & ".ipt", position := Nothing, grounded := False, visible := True, appearance := Nothing)
	
	If i <> 0 Then Constraints.AddUcsToUcs("UcsToUcs1", prevComp, "UCS_turnsection", newComp, "UCS_turnsection", xOffset := 0, yOffset := 0, zOffset := 0)	
	prevComp = newComp
	
Next

Does this fix the problem? 

Kind regards,

FINET L.

[kpk7VYPK]

Oh sorry i hadn't copied in the whole new code. It works somewhat, but not 100% due to the different UCS names (turn section, drive section ,etc). Its a long winded solution I am exploring so I am not even sure it is possible to make work. 😐

[FINET_Laurent]

@kpk7VYPK,

Indeed it would be dificult to make the code work if the logic in the UCS names is not consistent. 
Can you explain us a bit more about the naming logic? We might find something. 
How many components are there ?

Kind regards,

FINET L.

[kpk7VYPK]

Its a kind of 'configurator' I am trying to code as an interim solution until we get a fully integrated configurator. It is for different transport machines, like conveyor belts etc. So there are many different components and constraint names that need to be connected. Getting the parts into the assembly from the code you sent is a huge step forward, at least this way we can use the BOM from inventor and manually do the constraints.  🙏

[kpk7VYPK]

Hi again, thanks so much for your help yesterday. My code is coming along nicely. One area i am stuck is to get the components.add part of the code to place the parts into the assembly multiple times based on data in the excel sheet. I have worked out how to override the BOM, but i dont think that is good enough. I need the parts to be added in multiple times. Any way to do this?

Thanks 🙂