Issues Changing Parts Using iLogic from excel

[tsp62MT3AF]

Hye everyone,

I was wondering if I could please get some help. I am needing to use an excel spreadsheet to replace components in an assembly based off a pump model. Currently I will only need to be replacing the pump casing (Volute), bellhousing and the motor. I have created a excel spread sheet that has numerous columns but I am only going to be looking at 4 of them, being Pump model, Volute, Bellhousing and motor. I followed KETIVAVA on using excel and used their code however, when I try and run it, nothing changes. I have even bought in other files that look completely different and named them what they are in the excel spreadsheet and they do not swap. I have run it with different log level options and nothing is coming up. Can anyone point me in the right direction as to what has been going on. I made a more simplified spreadsheet just to get it going as well and nothing. 

Dim asmDoc As AssemblyDocument = ThisDoc.Document
Dim compDef As AssemblyComponentDefinition = asmDoc.ComponentDefinition
Dim allOcc As ComponentOccurrences = compDef.Occurrences
Dim excelFileLocation As String = "********\End Suction Pump Drawings\Pumps\Pump Layout Spreadsheet Test.xlsx"

work = GoExcel.FindRow(excelFileLocation, "Sheet1", "Pump Model", "=", PumpModel)

For Each occurrence As ComponentOccurrence In allOcc
	
	Try
		replacementModelName = GoExcel.CurrentRowValue(occurrence.Name)
	Catch
		Continue For
	End Try
	
	Try
		Component.Replace(occurrence.Name, replacementModelName, True)

	Catch
	End Try
	
Next
	

I have created a user parameter called PumpModel and a form as well. 

I have attached the spreadsheet.

Kind Regards,
Trent 

[A.Acheson]

Here is a method to do this. It will rely on finding a filename within a column and then with the new row determined by the form (parameter change) select a new file name and replace this occurrence. Note replacing occurrence will need a fullfilename, path and filename including extension. 

Dim asmDoc As AssemblyDocument = ThisDoc.Document
Dim compDef As AssemblyComponentDefinition = asmDoc.ComponentDefinition
Dim Occs As ComponentOccurrences = compDef.Occurrences

Dim excelFileLocation As String = "********\End Suction Pump Drawings\Pumps\Pump Layout Spreadsheet Test.xlsx"

'Automatic setting of Pump Model. 
MultiValue.List("PumpModel") = GoExcel.CellValues(excelFileLocation, "Sheet1", "A2", "A10")

'Intial List creation to ensure list not blank on creation.
If PumpModel = "" Then
	PumpModel = MultiValue.List("PumpModel").Item(0)
End If


Dim NewRow1 As Integer = GoExcel.FindRow(excelFileLocation, "Sheet1", "Pump Model", "=", PumpModel)
If NewRow1 = -1 Then MessageBox.Show("Error retrieving row, exiting", "Error Check") : Return
	
	
Dim OldFileName1 As String 
Dim NewFileName1 As String 	
	
For Each Occ As ComponentOccurrence In Occs
	
	Dim FileName As String = System.IO.Path.GetFileName(Occ.Definition.Document.FullFileName)
	Dim Path As String = System.IO.Path.GetDirectoryName(Occ.Definition.Document.FullFileName)
	
	'[Occurrence(1) replace
	'Check if occurrence filename is in the correct column, so we change the right occurrence.
	Dim OldRow1 As Integer = GoExcel.FindRow(excelFileLocation, "Sheet1", "Volute", "=", FileName)
	
	If OldRow1 = -1 Then MessageBox.Show("Error retrieving row, exiting", "Error Check") : Return
	
	OldFileName1 = GoExcel.CellValue(excelFileLocation, "Sheet1", "B" & OldRow1)
	
	If FileName = OldFileName1 Then
		NewFileName1 = GoExcel.CellValue(excelFileLocation, "Sheet1", "B" & NewRow1)
		
		Logger.Info("Path: " & Path)
		Logger.Info("NewRow: " & NewRow1)
		Logger.Info("OldRow: " & OldRow1)
		Logger.Info("FileName: " & FileName)
		Logger.Info("OldFileName: " & OldFileName1)
		Logger.Info("NewFileName: " & NewFileName1)
		
		Try
			Occ.Replace(Path & "\" & NewFileName1, False)
		Catch
		End Try

	End If
'[

	'[Occurrence(2) replace
		'Copy first structure and rename variables
	']

Next

[tsp62MT3AF]

Hi Alan,

Thank you for your help. I am having issues that is unable to find the row. I have changed the multi variables list to MEA41, MEA42 and MEA05 as per our naming convention, and made the same changes in the excel spreadsheet, however when I run the rule, it is coming up with Error Retrieving Row, which makes me think that it does not like something to do with the way I have named it maybe. Also when I select row 1 which is MEA41, it adds a part even through there is an error and another part is removed. I have attached all the files if that helps. 

Trent

[tsp62MT3AF]

Here are the other files

[A.Acheson]

I haven’t been able to open your files due to time constraints. So here is just a few tips. The -1 simply means the value your searching for in that column does not exist. This will also error out if you have blanks in between data so ensure you fill any blanks with let’s say N/A etc. Can you post the code your using here? This will at least help check the logic. I tested it last replacing just the volute and that seemed to work as expected. 

[A.Acheson]

After testing again I found the issue with the above code. It worked find with occurrences having filenames in one column but was failing if the filename did not exist in that column. A remedy for that would be just an "If Not Row = -1 " statement. However an easier method would be to eliminate needing to check the filename. This is achieved by stabilizing the occurrence Name with a generic name such as "Volute" and "Bellhousing". This is then a reference to find this correct occurrence in the assembly. Once you find this occurrence you can swap the FullFileName very easily. 

Updated Rule.

---

Dim AsmDoc As AssemblyDocument = ThisDoc.Document
Dim CompDef As AssemblyComponentDefinition = AsmDoc.ComponentDefinition

Dim ExcelFileLocation As String = "********\End Suction Pump Drawings\Pumps\Pump Layout Spreadsheet Test.xlsx"

'Automatic setting of Pump Model. 
MultiValue.List("PumpModel") = GoExcel.CellValues(ExcelFileLocation, "Sheet1", "A2", "A10")

'Intial List creation to ensure list is not blank on creation.
If PumpModel = "" Then : PumpModel = MultiValue.List("PumpModel").Item(0) : End If

Dim PumpModelRow As Integer = GoExcel.FindRow(ExcelFileLocation, "Sheet1", "Pump Model", "=", PumpModel)
	
For Each Occ As ComponentOccurrence In CompDef.Occurrences
	
	Dim Path As String = System.IO.Path.GetDirectoryName(Occ.Definition.Document.FullFileName)
	
	Select Case Occ.Name
	Case "Volute" 
		Try
			Occ.Replace(Path & "\" & GoExcel.CurrentRowValue("Volute"), False)
		Catch
		End Try
	Case "Bellhousing" 
		Try
			Occ.Replace(Path & "\" & GoExcel.CurrentRowValue("Bellhousing"), False)
		Catch
		End Try
	End Select
	
Next

[tsp62MT3AF]

Hi Alan,

Thank you very much for your help. It works well. I just need to work through the auto constraining as it does not seem to be working. Is that something you need to specify in the iLogic code?

Kind Regards,

Trent

[A.Acheson]

This can vary, if your using models created from the same part file there is more chance the constraints stick together when you use occurrence replace. If not what you will need is to use the origin planes and axis as much as possible avoiding proxy faces etc. The reason becomes clear later when you look at the code created using Components.Constraints.add. See below example focusing on the constraint.Insert section, you don't need the addding sections as your already replacing the occurrence. You will need to supply the occurrence names of each part and any proxies edges/faces used. 

[tsp62MT3AF]

Hi Alan,

I am sorry to be a pain, but I have converted everything over to where it should be in terms of the large excel file and put the parts in where they should be and it does not seem to work. I have tried to add debugging into the rule to make sure it is looking at the correct place for which it seems to but it is not replacing parts at all. This is confirmed by looking at the iproperties. Have I done something wrong?

Dim AsmDoc As AssemblyDocument = ThisDoc.Document
Dim CompDef As AssemblyComponentDefinition = AsmDoc.ComponentDefinition

Dim ExcelFileLocation As String = "**************End Suction Pump Drawings\Pumps\Pump_Layout_Spreadsheet.xlsx"

'Automatic setting of Pump Model. 
MultiValue.List("PumpModel") = GoExcel.CellValues(ExcelFileLocation, "Sheet1", "A2", "A250")

'Intial List creation to ensure list is not blank on creation.
If PumpModel = "" Then : PumpModel = MultiValue.List("PumpModel").Item(0) : End If

Dim PumpModelRow As Integer = GoExcel.FindRow(ExcelFileLocation, "Sheet1", "Pump Model", "=", PumpModel)
Logger.Debug(PumpModelRow)
For Each Occ As ComponentOccurrence In CompDef.Occurrences
	
	Dim Path As String = System.IO.Path.GetDirectoryName(Occ.Definition.Document.FullFileName)
		Logger.Debug(Path)
	
	Select Case Occ.Name
	
	Case "Volute" 
		Try
			Occ.Replace(Path & "\Volutes\" & GoExcel.CurrentRowValue("Volute"), False)
			VoluteModel = GoExcel.CurrentRowValue("Volute")
'			Logger.Debug(VoluteModel)
		Catch
		End Try
		
	Case "Bellhousing" 
		Try
			Occ.Replace(Path & "\Bellhousings\" & GoExcel.CurrentRowValue("Bellhousing"), False)
			BellhousingModel = GoExcel.CurrentRowValue("Bellhousing")
'			Logger.Debug(BellhousingModel)
		Catch
		End Try
		
	Case "Motor" 
		Try
			Occ.Replace(Path & "\Motors\" & GoExcel.CurrentRowValue("Motor"), False)
			MotorModel = GoExcel.CurrentRowValue("Motor")
'			Logger.Debug(MotorModel)
		Catch
		End Try
		
	End Select
	
Next

[A.Acheson]

The issue I would think is the replace Directory path . The path of the current file is picked up then you add in a folder. Which path is the replacement files in? Are they not all in the same folder? Check the fullfilename your supplying as it may not be logical to the file.