close workbook after "edit via spreadsheet"

[Anonymous]

I have a macro that will send a command to inventor to edit the iTable 'via spreadsheet'. Similarly as if you right clicked on the iTable and chose "edit via spreadsheet" from the menu. Here's the code

Sub Edit_Via_Spreadsheet()

Dim oDoc As Document
Set oDoc = ThisApplication.ActiveDocument

Dim oDocName As String
oDocName = oDoc.DisplayName

'Opens table via spreadsheet:
' Get the CommandManager object.
Dim oCommandMgr As CommandManager
Set oCommandMgr = ThisApplication.CommandManager

' Get control definition for the line command.
Dim oControlDef As ControlDefinition
'open iTable spreadsheet in MSExcel:
Set oControlDef = oCommandMgr.ControlDefinitions.Item( _
"Applaunchviaspreadsheetcmd")
Call oControlDef.Execute
DoEvents

Dim oExlDoc As Workbook
Set oExlDoc = Excel.Workbooks("Worksheet in " & oDocName & ".xls")
oExlDoc.Close savechanges:=False

End Sub

I want to be able to automatically close the workbook that opens. I get the whole way down to setting 'oExlDoc as the workbook and get a "subscript out of range" error. Anyone see what i'm doing wrong here?

thanks

[Anonymous]

What exactly do you want to do? Edit the
spreadsheet programmatically? Or just display the spreadsheet to the user for
editing?

Sanjay-

[Anonymous]

i just want to close the worksheet. and tell it to update if it asks.

[Anonymous]

I understand. But I was asking about the workflow.
Are you bringing up the spreadsheet so the user can edit values, or in order to
edit it programmatically? If the latter, there's a better way to do this instead
of executing the command.

Sanjay-

[Anonymous]

Oh, sorry. I dont want the user to change any values. The values should already be set; but my iTable references a seperate Excel Workbook, i'll call it my master workbook, full of values to determine whether a component should be included or excluded in my assembly.

My current workflow, if values change in my 'master workbook' is to open the assembly, right click on the iTable and choose 'edit via spreadsheet', answer yes if i'm prompted to update the table because the values have changed,a workbook opens which I close without any changes, I again answer yes if i'm prompted to update the table, Inventor includes and excludes components based on their updated respective values.

If there is a better way to update the iTable I dont know it.

Another approach I've tried is to query the iTable to see if a component's respective value is "include" or "exclude" using visual basic. This I can do. However the only way I know to exclude a component using visual baisc is to send the exclude command, "ExcludeCtxCmd", to the command line. But I can not tell through visual basic if the component is already excluded or not in the assembly. I believe the exclude command in INV2008 is a toggle; so if I have a value of "exclude" returned from the iTable I would not want to send the exclude command if the component was already excluded in the assembly because that would then make it unexcluded.

I would like to completely automate this process if I can. And i'm not sure i'm coming at it in the best way. I'm teaching myself visual basic as I go so all input is greatly appreciated.

thanks.

[Anonymous]

One last question. Inventor's iPart/Assembly
documents embed the associated workbook in the document. I assume that the
"Master" workbook that you are referring to is this embedded spreadsheet. If so,
how are the values within that spreadsheet modified? Are you using Excel APIs to
modify these values?

If all of the editing is done programmatically,
then see the Inventor VBA macro below that demonstrates the right way to do
this. You can avoid all message boxes this way.

Sanjay-

' Make sure to add a reference to the Excel type
library.

Sub EditFactory()

    Dim oDoc As
AssemblyDocument
    Set oDoc =
ThisApplication.ActiveDocument
    
   
Dim oDef As AssemblyComponentDefinition
    Set oDef =
oDoc.ComponentDefinition
   
    Dim
oFactory As iAssemblyFactory
    Set oFactory =
oDef.iAssemblyFactory
   
    Dim oSheet As
WorkSheet
    Set oSheet =
oFactory.ExcelWorkSheet
    

    ' Modify the excel
sheet
    oSheet.Cells(2, 1) =
"Part1-02"
    oSheet.Cells(2, 2) =
"Part1-02"
    oSheet.Cells(2, 3) = "2
in"
    oSheet.Cells(3, 1) = "Part1-03"
   
oSheet.Cells(3, 2) = "Part1-03"
    oSheet.Cells(3, 3) = "4
in"
   
   
oDoc.Update
   
    Dim oWB As
workbook
    Set oWB =
oSheet.Parent
       

    oWB.Save
   
oWB.Close    

End Sub

[Anonymous]

right, when I say my "master" workbook i mean the one embedded in Inventor.

But the values in this workbook are changed by simple formulas. Here's an example: =IF('C:\Documents and Settings\jknoll\Desktop\[copyLS_MASTER.xls]PARAMETERS'!$N$18=2,"Include","Exclude") There is a formula similar to this in each cell that changes the values. There is no value editing neccessary.

my reason for editing the iTable via this embedded spreadsheet is so Inventor prompts for updating the iTable. Ordinarily I have to choose the "edit via spreadsheet" command, answer yes to a prompt to update the iTable, close the workbook that opens, and then answer yes again to the prompt upon closing the workbook to update the iTable. For the most part my iTable controls whether components are included or excluded.

I'd like those four steps automated. Or a better way to update the iTable.

thansk for the help.

[Anonymous]

Sanjay,
I took the code you wrote out and changed it a little:

{code}Sub Update_iAssy()

Dim oInvDoc As Document
Set oInvDoc = ThisApplication.ActiveDocument

Dim oIassy As iAssemblyFactory
Set oIassy = oInvDoc.ComponentDefinition.iAssemblyFactory

ThisApplication.SilentOperation = True
Dim oSheet As WorkSheet
Set oSheet = oIassy.ExcelWorkSheet

oInvDoc.Update
DoEvents

Dim oWB As Workbook
Set oWB = oSheet.Parent

oWB.Close
ThisApplication.SilentOperation = False

End Sub{code}

This seems to work but I'm still getting prompts to update the iTable. The two places that call a prompt are "Set oSheet=oIassy.ExcelWorkSheet" and "oWB.Close".

And here's something I cant figure out: if i'm stepping through the code in the editor and stop when the line "Set oSheet=oIassy.ExcelWorkSheet" is highlighted, but not executed yet, and mouse over "oIassy.ExcelWorkSheet" in this line a tooltip will come up saying "ThisApplication.SilentOperation=True". If I then press f8 to step to the next line, after seeing this tooltip, my code will continue without prompting to update until i get to "oWB.Close". The same thing does not work on the line "oWB.Close". And if I just step through the code without seeing that tooltip I get an update prompt after each of those lines. Is there something screwy about my code or the API that i'm not catching on to? I dont get it?

thanks for the help though,
-jeremy

[Anonymous]

Okay, I understand now. You basically need a way to
refresh the (out of date) table from the excel spreadsheet. There isn't a
formal API for this, but try this workaround of creating a dummy row and
deleting it. This should force a refresh.

Sanjay-

{code}

Sub Update_iAssy()
 
Dim oInvDoc As
Document
Set oInvDoc = ThisApplication.ActiveDocument
 
Dim oIassy
As iAssemblyFactory
Set oIassy =
oInvDoc.ComponentDefinition.iAssemblyFactory

Dim oDummyRow As iAssemblyTableRow
Set oDummyRow
= oIassy.TableRows(1).Copy
oDummyRow.Delete

End Sub

{code}

[Anonymous]

yep, thats exactly what i'm trying to do.

I like your dummy row idea...but its not forcing a refresh as is. I think this is because it duplicates the values of the cells of the row it is copying but not the formulas of those cells. So instead of copying the formula from a cell that references a seperate data spreadsheet and returns the value of "include" its just copying "include". I think because the table does not have a new formula it is not re-referencing my data spreadsheet and so not refreshing. I'm not sure though.

I've tried: {code} Dim oResetRow As String
oReset row = "=IF('C:\DOCUMENTS AND SETTINGS\JKNOLL\DESKTOP\[copyLS_MASTER.xls]PARAMETERS'!$N$18=1,"Include","Exclude")"
oIassy.TableRows(1).Item(3).Value = oResetRow
End Sub{code}

to set the value as the formula but i get a syntax error. I dont think it likes the equal signs and quotes, so I guess I cant set the formula via the API.

Any other ways to force the table to refresh in a similar way?

[Anonymous]

Sanjay,
I figured out thanks to that code you posted. The code I came up with will update my iTable and then proceed to delete any excluded components. Its probably not the cleanest thing but it works; here it is: {code}Sub Update_iAssy()

Dim oInvDoc As Document
Set oInvDoc = ThisApplication.ActiveDocument

Dim oIassy As iAssemblyFactory
Set oIassy = oInvDoc.ComponentDefinition.iAssemblyFactory

Dim oDummyRow As iAssemblyTableRow
Set oDummyRow = oIassy.TableRows(1).Copy

Call DeleteComponents

oIassy.Delete

Call oInvDoc.SaveAs("C:\Documents and Settings\jknoll\Desktop\xTest.iam", False)

oInvDoc.Close True

End Sub

Sub DeleteComponents()
Dim oInvDoc As AssemblyDocument
Set oInvDoc = ThisApplication.ActiveDocument

Dim oDocdef As ComponentDefinition
Set oDocdef = oInvDoc.ComponentDefinition

Dim oFactory As iAssemblyFactory
Set oFactory = oInvDoc.ComponentDefinition.iAssemblyFactory

Dim oOcc As ComponentOccurrence
Dim oOccName As String
Dim i As Long
Dim oOccNum As Long
oOccNum = oDocdef.Occurrences.Count

'checks if occurence is in table:
For Each oOcc In oDocdef.Occurrences
oOccName = oOcc.Name + ":Include/Exclude"
ContinueCheck:
For i = 3 To oFactory.TableColumns.Count
If (oFactory.TableColumns.Item(i).DisplayHeading) = oOccName Then
GoTo RowCheck
'checks if there are more colums to query and if the current occurrence is the last to be checked:
ElseIf i = oFactory.TableColumns.Count And oOccName = oDocdef.Occurrences.Item(oOccNum).Name Then
GoTo Finish
Else
End If
Next

NextoOcc:
Next

'if occurence is in table, checks if value is "Include" or "Exclude" for respective occurence and
'if there are more occurences to check:
RowCheck:
If oFactory.TableRows.Item(2).Item(i).Value = "Include" And oOcc.Name = oDocdef.Occurrences.Item(oOccNum).Name Then
GoTo Finish
ElseIf oFactory.TableRows.Item(2).Item(i).Value = "Include" And oOccName <> oDocdef.Occurrences.Item(oOccNum).Name Then
GoTo NextoOcc
ElseIf oFactory.TableRows.Item(2).Item(i).Value = "Exclude" And oOccName <> oDocdef.Occurrences.Item(oOccNum).Name Then
'deletes occurence if respective value is "Excluded" and moves to next occurence:
oOcc.Delete
oOccNum = oDocdef.Occurrences.Count
GoTo NextoOcc
Else
oOcc.Delete
End If


Finish:
End Sub{code}

A key to this working is that the file I am updating will be used as a Master assembly containing all possible components to the product my company makes. This Master file's iTable is driven by a seperate spreadsheet of values which include and exclude components to create the engineer's desired product. This makes a 'save as:' required to maintain the integrity of the Master assembly as the excluded components need to be deleted. I also must delete my iTable after I have my desired product so that it does not continue to reference my data spreadsheet and change values.
This is important because when you copy 'TableRows(1)' the cells in 'TableRows(1)' remain out of date, and the cells in 'TableRows(2)' are updated. The cells of 'TableRows(2)' are inserted above those of 'TableRows(1)' and so they are now the cells driving the iTable. When you delete the copied cells, 'TableRows(2)', the cells in 'TableRows(1)' remain out of date and again become the cells driving the iTable. This is ok because i delete the excluded components while 'TableRows(2)' is driving my iTable and then simply delete my iTable and do a 'saveas:' so that my Master file is not changed. This way I can continue to come back to my Master file time after time, creating products and saving them out.

Thank you very much for the help Sanjay,
-Jeremy