Import excel data into iProperties

[Lewis.Young]

Hi all,

I'm reasonably new to using iLogic, but have been experimenting a lot recently, and i can't seem to figure out how to retrieve specific data from a spreadsheet.

What i want is a rule that will look into this spreadsheet, and then depending on what stock code the user has put in (Variable A) it will find the matching description to the right of it.

From then, it can either put it straight into the iProperties or just display it in a message box so the user can manually put it in.

Also, if the stock code doesn't exist in the spreadsheet i need a message box to come up telling the user that it cant find a description.

Any help on this will be greatly appreciated, bare in mind my knowledge on the coding is average/good so don't blow me away too much haha.

Lewis.

[AlexFielder]

Hi @Lewis.Young,

This rule of mine:

Sub Main()
	
	If TypeOf ThisApplication.activeDocument Is AssemblyDocument Or TypeOf ThisApplication.activeDocument Is PartDocument Then
		Call OpenExcel("\\PEBBLES\Drawings\Component Drawings\Post NAV Drawings\Drawing List.xlsx")	
	End If

	If TypeOf ThisApplication.activeDocument Is AssemblyDocument Then
		If disableBOMRowMerge Then
			Dim ThisBOM As BOM = ThisApplication.activeDocument.componentdefinition.BOM
			ThisBOM.SetPartNumberMergeSettings(False)
		End If
		Dim thisAssy As assemblydocument =  ThisApplication.activeDocument
		For Each subdoc as Inventor.Document In thisAssy.ReferencedDocuments
			If TypeOf subdoc Is PartDocument Then
				updatestatusbar(subdoc.FullFileName)
				For Each product As CSProduct In CSProductList
					Dim docname As String = System.io.Path.getfilenamewithoutextension(subdoc.Displayname) & ":1"
					Dim partno As String = iProperties.Value(docname, "Project", "Part Number")
					If partno.contains(product.DrawingNumber) Then
						AssigniProperties(subdoc,product)
						Exit For
					End If
				Next
			End If
		Next
	ElseIf TypeOf ThisApplication.activeDocument Is PartDocument Then
		Dim ThisPart As partdocument = ThisApplication.activeDocument
		For Each product As CSProduct In CSProductList
			Dim partno As String = iProperties.Value("Project", "Part Number")
			
			If partno.contains(product.DrawingNumber) Then
				AssigniProperties(product)
				Exit For
			End If
		Next
	End If

'___Use windows voice command____________
Dim objSPVoice,colVoices
objSPVoice = CreateObject("SAPI.SpVoice")
objSPVoice.Speak ("i Logic Rules complete")

End Sub

Public CSProductList As list(Of CSProduct)
Public MaxExcelRow As Integer = 800
Public disableBOMRowMerge As Boolean = True

Sub OpenExcel(ByVal Filename As String)

	'MessageBox.Show(Filename, "Title")
	updatestatusbar("Working with Excel, hold tight!")
	GoExcel.Open(Filename, "Components")
	
	CSProductList = New list(Of CSProduct)
	Dim percent As Double = Nothing
	For cellrow As Integer=4 To MaxExcelRow
		Dim columnLetter As String = "A"
		percent = (CDbl(cellrow) / MaxExcelRow)
		updatestatusbar(percent, "Percentage complete during get of Excel Data is:")
		If GoExcel.CellValue(columnLetter & CStr(cellrow)) = "" Then
			Continue For
		End If

		Dim product As CSProduct
		product = New CSProduct
		product.drawingnumber = GoExcel.CellValue(columnLetter & CStr(cellrow))
		columnLetter="C"
		product.description = GoExcel.CellValue(columnLetter & CStr(cellrow))
		columnLetter="E"
		product.itemcode = GoExcel.CellValue(columnLetter & CStr(cellrow))
		CSProductList.add(product)
	Next
	
	GoExcel.Close()
	updatestatusbar("list count= "& csproductlist.count)
	'MessageBox.Show("list count= "& csproductlist.count, "Title")

End Sub

Sub AssigniProperties (ByVal ThisDoc as document, ByVal product As CSProduct) 
	'MsgBox(ThisDoc.FullFileName)
	updatestatusbar(ThisDoc.FullFileName)
	Dim docname As String = System.io.Path.getfilenamewithoutextension(ThisDoc.Displayname) & ":1"
	iProperties.Value(docname, "Project", "Part Number") = product.itemCode & "-" & product.DrawingNumber
	iProperties.Value(docname, "Project", "Description") = product.Description
End Sub


Sub AssigniProperties (ByVal product As CSProduct) 
	'MsgBox(ThisDoc.FullFileName)
	updatestatusbar(ThisApplication.activeDocument.FullFileName)
	iProperties.Value("Project", "Part Number") = product.itemCode & "-" & product.DrawingNumber
	iProperties.Value("Project", "Description") = product.Description
End Sub




Sub updatestatusbar(ByVal message As String)
	ThisApplication.statusbartext = message
End Sub

Sub updatestatusbar(ByVal percent As Double, ByVal message As String)
	ThisApplication.statusbartext = message + " (" & percent.tostring("P1") + ")"
End Sub
'This is our Product Class
Class CSProduct
	
	Public DrawingNumber As String
	
	Public Description As String
	
	Public ItemCode As String
	
	Public Sub init (m_DrawingNumber As String ,
					m_description As String,
					m_itemcode As String)
					
	DrawingNumber = m_DrawingNumber
	Description = m_description
	ItemCode = m_itemCode
	End Sub
End Class

from my iLogic library here:

https://github.com/AlexFielder/iLogic

will do what you want with a bit of a tweak.

Basically, in its current form, when you run it in a part or Assembly, the rule will open an Excel spreadsheet, capture the data from it and then compare the a part number from the current part or occurrence with the list of information and populate the iProperties accordingly.

The tweak for your needs would be to prompt the user for input instead of (blindly) searching the list.

If there is no match then MessageBox.show("No match") would suffice.

[Lewis.Young]

Cheers for the reply, i honestly don't know where to start making tweaks to it though, as there's a lot to take in (maybe abit too complicated for my skill level). 

[AlexFielder]

Hi @Lewis.Young,

Unfortunately, there's no easy answer to this problem.

You're going to need to open an Excel file and store the values within in some searchable method.

The fastest way of doing so is to use a Class as I have in the rule I shared.

Yes, you can have Excel perform a search for you but recently I have found relying upon Excel (2016) to do anything more than open a spreadsheet is a lesson in futility; sometimes it opens in a couple of seconds, other times it can be many minutes before Excel does anything(!).

I digress, but the fact is this: the rule I shared will do exactly what you want, but you will need to read and understand each line of it in order to change it.

I am more than happy to guide you in doing so, so please reply to this with any questions you have.

Cheers,

Alex.

[Anonymous]

Could you expand this example to show how to import an exel date into iproperties 'checked date'. Thank you