Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.

read from Excel, paste to iprop, but don't overwrite if the same

warrentdo
Collaborator

read from Excel, paste to iprop, but don't overwrite if the same

warrentdo
Collaborator
Collaborator

I Have cobbled some code together that reads from an Excel spreadsheet and populates the contents as Iproperties in inventor. the spreadsheet has many rows that start off with a tag number. from the tag the other properties are found and written to Inventor.

Everything is working fine but I would like the code to check it the vault is the same then don't over ride.

I don't want the code dirtying all the files that don't need updating.

 

Dim odoc = ThisDoc.Document
Dim oXLfile As String =  ThisDoc.Path & "\Data.xlsx"
Dim oSheet As String = "Export"
Dim oPartNumber As String = iProperties.Value("Project", "Part Number")

GoExcel.Open(oXLfile, oSheet)

GoExcel.FindRowStart = 2
	
i = GoExcel.FindRow(oXLfile, oSheet, "Tag Name", "=", oPartNumber)

o3DObjectMaturity = GoExcel.CurrentRowValue("3D Object Maturity")
oActivityCode = GoExcel.CurrentRowValue("Activity Code")
oAreaCode = GoExcel.CurrentRowValue("Area Code")
oDryWeight = GoExcel.CurrentRowValue("Dry Weight")
oEngineeringObjectMaturity = GoExcel.CurrentRowValue("Engineering Object Maturity")

If i <= 0 Then
	MessageBox.Show("Item is not in spreadsheet", "Warning")

Else
iProperties.Value("custom", "3D Object Maturity") = o3DObjectMaturity
iProperties.Value("custom", "Activity Code") = oActivityCode
iProperties.Value("custom", "Area Code") = oAreaCode
iProperties.Value("custom", "Dry Weight") = oDryWeight
iProperties.Value("custom", "Engineering Object Maturity") = oEngineeringObjectMaturity


MessageBox.Show("MetaData from Engineering data has been updated", "Update Notice")

End If

GoExcel.Close

'iLogicVb.UpdateWhenDone = True

'ThisDoc.Save

 

Regards,

 

Warren.

0 Likes
Reply
303 Views
3 Replies
Replies (3)

WCrihfield
Mentor
Mentor

Hi @warrentdo.  I'm sure there are several ways this could potentially be handled, but here is one possibility.  It uses a custom routine and Inventor API code techniques instead of the 'iProperties.Value()' iLogic shortcut snippet, to give us more control.   That function will only make a change if the specified custom iProperty can not be found, because it will then attempt to create that custom iProperty.  Or if that custom iProperty is found, but its value does not already match the supplied value.  That function will return False if no changes were made to the document during that call, and will return True if it either created the custom iProperty, or changed its value.

Sub Main
	Dim odoc = ThisDoc.Document
	Dim oXLfile As String =  ThisDoc.Path & "\Data.xlsx"
	Dim oSheet As String = "Export"
	Dim oPartNumber As String = iProperties.Value("Project", "Part Number")
	GoExcel.Open(oXLfile, oSheet)
	GoExcel.FindRowStart = 2
	i = GoExcel.FindRow(oXLfile, oSheet, "Tag Name", "=", oPartNumber)
	o3DObjectMaturity = GoExcel.CurrentRowValue("3D Object Maturity")
	oActivityCode = GoExcel.CurrentRowValue("Activity Code")
	oAreaCode = GoExcel.CurrentRowValue("Area Code")
	oDryWeight = GoExcel.CurrentRowValue("Dry Weight")
	oEngineeringObjectMaturity = GoExcel.CurrentRowValue("Engineering Object Maturity")
	If i <= 0 Then
		MessageBox.Show("Item is not in spreadsheet", "Warning")
	Else
		Dim bChange1, bChange2, bChange3, bChange4, bChange5 As Boolean
		bChange1 = TryCreateUpdateCustomProperty(odoc, "3D Object Maturity", o3DObjectMaturity)
		bChange2 = TryCreateUpdateCustomProperty(odoc, "Activity Code", oActivityCode)
		bChange3 = TryCreateUpdateCustomProperty(odoc, "Area Code", oAreaCode)
		bChange4 = TryCreateUpdateCustomProperty(odoc, "Dry Weight", oDryWeight)
		bChange5 = TryCreateUpdateCustomProperty(odoc, "Engineering Object Maturity", oEngineeringObjectMaturity)
		MessageBox.Show("MetaData from Engineering data has been updated", "Update Notice")
	End If
	GoExcel.Close
	'If odoc.PropertySets.Item(4).Dirty Then 'changes were made to the custom property set
	If odoc.Dirty Then odoc.Save2(False)
	'ThisDoc.Save
End Sub

Function TryCreateUpdateCustomProperty(oDoc As Document, sPropName As String, oValue As Object) As Boolean
	If (oDoc Is Nothing OrElse oDoc.IsModifiable = False) Or sPropName = "" Then Return False
	Dim oCProps As Inventor.PropertySet = oDoc.PropertySets.Item(4)
	Dim oCProp1 As Inventor.Property = Nothing
	Try 'try to find the existing custom iProperty object
		oCProp1 = oCProps.Item("3D Object Maturity")
	Catch 'that failed, so now create that custom iProperty
		oCProp1 = oCProps.Add(o3DObjectMaturity, "3D Object Maturity")
		Return True
	End Try
	If oCProp1 IsNot Nothing AndAlso oCProp1.Value <> o3DObjectMaturity Then
		Try : oCProp1.Value = o3DObjectMaturity : Return True : Catch : End Try
	End If
	Return False
End Function

If this solved your problem, or answered your question, please click ACCEPT SOLUTION .
Or, if this helped you, please click (LIKE or KUDOS) 👍.

 

Wesley Crihfield

EESignature

(Not an Autodesk Employee)

Frederick_Law
Mentor
Mentor

Read excel value

Read iProp value

Compare iProp to excel

Write only when they're different

 

My code to check and update Mass and Surface area:

Sub Main()
Dim NewMass As Double
Dim OldMass As Double
NewMass = Round(iProperties.Mass, 2) 'Get current Mass, 2 decimal
Dim NewArea As Double
Dim OldArea As Double
NewArea = Round(iProperties.Area, 2) 'Get current Area, 2 decimal
Logger.Info("Mass Running: " & ThisDoc.FileName)
Logger.Info("File Modifiable: " & ThisDoc.Document.IsModifiable)
If ThisDoc.Document.IsModifiable 
	Try
		OldMass = iProperties.Value("Custom", "Mass") 'Get Old Mass
	Catch
		Logger.Info("Mass Property Missing " & NewMass)
		iProperties.Value("Custom", "Mass") = NewMass
	End Try
	If OldMass <> NewMass Then 'Update if Mass different
		Logger.Info("Mass Different: Old:" & OldMass)
		Logger.Info("New: " & NewMass)
		iProperties.Value("Custom", "Mass") = NewMass
	End If

	If ThisDoc.Document.DocumentType=kPartDocumentObject Then
		Try
			OldArea = iProperties.Value("Custom", "SurfaceArea") 'Get Old SurfaceArea
		Catch
			Logger.Info("SurfaceArea Property Missing " & NewArea)
			iProperties.Value("Custom", "SurfaceArea") = NewArea
		End Try
		If OldArea <> NewArea Then 'Update if SurfaceArea different
			Logger.Info("SurfaceArea Different: Old:" & OldArea)
			Logger.Info("New: " & NewArea)
			iProperties.Value("Custom", "SurfaceArea") = NewArea
		End If
	End If

Else
	Logger.Info("File Readonly")
End If
End Sub

 

Curtis_Waguespack
Consultant
Consultant

Hi @warrentdo 

 

Here's another example.

 

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

 

GoExcel.FindRowStart = 2
i = GoExcel.FindRow(ThisDoc.Path & "\Data.xlsx", "Export", "Tag Name", "=", iProperties.Value("Project", "Part Number"))
If i <= 0 Then : MessageBox.Show("Item is not in spreadsheet", "Warning") : Exit Sub : End If

Dim oStrings = New String() {"3D Object Maturity", "Activity Code", "Area Code", "Dry Weight", "Engineering Object Maturity" }

oSmudged = False
For Each oString In oStrings
	oExcelValue = GoExcel.CurrentRowValue(oString)
	Try 'try to get iprop value
		If Not iProperties.Value("custom", oString) = oExcelValue Then
			iProperties.Value("custom", oString) = oExcelValue
			oSmudged = True
		End If
	Catch 'create it if not found
		iProperties.Value("custom", oString) = oExcelValue
		oSmudged = True
	End Try

Next
GoExcel.Close
MessageBox.Show("MetaData from Engineering data has been updated =  " & oSmudged, "Update Notice")