Another option. It works like this: it extracts data from the active document (part or assembly), creates a temporary Excel file, moves the table to be edited there, and opens it in the foreground. After you modify and save the data in Excel and then close the application, the code imports the updated data back into Inventor and deletes the temporary file.
Imports System.Runtime.InteropServices
Sub Main()
' Get the active document
Dim oDoc As Document = ThisApplication.ActiveDocument
Dim oCompDef As ComponentDefinition = Nothing
Dim oModelStates As ModelStates = Nothing
' Determine the document type and get ComponentDefinition
If TypeOf oDoc Is AssemblyDocument Then
oCompDef = DirectCast(oDoc, AssemblyDocument).ComponentDefinition
ElseIf TypeOf oDoc Is PartDocument Then
oCompDef = DirectCast(oDoc, PartDocument).ComponentDefinition
Else
MessageBox.Show("The current document does not support ModelStates", "Error")
Return
End If
' Get ModelStates
oModelStates = oCompDef.ModelStates
If oModelStates Is Nothing Then
MessageBox.Show("Failed to retrieve ModelStates", "Error")
Return
End If
Try
' Call the method to work with Excel, running in a separate scope
ProcessModelStatesWrapper(oModelStates)
Catch ex As Exception
MessageBox.Show("Error while working with the ModelStates table: " & ex.Message, "Error")
End Try
End Sub
' Declaration of the SetForegroundWindow function from user32.dll
<DllImport("user32.dll")> _
Private Shared Function SetForegroundWindow(ByVal hWnd As IntPtr) As Boolean
End Function
' Wrapper that isolates Excel operations and ensures garbage collection
Sub ProcessModelStatesWrapper(ByVal oModelStates As ModelStates)
' Call the method to work with Excel
ProcessModelStates(oModelStates)
' Perform garbage collection twice to ensure cleanup
ForcedGarbageCollection()
End Sub
Sub ProcessModelStates(ByVal oModelStates As ModelStates)
' Create a temporary copy of the ModelStates table for editing
Dim tempFolder As String = System.IO.Path.GetTempPath()
Dim tempFileName As String = "TempModelStates_" & Guid.NewGuid().ToString() & ".xlsx"
Dim tempFilePath As String = System.IO.Path.Combine(tempFolder, tempFileName)
' Declare COM object variables
Dim objExcel As Object = Nothing
Dim objWorkbooks As Object = Nothing
Dim objWorkbook As Object = Nothing
Dim objWorksheet As Object = Nothing
Dim objRange As Object = Nothing
Dim modelStateTable As ModelStateTable = Nothing
Dim tableRows As ModelStateTableRows = Nothing
Dim tableColumns As ModelStateTableColumns = Nothing
Try
' Create a new Excel file
objExcel = CreateObject("Excel.Application")
objWorkbooks = objExcel.Workbooks
objWorkbook = objWorkbooks.Add()
objWorksheet = objWorkbook.Sheets(1)
' Get ModelStateTable
modelStateTable = oModelStates.ModelStateTable
If modelStateTable Is Nothing Then
MessageBox.Show("Failed to retrieve ModelStateTable", "Error")
' Release COM objects
CleanupExcelObjects(objWorksheet, Nothing, objWorkbook, objWorkbooks, objExcel)
Return
End If
' Get all rows and columns
tableRows = modelStateTable.TableRows
tableColumns = modelStateTable.TableColumns
' Fill column headers
For i As Integer = 1 To tableColumns.Count
Dim column As ModelStateTableColumn = tableColumns.Item(i)
objWorksheet.Cells(1, i).Value = column.Heading
' Bold the headers
objWorksheet.Cells(1, i).Font.Bold = True
Next
' Fill row data
For i As Integer = 1 To tableRows.Count
Dim row As ModelStateTableRow = tableRows.Item(i)
' Iterate through each column to retrieve cell data
For j As Integer = 1 To tableColumns.Count
Try
' Get the cell from the ModelState table
Dim cell As ModelStateTableCell = row.Item(j)
If cell IsNot Nothing AndAlso cell.IsValid Then
objWorksheet.Cells(i + 1, j).Value = cell.Value
End If
Catch ex As Exception
' Skip cells with errors
End Try
Next
Next
' Get the used range of cells
objRange = objWorksheet.UsedRange
objRange.Columns.AutoFit()
NAR(objRange)
' Save the temporary file
objWorkbook.SaveAs(tempFilePath)
' Show Excel and bring it to the foreground
objExcel.Visible = True
' Get the Excel window handle and convert it to IntPtr
Dim hWnd As Integer = objExcel.GetType().InvokeMember("HWnd", System.Reflection.BindingFlags.GetProperty, Nothing, objExcel, Nothing)
SetForegroundWindow(New IntPtr(hWnd))
' Enable Excel alerts
objExcel.DisplayAlerts = True
' Wait until the user closes Excel (closes all workbooks)
While objExcel.Workbooks.Count > 0
System.Threading.Thread.Sleep(1000) ' Small delay to avoid overloading the CPU
End While
' Close Excel after editing
Try
' Use a constant to display the save prompt (xlPromptToSaveChanges = -1)
objWorkbook.Close(-1)
Catch
' The workbook might already be closed by the user
End Try
objExcel.Quit()
' Release all Excel COM objects
CleanupExcelObjects(objWorksheet, objRange, objWorkbook, objWorkbooks, objExcel)
' Excel is closed, import the data back
If System.IO.File.Exists(tempFilePath) Then
ImportDataFromExcel(tempFilePath, modelStateTable, tableRows, tableColumns)
MessageBox.Show("Data successfully imported into Inventor", "Completed")
End If
Finally
' Ensure all COM objects are released in case of an exception
Try
' If Excel is still open, close it
If objExcel IsNot Nothing Then
objExcel.DisplayAlerts = True
If objWorkbook IsNot Nothing Then
Try
objWorkbook.Close(-1)
Catch
' Ignore errors during closure
End Try
End If
objExcel.Quit()
End If
' Release COM objects
CleanupExcelObjects(objWorksheet, objRange, objWorkbook, objWorkbooks, objExcel)
Catch
' Ignore errors in the finally block
End Try
' Delete the temporary file
Try
If System.IO.File.Exists(tempFilePath) Then
System.IO.File.Delete(tempFilePath)
End If
Catch
' Ignore errors during deletion
End Try
End Try
End Sub
Sub ImportDataFromExcel(ByVal filePath As String, ByVal modelStateTable As ModelStateTable, ByVal tableRows As ModelStateTableRows, ByVal tableColumns As ModelStateTableColumns)
' Declare COM objects
Dim importExcel As Object = Nothing
Dim importWorkbooks As Object = Nothing
Dim importWorkbook As Object = Nothing
Dim importWorksheet As Object = Nothing
Dim usedRange As Object = Nothing
Try
' Open the file to read data
importExcel = CreateObject("Excel.Application")
importExcel.Visible = False
importExcel.DisplayAlerts = False
importWorkbooks = importExcel.Workbooks
importWorkbook = importWorkbooks.Open(filePath)
importWorksheet = importWorkbook.Sheets(1)
' Get the used range of cells
usedRange = importWorksheet.UsedRange
' Update data in ModelStateTable
For i As Integer = 1 To tableRows.Count
Dim row As ModelStateTableRow = tableRows.Item(i)
For j As Integer = 1 To tableColumns.Count
Try
' Ensure we do not exceed the data range
If i + 1 <= usedRange.Rows.Count And j <= usedRange.Columns.Count Then
Dim newValue As String = Convert.ToString(importWorksheet.Cells(i + 1, j).Value)
Dim cell As ModelStateTableCell = row.Item(j)
' Update only if the cell is editable
If cell IsNot Nothing AndAlso cell.IsValid Then
cell.Value = newValue
End If
End If
Catch ex As Exception
' Skip problematic cells
End Try
Next
Next
Finally
' Close and release all COM objects in reverse order of creation
If importWorkbook IsNot Nothing Then
Try
importWorkbook.Close(False)
Catch
' Ignore errors during closure
End Try
End If
If importExcel IsNot Nothing Then
Try
importExcel.Quit()
Catch
' Ignore errors during closure
End Try
End If
' Release all COM objects
CleanupExcelObjects(importWorksheet, usedRange, importWorkbook, importWorkbooks, importExcel)
End Try
End Sub
' Method to release all Excel COM objects in the correct order
Sub CleanupExcelObjects(ByVal worksheet As Object, ByVal range As Object, ByVal workbook As Object, ByVal workbooks As Object, ByVal excelApp As Object)
' Release objects in reverse order of creation
If range IsNot Nothing Then NAR(range)
If worksheet IsNot Nothing Then NAR(worksheet)
If workbook IsNot Nothing Then NAR(workbook)
If workbooks IsNot Nothing Then NAR(workbooks)
If excelApp IsNot Nothing Then NAR(excelApp)
End Sub
' Method to release a COM object with a loop until fully released
Sub NAR(ByVal obj As Object)
Try
' Call ReleaseComObject in a loop until all references are released
While System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) > 0
End While
Catch
' Ignore errors
Finally
obj = Nothing
End Try
End Sub
' Method for forced garbage collection
Sub ForcedGarbageCollection()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
End Sub
INV 2025.3
