Extracting cell Data from Excel

[Nigel.Sims]

Hi all,

I have been trying to add an iLogic rule to populate a custom iProperty using an external excel spreadsheet. I have got it working once, but if i save the assembly as something else the custom iProperty doesn't update. Ilogic compares the Project iProperty and then  finds this value in the excel file and returns another value in the corresponding column. I have attached the code below.

SyntaxEditor Code Snippet

'Populate iProperties from filename
iProperties.Value("Summary", "Title") = (Mid(ThisDoc.FileName(False),11, 100))
iProperties.Value("Project", "Project") = (Left(ThisDoc.FileName(False), 4))
iProperties.Value("Custom", "SYSTEM") = (Mid(ThisDoc.FileName(False), 6,4))

'Project number variable
ProjectNo = iProperties.Value("Project", "Project")

'Spreadsheet rows start on the 3rd row
GoExcel.TitleRow = 3
'compare the project number variable against work order number column in spreadsheet.
i = GoExcel.FindRow("C:\Vault Work\Design Projects\Title Block Info.xlsx", "Sheet1", "Work Order Number", "=", "ProjectNo")
iProperties.Value("Custom", "Client") = GoExcel.CurrentRowValue("Client")

iLogicVb.UpdateWhenDone = True
InventorVb.DocumentUpdate()

 Many thanks

Nigel

[MechMachineMan · Accepted solution]

I removed the quotation marks from around ProjectNo as it's used in the FindRow line so that it uses the value, and not the 'dumb' string "ProjectNo"

'Populate iProperties from filename
iProperties.Value("Summary", "Title") = (Mid(ThisDoc.FileName(False),11, 100))
iProperties.Value("Project", "Project") = (Left(ThisDoc.FileName(False), 4))
iProperties.Value("Custom", "SYSTEM") = (Mid(ThisDoc.FileName(False), 6,4))

'Project number variable
ProjectNo = iProperties.Value("Project", "Project")

'Spreadsheet rows start on the 3rd row
GoExcel.TitleRow = 3
'compare the project number variable against work order number column in spreadsheet.
i = GoExcel.FindRow("C:\Vault Work\Design Projects\Title Block Info.xlsx", "Sheet1", "Work Order Number", "=", ProjectNo)
iProperties.Value("Custom", "Client") = GoExcel.CurrentRowValue("Client")

iLogicVb.UpdateWhenDone = True
InventorVb.DocumentUpdate()

[Nigel.Sims]

@MechMachineMan That works brilliantly, thank you. 

[Nigel.Sims]

Although the code works great when the I run the rule manually, getting the rule to trigger when the document is saved is another issue. On the first save of the document I can get the rule to work and populate the iProperties fine, but if I save as another document with a different name the rule doesn't run. I then have to run the rule manually to get it to work. 

Any ideas are highly appreciated.

Nigel

[MechMachineMan]

A simple test to do is to put a rule in that reads the file name and apply that on save - much like your rule - to see when the rule actually runs.

After doing that, it's apparent that the SaveAs will not actually trigger the rule on the newly saved document (regardless of whether you use the beforesave or aftersave triggers). I'm assuming this is because it saves the document first (without running the rule) then opens it second to make it visible, so it's never actually saved within the context of the iLogic rule.

I can't think of any workarounds that don't involve more advanced programming, but the fact that it will run when you save it anyways doesn't make it seem like it's that big of an issue.

Perhaps @chandra.shekar.g can comment on whether this is intended behavior or not.