Ilogic - Replace source of embeded excel file

[Anonymous]

Hello everyone,

I'm interesting if is possible to replace embeded excel file if is not linked?

On forum I was find one iLogic rule, which is work perfect, but with this rule I can replace only linked excel file.

Thank you a lot for any help.

With kind regards,

Jernej Puc

Sub Main()
   
    Dim oAssyDoc As Inventor.AssemblyDocument = ThisDoc.Document

    Dim OldExcelFile As String = InputBox("What was the full name of the old linked Excel file? (Includes file path)", "Old Linked Excel file", "")
    Dim NewExcelFile As String = InputBox("What is the full name of the new linked Excel file? (Includes file path)", "New Linked Excel file", OldExcelFile)
    
    'Handle Top level Doc
    Call ReplaceRefFile(oAssyDoc, OldExcelFile, NewExcelFile)


    'Iterate through sub level docs.
    Dim oDoc As Inventor.Document = Nothing
    For Each oDoc In oAssyDoc.AllReferencedDocuments
        If oDoc.DocumentType = Inventor.DocumentTypeEnum.kAssemblyDocumentObject Or oDoc.DocumentType = Inventor.DocumentTypeEnum.kPartDocumentObject Then
             Call ReplaceRefFile(oDoc, OldExcelFile, NewExcelFile)
        End If
    Next
    
    oAssyDoc.update2(True)
    
    MsgBox("Done", MsgBoxStyle.Information, "Done")

End Sub

Sub ReplaceRefFile(oDoc As Document, OldExcelFile As String, NewExcelFile As String)
            Dim oReferencedFile As Inventor.ReferencedOLEFileDescriptor = Nothing
            If oDoc.ReferencedOLEFileDescriptors.Count > 0 Then
                If oDoc.IsModifiable = True
                  For Each oReferencedFile In oDoc.ReferencedOLEFileDescriptors
                    If oReferencedFile.FullFileName = OldExcelFile Then
                        oReferencedFile.FileDescriptor.ReplaceReference(NewExcelFile)
                        oDoc.Update2(True)
                    End If
                  Next
                 Else
                     MsgBox(oDoc.FullFileName & vbLf & "is not modifiable. References NOT replaced")
                 End If
            End If
End Sub

[Ralf_Krieg]

Hello

You can delete the existing one and add a new one. I have not tested anything, but if embedded file is used for e.g. parameters, I think they will be lost.

[Anonymous]

Hello @Ralf_Krieg,

Thank you very much for your reply. In this case maybe do you have some idea, how I can add new embedded excel file with iLogic rule?

Thank you!

BW

[Ralf_Krieg]

Hello

I have modified the script from your first post. After starting the rule, pick the old embedded file in the modelbrowser tree. After this select the new Excel file in the file dialog. After closing the dialog, the script searches through assembly and all parts and subassemblies for the old embedded file. If found, it will delete it and add the new file instead.

Can you try if it works as you expect?

Sub Main()
	
	Dim oApp As Inventor.Application = ThisApplication
    Dim oAssyDoc As Inventor.AssemblyDocument = ThisDoc.Document
	Dim bDone As Boolean =False
	Dim oObject As Object
	Dim oOldFile As Inventor.ReferencedOLEFileDescriptor 
	
	Do While Not bDone = True
	    oObject = oApp.CommandManager.Pick(SelectionFilterEnum.kAllEntitiesFilter, "Pick the old embedded file in model browser...")
	    If oObject Is Nothing Then
	        Exit Sub
	    End If
	    If Not oObject.Type = ObjectTypeEnum.kReferencedOLEFileDescriptorObject Then
	        oObject = Nothing
	    Else
			oOldFile=TryCast(oObject, Inventor.ReferencedOLEFileDescriptor )
	        bDone = True
	    End If
	Loop
		
	Dim sOldFile As String = oOldFile.DisplayName 
	Dim sNewFile As String
	
	Dim oFileBrowser As Inventor.FileDialog = Nothing
	oApp.CreateFileDialog(oFileBrowser)
	oFileBrowser.CancelError = True
	oFileBrowser.Filter="Microsoft Excel (*.xls, *.xlsx)|*.xls; *.xlsx"
	On Error Resume Next
	oFileBrowser.ShowOpen
	If Err.Number <> 0 Then
		Return
	ElseIf oFileBrowser.FileName <> "" Then
		sNewFile = oFileBrowser.FileName
	Else
		Exit Sub
	End If
	On Error GoTo 0
    
    'Handle Top level Doc
    Call ReplaceRefFile(oAssyDoc, sOldFile, sNewFile)


    'Iterate through sub level docs.
    Dim oDoc As Inventor.Document = Nothing
    For Each oDoc In oAssyDoc.AllReferencedDocuments
        If oDoc.DocumentType = Inventor.DocumentTypeEnum.kAssemblyDocumentObject Or oDoc.DocumentType = Inventor.DocumentTypeEnum.kPartDocumentObject Then
             Call ReplaceRefFile(oDoc, sOldFile, sNewFile)
        End If
    Next
    
    oAssyDoc.Update2(True)
    
    MsgBox("Done", MsgBoxStyle.Information, "Done")

End Sub

Sub ReplaceRefFile(oDoc As Document, sOldFile As String, sNewFile As String)
            Dim oReferencedFile As Inventor.ReferencedOLEFileDescriptor = Nothing
			Dim oNewReferencedFile As Inventor.ReferencedOLEFileDescriptor = Nothing
            If oDoc.ReferencedOLEFileDescriptors.Count > 0 Then
                If oDoc.IsModifiable = True
                  For Each oReferencedFile In oDoc.ReferencedOLEFileDescriptors
                    If oReferencedFile.DisplayName = sOldFile Then
                        oReferencedFile.Delete
						oNewReferencedFile = oDoc.ReferencedOLEFileDescriptors.Add(sNewFile, OLEDocumentTypeEnum.kOLEDocumentEmbeddingObject)
						oNewReferencedFile.DisplayName=System.IO.Path.GetFileNameWithoutExtension(sNewFile)
                        oDoc.Update2(True)
						Exit For
                    End If
                  Next
                 Else
                     MsgBox(oDoc.FullFileName & vbLf & "is not modifiable. References NOT replaced")
                 End If
            End If
End Sub

[Anonymous]

Hello @Ralf_Krieg,

This works very good! You made a great program. 

The problem is now, when I replacing with the new Excel file, 3rd Party is replaced, but parameters in fx are not.

[Ralf_Krieg]

Hello

Sadly there is no relationship between the embedded file and the parameter table exposed by the API. I can not "see" if these parameters are imported from this file, are now obsolet and can be deleted or something else. I think we get stuck here, sorry. 😞

[Anonymous]

Hello @Ralf_Krieg,

Thank you very much for all your help.

I understand your point. Maybe do you know if is possible to link embed excel file in Parameters  with start cell A2 with iLogic?

BR

[Ralf_Krieg · Accepted solution]

Hello

I think this should do. Keep in mind, alwaays the first worksheet of Excel file will be used, all others ignored.

	Dim sStartCell As String = "A2" 'change to your desired start cell
	Dim bLink As Boolean = False 'False=embed file, True=link file
	
	Dim oApp As Inventor.Application = ThisApplication
    Dim oAssDoc As Inventor.AssemblyDocument = ThisDoc.Document
	Dim sNewFile As String
	Dim oParams As Parameters = oAssDoc.ComponentDefinition.Parameters
    Dim oFileBrowser As Inventor.FileDialog = Nothing
	
	oApp.CreateFileDialog(oFileBrowser)
	oFileBrowser.CancelError = True
	oFileBrowser.Filter="Microsoft Excel (*.xls, *.xlsx)|*.xls; *.xlsx"
	On Error Resume Next
	oFileBrowser.ShowOpen
	If Err.Number <> 0 Then
		Return
	ElseIf oFileBrowser.FileName <> "" Then
		sNewFile = oFileBrowser.FileName
	Else
		Exit Sub
	End If
	On Error GoTo 0

    oParams.ParameterTables.AddExcelTable(sNewFile, sStartCell, bLink)

[Anonymous]

Hello @Ralf_Krieg,

Many many thanks for your help and very good solutions!

I´m very grateful to you 🙂

With kind regards,

Jernej