iLogice rule to fill our iProperties from Microsof Excel file

[Anonymous]

Hello, I am very new to iLogic, but have been interested in getting to know it better.  I am trying to write a piece of code that will allow me to pull information from an excel file and insert that into the iProperties for the part specified.  the excel file is very simple and in the first row the columns read as follows: PART NUMBER, DESCRIPTION, PROJECT, TITLE

I have got the code to read from the file, but it only fills out the iProperties on the part with information listed from the first part on the list in the excel file. 

part_number = InputBox("Plese enter the part number", "Get Data")

i = GoExcel.FindRow("C:\LSE Vault\Data\iProperties_new.xlsx", "Sheet1", "PART NUMBER", "=", part_number, "DESCRIPTION", "=", part_description, "PROJECT", "=", part_project, "TITLE", "=", part_title)
part_description = GoExcel.CurrentRowValue("DESCRIPTION")
part_project = GoExcel.CurrentRowValue("PROJECT")
part_title = GoExcel.CurrentRowValue("TITLE")

iProperties.Value("Project", "Part Number") = part_number
iProperties.Value("Project", "Description") = part_description
iProperties.Value("Project", "Project") = part_project
iProperties.Value("Summary", "Title") = part_title

MessageBox.Show("All properties have been applied to " & part_number, "Success")

[bradeneuropeArthur]

could you provide the Excel file for testing?

[clutsa]

My guess is you need to not have all the extra lookup data for the row.

part_number = InputBox("Plese enter the part number", "Get Data")

i = GoExcel.FindRow("C:\LSE Vault\Data\iProperties_new.xlsx", "Sheet1", "PART NUMBER", "=", part_number)
part_description = GoExcel.CurrentRowValue("DESCRIPTION")
part_project = GoExcel.CurrentRowValue("PROJECT")
part_title = GoExcel.CurrentRowValue("TITLE")

iProperties.Value("Project", "Part Number") = part_number
iProperties.Value("Project", "Description") = part_description
iProperties.Value("Project", "Project") = part_project
iProperties.Value("Summary", "Title") = part_title

MessageBox.Show("All properties have been applied to " & part_number, "Success")

[bradeneuropeArthur]

part_number = InputBox("Plese enter the part number", "Get Data")

i = GoExcel.FindRow("C:\Vault\Projects\Forum\iProperties_new.xlsx", "Sheet1", "PART NUMBER", "=", part_number)
'i = GoExcel.FindRow("C:\Vault\Projects\Forum\iProperties_new.xlsx", "Sheet1", "PART NUMBER", "=", "0003", "DESCRIPTION", "=", part_description, "PROJECT", "=", part_project, "TITLE", "=", part_title)

part_description = GoExcel.CurrentRowValue("DESCRIPTION")

part_project = GoExcel.CurrentRowValue("PROJECT")
part_title = GoExcel.CurrentRowValue("TITLE")

iProperties.Value("Project", "Part Number") = part_number
iProperties.Value("Project", "Description") = part_description
iProperties.Value("Project", "Project") = part_project
iProperties.Value("Summary", "Title") = part_title

MessageBox.Show("All properties have been applied to " & part_number, "Success")

[Sergio.D.Suárez · Accepted solution]

I have tried it, and it has worked for me, it has a variable to test if the values of the excel table are empty, not to search in all the excel cells and only those in the table.
I hope it works for you. regards

Dim Dirxlsx As String = "C:\LSE Vault\Data\iProperties_new.xlsx" 
Dim osheetbom As String = "Sheet1"     

xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlWorkbook = xlApp.Workbooks.Open(Dirxlsx)
xlWorksheet = xlWorkbook.Worksheets.Item(osheetbom)

part_number = InputBox("Plese enter the part number", "Get Data")

Dim j As Integer = 1
 
Line1 :
oTest = xlWorksheet.Range("A" & j).Value
 
If oTest <> "" Then 
	j = j + 1
	GoTo Line1
End If

For row = 2 To j
	If xlWorksheet.Range("A" & Row).Value = part_number Then
		iProperties.Value("Project", "Part Number") = xlWorksheet.Range("A" & Row).Value
		iProperties.Value("Project", "Description") = xlWorksheet.Range("B" & Row).Value
		iProperties.Value("Project", "Project") = xlWorksheet.Range("C" & Row).Value
		iProperties.Value("Summary", "Title") = xlWorksheet.Range("D" & Row).Value
		MessageBox.Show("All properties have been applied", "Success")

	End If
Next

xlWorkbook.Close (True)
xlApp.Quit

[bradeneuropeArthur]

@clutsa 

That was close!

[Anonymous]

Thank you! That works great! My next step is to figure out how to apply a rule like this to an assembly drawing and import properties to each part if the properties have yet to be established.  Do you have any advice on what I might look into to point me in the right direction?

[clutsa]

@bradeneuropeArthur Right! I feel robbed! I dropped a "Dim part_number as string" and ran this on an part file with the spreadsheet embedded and it works fine. If I hadn't had a dentist appointment I could have tested it before I posted. Some days man.

Dim part_number As String
part_number = InputBox("Plese enter the part number", "Get Data")

i = GoExcel.FindRow("iProperties_new.xlsx", "Sheet1", "PART NUMBER", "=", part_number)
part_description = GoExcel.CurrentRowValue("DESCRIPTION")
part_project = GoExcel.CurrentRowValue("PROJECT")
part_title = GoExcel.CurrentRowValue("TITLE")

iProperties.Value("Project", "Part Number") = part_number
iProperties.Value("Project", "Description") = part_description
iProperties.Value("Project", "Project") = part_project
iProperties.Value("Summary", "Title") = part_title

MessageBox.Show("All properties have been applied to " & part_number, "Success")