A few questions regarding parameters and embedded Excel spreadsheets

[Anonymous]

Hi all,

I started reading VBA and Inventor API tutorials a while ago, and I'd appreciate help with a few challenges I've come across.

1. I have a skeletal part with user parameters and also parameters that have been created in an embedded Excel spreadsheet. I am able to read and change the expression that defines the equation (parameters table term) for a user parameter using a simple macro I wrote. However, I am able to read but not able to change the value of any of the embedded spreadsheet parameters. Why would this be?

2. I found the macro below in this discussion group. It reads and changes the value of a cell in an external Excel file. This is related to my first question - how would I write a new value in a cell in a spreadsheet that has been embedded via the parameters table? I do not necessarily need to modify the value of the parameter-defining cells in the spreadsheet specifically, but any cell in the spreadsheet.

Public Sub XLtest()

Dim oXL As Object
Dim oXLWorkBook As Object
Dim oXLWorkSheet As Object
Set oXL = CreateObject("Excel.Application")
Set oXLWorkBook = oXL.Workbooks.Open("C:\Test.xls")
Set oXLWorkSheet = oXLWorkBook.ActiveSheet
MsgBox "The Value in Cell A1 is " & vbCr & oXLWorkSheet.Cells(1, 1).Value
oXLWorkSheet.Cells(1, 1).Value = "Caramba!"
MsgBox "We just changed the value in Cell A1 to " & vbCr & oXLWorkSheet.Cells(1, 1).Value
oXLWorkBook.Close SaveChanges:=True
Set oXLWorkBook = Nothing
oXL.Quit
Set oXL = Nothing
End Sub

3. I have another skeletal part with a large number of parameters on the first sheet of the spreadsheet. The objective is to be able to capture and save configurations of these parameters in order to be able to restore them quickly. One configuration would correspond with a particular size of an equipment with set length, width etc. One idea I have is to copy the parameters specific to a configuration onto a new sheet. To activate a configuration in the skeletal model, I would copy the parameters back to the first sheet, which are then read into Inventor to create the model corresponding with that configuration. I have written a macro in Excel that takes care of copying the cells from one sheet to another.

How do I execute this macro that resides in Excel from Inventor? Or, can I bring the code to Inventor and run the macro from there? If so, how?

4. Is it possible to enable/disable defer updates of a drawing via API?

Input to any of my questions is much appreciated!
Samu

[Anonymous]

1. The API behavior is the same as that of the user
interface - values of parameters created via an Excel link cannot be edited
within Inventor.

2. See sample code below to edit values in excel
sheet associated with parameter tables.

4. Use DrawingDocument.DrawingSettings.DeferUpdates
property to set the defer updates option in drawings.

Sanjay-

Make sure to add a reference to the Excel type
library in VBA (go to Tools | References):

Sub ChangeExcelValue()
   

    Dim oDoc As PartDocument
    Set oDoc =
ThisApplication.ActiveDocument
   
    Dim
oDef As PartComponentDefinition
    Set oDef =
oDoc.ComponentDefinition
   
    Dim
oParamTable As ParameterTable
    Set oParamTable =
oDef.Parameters.ParameterTables.Item(1)
   

    Dim oExcelSheet As WorkSheet
    Set
oExcelSheet = oParamTable.WorkSheet
   
   
oExcelSheet.Cells(1, 2) = "6 in"
       

    oDoc.Update
   
   
Dim oWB As Workbook
    Set oWB =
oExcelSheet.Parent
       

    oWB.Save
   
oWB.Close
   
End Sub

[Anonymous]

Sanjay,

Your example code does exactly what I was looking for. Thanks so much for a clear and concise answer!

I have one additional question: By default, Inventor always reads the parameters of an embedded spreadsheet from the first sheet of Excel. Is it possible to change it to instead read from the 2nd, 3rd, or any other sheet indicated by its name via API?

Thanks!!!
Samu

[Anonymous]

Inventor always references the first excel sheet.
There isn't a way to make it point to some other sheet. The best I can think of
is to reorder the sheets in Excel so that the desired sheet is the first one.
For instance, to reorder the 2nd sheet to the 1st position, you would something
along the following lines using Excel APIs:

Sheets(2).Move Before:=Sheets(1)

Save and close the parent workbook to ensure
Inventor absorbs the changes.

Sanjay-