ilogic change source of embeded excel sheet

[Anonymous]

I have an assembly that contain an embedded Excel sheet driving subassemblies and parts that contain the same source sheet. , My question is, is it possible to make an Ilogic that will find and change the source for all subassemblies and part that s contain in the top assembly

[Anonymous]

Hi,

Try to create an iLogic rule in your assembly file, and paste following code:

    Dim oAssyDoc As Inventor.AssemblyDocument = ThisDoc.Document
    
    Dim OldExcelFile As String = "C:\...\...\ExcelFile01.xlsx"
    Dim NewExcelFile As String = "C:\...\...\ExcelFile02.xlsx"
    
    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
    
            'You should implement some checks here to see if file is readonly or not
    
            Dim oReferencedFile As Inventor.ReferencedOLEFileDescriptor = Nothing
            If oDoc.ReferencedOLEFileDescriptors.Count > 0 Then
                For Each oReferencedFile In oDoc.ReferencedOLEFileDescriptors
                    If oReferencedFile.FullFileName = OldExcelFile Then
                        oReferencedFile.FileDescriptor.ReplaceReference(NewExcelFile)
                        oDoc.Update2(True)
                    End If
                Next
            End If
        End If
    Next
    
    oAssyDoc.update2(True)
    
    MsgBox("Done", MsgBoxStyle.Information, "Done")

Make sure to enter the correct names for the old and new Excel files in the 2nd and 3rd line.

You should also add some checks to see if the file you are replacing the Excel file is actually read/write. Libraries & Vault restrictions may result in read only files.

Also make sure your Excel files are placed in your workspace !!!!

[Anonymous]

Wow!!! It's working very good, for all subassemblies and parts, but doens't change Excel source for the main ass'y

But i can do with that

Many TKS.

[MechMachineMan · Accepted solution]

Sub Main()
   
    Dim oAssyDoc As Inventor.AssemblyDocument = ThisDoc.Document
    
    Dim OldExcelFile As String = "C:\...\...\ExcelFile01.xlsx"
    Dim NewExcelFile As String = "C:\...\...\ExcelFile02.xlsx"
    
    '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

[Anonymous]

Many Thank you, this rule doing exactly what i was looking for, i added inputboxes to enter file path and working perfect !!!

[Anonymous]

With added input boxes

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

[Darrell.johnson]

Hi,

is it possible to change the source of Inventor-parts in a similar way?

[Anonymous]

Hi Mr. lcampstejin,

I found Your reply to a question in regards to changing the Excel source to recreate an assembly or iParts using iLogic,

I liked that approach but that change is with a hardcoded location.

How can I do the same but dynamically? so I don't have to go to every time and change it manually.

I have my Assembly in a Template location and I copy everything to a new Project folder and the Excel file is retaining the original location and not the new one which it should be since the parameter values will be different.

Can you suggest me something in this matter?

I have the batch process written in visual studio 2017, but if you think that this can be done at iLogic level that also can be helpful as long as the Excel file will follow the new source folder location.

Thank you for any suggestion you may have, and I am looking forward to hearing from you.

Sincerely, Sam Lazcano