iLogic GoExcel Loop Through Excel Rows

[insomnix]

I have an iLogic rule that cycles through an excel spreadsheet. For each row it loads a part number and some other information, then exports a file into another folder. Everything works fine, except to get the information from the excel file I'm using the GoExcel.CellValue function. Whenever a new column is inserted into the spreadsheet the iLogic rule has to be reworked. There is this nice GoExcel.CurrentRowValue function that pulls the data based on a title row name, but I can't figure out how to get it to work in a loop. 

Below is a simplified version of the loop I'm trying to create. How do I iterate through each row of a spreadsheet using the title row as a column reference to load information into properties and parameters?

For Each row In GoExcel.FindRow(xlFile, xlTab, "Export", "=", "Y")
	
	iProperties.Value("Project", "Part Number") = row.CurrentRowValue("Part Number")
	iProperties.Value("Project", "Description") = row.CurrentRowValue("Description")
	'...... other fields loaded into the ipt to create the part ......
	
	' other work here that exports ipt files to another folder to be loaded onto a machine to be cut
Next 

[insomnix]

Anyone have any ideas?

[chandra.shekar.g]

@insomnix,

Please provide non confidential files (including excel file) to investigate.

Thanks and regards,

[insomnix]

I created a new file and placed the portion of code I was asking about into the file. There are two forms that demonstrate the code used in two of the rules. "Load Single Part" uses GoExcel.CurrentRowValue, which references a column title created by the user. This is nice because it doesn't matter if a column is added in the middle of the spreadsheet, the code will still work. However, "Load Multiple Parts" cycles through row by row to gather information using GoExcel.CellValue, which requires a column/row reference. This will cause some serious problems if someone wants to add another column in the middle of the spreadsheet.

What I would like is to use GoExcel.CurrentRowValue in a loop, similar to the "load multiple" rule.

[chandra.shekar.g]

@insomnix,

Hoping that suggestions in the below link may be helpful.

https://knowledge.autodesk.com/support/inventor-products/learn-explore/caas/CloudHelp/cloudhelp/2014...

https://adndevblog.typepad.com/manufacturing/2013/12/how-to-use-goexcelfindrowstart.html

Thanks and regards,

[insomnix · Accepted solution]

Neither of those links really gave my any more information than I already had. Autodesk's documentation on GoExcel is very poor. I only found the FindColumn function by accident, no documentation on that anywhere. Using that I was able to put together something that will allow the excel file to be more dynamic. It is not exactly what I wanted, but it accomplishes what I need. I've attached the new ipt for anyone else who might need something similar and listed the code below.

Sub Main()
	If MessageBox.Show("Ready to create files?", "Create Files", MessageBoxButtons.YesNo) = vbYes Then
		' reset the variable
		part_load = ""
		
		'assign the name of the excel file if it is not assigned.
		If Trim(excel_file_name) = "" Then
			MessageBox.Show("An excel file has not been defined for this part.","File Name Does Not Exist",MessageBoxButtons.OK)
		Else
			'define the excel file to open
			xlFile = ThisDoc.Path & "\" & excel_file_name & ".xlsx"
			
			'check for existing file
			If System.IO.File.Exists(xlFile) Then
				Dim oProgBar As Inventor.ProgressBar
				Dim iStepCount As Long = 0
				Dim iStepMax As Long = 0
				Dim xlTab As String = "Sheet1"
				Dim i As Integer = 2
				Dim PartsList As New ArrayList
				
				' load the excel file
				GoExcel.Open(xlFile, xlTab)

				'find columns
				PartNumberCol = GetExcelColumnName(GoExcel.FindColumn("Part Number"))
				CreateCol = GetExcelColumnName(GoExcel.FindColumn("Create"))
				
				' get count of all files to generate and create list of parts numbers
				While GoExcel.CellValue(xlFile, xlTab, PartNumberCol & i) > ""
					If GoExcel.CellValue(CreateCol & i) = "Y" Then
						PartsList.Add(GoExcel.CellValue(PartNumberCol & i))
						iStepMax += 1
					End If
					i = i + 1
				End While

				'start progress bar
				oProgBar = ThisApplication.CreateProgressBar(False, iStepMax + 2, "Creating Files")
				oProgBar.Message = "Preparing to create files..."
				oProgBar.UpdateProgress
				
				MessageBox.Show("Doing the needed work before the fake files are created.","Pre-Work",MessageBoxButtons.OK)

				For Each pn In PartsList
					GoExcel.FindRow(xlFile, xlTab, "Part Number", "=", pn)
					
					iProperties.Value("Project", "Part Number") = GoExcel.CurrentRowValue("Part Number")
					iProperties.Value("Project", "Description") = GoExcel.CurrentRowValue("Description")
					width = GoExcel.CurrentRowValue("Width")
					length = GoExcel.CurrentRowValue("Length")
					depth = GoExcel.CurrentRowValue("Depth")

					iStepCount += 1
					oProgBar.Message = "Creating " + CStr(iStepCount) + " of " + CStr(iStepMax) + " files. " + iProperties.Value("Project", "Part Number")
					oProgBar.UpdateProgress

					RuleParametersOutput()
					iLogicVb.UpdateWhenDone = True
					
					InventorVb.DocumentUpdate()
					ThisDoc.Save
					DoWork()
				Next
				GoExcel.Close()
				'iLogicVb.Automation.RulesOnEventsEnabled = True
				
				oProgBar.Message = "Files Created."
				oProgBar.UpdateProgress
				MessageBox.Show("All files created, but not really. Just pretending.","Done",MessageBoxButtons.OK)
				oProgBar.Close
			Else
				MessageBox.Show("The excel file or drawing file does not exist in the folder. Make sure the files were created or are checked out of vault.", "File Exists")
				'iLogicVb.RunExternalRule("open working folder")
			End If
		End If
	End If
End Sub

Sub DoWork()
	' Where all the work will be done once values from the excel file are loaded into the ipt
	MessageBox.Show("Pretending to do work to create part " & iProperties.Value("Project", "Part Number") & ". Just click OK.","Do Work",MessageBoxButtons.OK)
End Sub

Function GetExcelColumnName(columnNumber As Integer) As String
    Dim dividend As Integer = columnNumber
    Dim columnName As String = String.Empty
    Dim modulo As Integer

    While dividend > 0
       modulo = (dividend - 1) Mod 26
       columnName = Convert.ToChar(65 + modulo).ToString() & columnName
       dividend = CInt((dividend - modulo) / 26)
   End While

   Return columnName
End Function

Thanks for the suggestions from those who responded. It is appreciated.

[Anonymous]

Really nice 🙂

How to change the code so it really makes the parts?

[insomnix]

That entirely depends on what you are trying to do. The code to create parts would go into the Do Work portion. You can save the part as another ipt, or open an idw that will then generate a pdf. I have done both. 

[romanazaur]

Hello. Can you help me please? I have kind a similar task to do. I have one sheet metal part with extruded marking on it - nameplate with number. Extruded marking is a Part Number of this detail. And I have xl table with numbers in first column and quantity in second column. I need a macro that

- takes the text from the first cell, assigns it as Part Number

- then updates the part

- then saves the flat pattern in dxf with name "Part Number_quantity from the second column". For example "Sfgt-64_2.dxf"

- and then he returned to the table, to the second line

- and continued like this until the empty line.

I would be very grateful for any ideas