07-08-2021
05:01 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
07-08-2021
05:01 PM
Hi I have this working on my pc but when I get one of the other people in the office to run it, it troughs up and error and I can't work out why. Can you please point me in the right direction?
System.InvalidCastException: Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Element not found. (Exception from HRESULT: 0x8002802B (TYPE_E_ELEMENTNOTFOUND)).
at System.StubHelpers.StubHelpers.GetCOMIPFromRCW(Object objSrc, IntPtr pCPCMD, IntPtr& ppTarget, Boolean& pfNeedsRelease)
at Microsoft.Office.Interop.Excel.ApplicationClass.set_DisplayAlerts(Boolean RHS)
at ThisRule.Main()
at Autodesk.iLogic.Exec.AppDomExec.ExecRuleInAssembly(Assembly assem)
at iLogic.RuleEvalContainer.ExecRuleEval(String execRule)
at System.StubHelpers.StubHelpers.GetCOMIPFromRCW(Object objSrc, IntPtr pCPCMD, IntPtr& ppTarget, Boolean& pfNeedsRelease)
at Microsoft.Office.Interop.Excel.ApplicationClass.set_DisplayAlerts(Boolean RHS)
at ThisRule.Main()
at Autodesk.iLogic.Exec.AppDomExec.ExecRuleInAssembly(Assembly assem)
at iLogic.RuleEvalContainer.ExecRuleEval(String execRule)
It fails at this point.
'Start a new instance of the Excel application
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
oExcelApp = New Microsoft.Office.Interop.Excel.ApplicationClass
'Fails here
oExcelApp.DisplayAlerts = False
oExcelApp.Visible = False
I also modified it to show a message box that explains how the excel doc needs to be set out, use a specific template, use part number as file name and ground the parts.
'Source "https://forums.autodesk.com/t5/inventor-customization/ilogic-create-empty-parts-and-fill-their-iproperties-from-excel/td-p/9850027"
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel
Sub Main
MessageBox.Show("Excel sheet needs to have the following columns in row 1 in order to populate the properites" & vbCrLf & _
"'Qty' (by ea units only not length), 'Description','Part Number', 'Vendor', 'Stock Number'" & vbCrLf & vbCrLf & _
"Assembly needs to be saved", "Rule Requirements")
'Specify the Excel file's (Workbook) full path & file name (with extension)
'It is calling a Windows OpenFileDialog to allow the user to do this
Dim oFileName As String = GetExcelFile()
'Specify the Sheet name within the workbook
'perhaps this name could be selected from a list of all available sheets
'in the Excel Workbook once opened, of you don't want to manually specify it here?
Dim oSheetName As String = "Sheet1"
'Start a new instance of the Excel application
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
oExcelApp = New Microsoft.Office.Interop.Excel.ApplicationClass
oExcelApp.DisplayAlerts = False
oExcelApp.Visible = False
'[ Attempt to open the specified Workbook (file) using the supplied file name
Dim oWB As Workbook
Try
oWB = oExcelApp.Workbooks.Open(oFileName)
Catch oEx As Exception
MsgBox("The attempt to open the Excel file named '" & oFileName & "' failed." & vbCrLf & _
"The error message for this failure is as follows:" & vbCrLf & _
oEx.Message & vbCrLf & vbCrLf & _
"Its 'StackTrace is as follows:" & vbCrLf & _
oEx.StackTrace & vbCrLf & vbCrLf & _
"Its source is as follows:" & vbCrLf & _
oEx.Source, vbOKOnly + vbCritical, "Couldn't Open File")
Exit Sub
End Try
'Attempt to get the Worksheet
Dim oWS As Worksheet
Try
oWS = oWB.Sheets.Item(oSheetName)
Catch
oWS = oWB.ActiveSheet
Catch
oWS = oWB.Worksheets.Item(1)
Catch
MsgBox("'Sheet 1' Worksheet was not found in the specified Excel file. Exiting.", vbOKOnly + vbCritical, " ")
Exit Sub
End Try
Dim oCells As Range = oWS.Cells
'Define the active Assembly and all related variables
If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
MsgBox("This rule '" & iLogicVb.RuleName & "' only works for Assembly Documents.",vbOKOnly, "WRONG DOCUMENT TYPE")
Exit Sub
End If
Dim oADoc As AssemblyDocument = ThisAssembly.Document
Dim oDir As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName)
Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
Dim oOccs As ComponentOccurrences = oADef.Occurrences
Dim oOcc As ComponentOccurrence
Dim oPDoc As PartDocument
Dim oDProps As Inventor.PropertySet 'Design Tracking Properties (Project)
Dim oSProps As Inventor.PropertySet 'Inventor Summary Information (Summary)
Dim oMatrix As Inventor.Matrix = ThisApplication.TransientGeometry.CreateMatrix
'Find the last row and last column being used to limit our loop ranges
Dim oLastRow As Integer = oWS.UsedRange.Rows.Count
'Dim oLastCol As Integer = oWS.UsedRange.Columns.Count
'Dim oColumnHeadersRow As Integer = 1
Dim o1stDataRow As Integer = 2
Dim oRow, oQty As Integer
Dim oPName, oPNum, oVendor, oStock As String
For oRow = o1stDataRow To oLastRow
'First number is Row index, second number is Column Index
oQty = oCells.Item(oRow, 1).Value
oPName = oCells.Item(oRow, 2).Value
oPNum = oCells.Item(oRow, 3).Value
oVendor = oCells.Item(oRow, 4).Value
oStock = oCells.Item(oRow, 5).Value
'Define part template
oTemplate=ThisApplication.DesignProjectManager.ActiveDesignProject.TemplatesPath & "\Emtpy part for BOM.ipt"
'Create the new Part
oPDoc = ThisApplication.Documents.Add(DocumentTypeEnum.kPartDocumentObject,oTemplate , False)
'Set the iProperties within the new Part
oDProps = oPDoc.PropertySets.Item("Design Tracking Properties")
oSProps = oPDoc.PropertySets.Item("Inventor Summary Information")
oDProps.Item("Description").Value = oPName
oDProps.Item("Part Number").Value = oPNum
oDProps.Item("Vendor").Value = oVendor
oDProps.Item("Stock Number").Value = oStock
'<<<< !!! CHECK IF THIS IS OK BEFORE RUNNING !!! >>>>
'Saving each new Part to the same directory as the Assembly
'if this is not OK, you could specify a different directory
'or you could use a SaveFileDialog for this
'or just skip saving the parts at this stage altogether
oPDoc.SaveAs(oDir & "\" & oPNum & ".ipt", False)
'Add that many of this part to the assembly
For i = 1 To oQty
' oOccs.AddByComponentDefinition(oPDoc.ComponentDefinition, oMatrix)
oOcc = oOccs.AddByComponentDefinition(oPDoc.ComponentDefinition, oMatrix)
oOcc.Grounded = True
Next
Next
'Close the Workbook (file)
oWB.Close
'Close this instance of the Excel application
oExcelApp.Quit
End Sub
Function GetExcelFile() As String
Dim oFileName As String
Dim oOpenDlg As New System.Windows.Forms.OpenFileDialog
oOpenDlg.Title = "Browse To And Select Your Excel File."
oOpenDlg.InitialDirectory = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath
oOpenDlg.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx"
oOpenDlg.Multiselect = False
oOpenDlg.RestoreDirectory = False
Dim oResult = oOpenDlg.ShowDialog
If oResult = vbOK Then
If oOpenDlg.FileName <> vbNullString Then
oFileName = oOpenDlg.FileName
Else
MsgBox("No file was selected. Exiting.", vbOKOnly + vbExclamation, "FILE NOT SELECTED")
End If
ElseIf oResult = vbCancel Then
MsgBox("The dialog was Canceled. Exiting.", vbOKOnly + vbInformation, "CANCELED")
End If
GetExcelFile = oFileName
End Function