Link Excel file to Parameters via file dialog and Ilogic
Hi @j3scat. According to the error text at the start of that last post, the error is happening at the line where you are trying to use that AddExcelTable method. I wander if it might possibly have a problem with the location of the file. Perhaps as a test, you could copy the file locally, just for a temporary test, then try linking to it in that local location, just to see if that makes any difference. As a process of elimination step. Is that excel file 'macro enabled'? Is it possible that there might be some sort of 'access permissions' on that file, or on that file location that may be preventing this linking?
Wesley Crihfield
(Not an Autodesk Employee)
I'm using 2022. I loaded and tried 2023, and receive the same results.
I was initially trying ThisApplication.ActiveDocument 😕
You can see my linked '30-0096-0001.xlsx' under 3rd third party, but nothing in the parameters box.
If I delete the 3rd Party link and add via the 'Link' button in the Parameters box I end up with this.
EDIT:
I created a new excel file, not one from template and just one line (no headers) and it loaded. I must have some hidden formating issue. Weirdly it fails with files from years ago, so my formating issue is very old.
Hi @j3scat. As a quick test, I created a new simple part document, sketched a rectangle, extruded it, and saved the part. Created 3 new user parameters named "WIDTH", "HEIGHT", & "DEPTH", and have them values (6, 3, & 1), then put those user parameter names as the 'equation' of the 3 model parameters which control the 2D base size, and the extruded depth. Then saved it again. Then created a new Excel document, put those same 3 user parameter names as the first 3 cells in the first column, then different simple numerical values for them in the first 3 cells of the second column. When I linked that Excel file to the part, it instantly replaced my 3 user parameters with the 3 newly linked parameters (exact same names), and the model parameters instantly reflected those new values. Once I clicked the update button, the model reflected the new size, as expected. Then I clicked UNDO to before linking the Excel file. Modified the 3 values so that the second two used the name of the first one in their equation, then saved and closed that Excel file. When I linked it, it also worked exactly as expected. So, this proves that you can have a set of user parameters in your model with the same names as ones you are planning on linking from Excel, and as long as the equations can be calculated, it should work OK. I'm thinking that you could use an iLogic rule to read the list of parameter names from the first column of the Excel file first, then create that set of parameters with default values (such as 1 for numerical) in a loop, then maybe do an update, then create the link to that Excel file right afterwards. That could all be automated for you by a rule.
Edit: However, if you are actually newly creating all those user parameters from a list like that, integrating their names into the equations of any model parameters that may already exist may be the more challenging task, unless maybe you have renamed all of your model parameters to more useful and identifiable names before this point. But I guess if the model file is new, and there are no pre-existing model parameters, you could simply set each new dimension or feature spec to the name of one of these linked parameters after the fact, as you build the model, which would be pretty nice.
Wesley Crihfield
(Not an Autodesk Employee)
Hi @j3scat. Here is a pretty basic iLogic rule for this task, which just uses the GoExcel tools that are available in the iLogic AddIn for accessing Excel stuff. You will obviously need to change the path & file name of the Excel file, and/or sheet name. And you will likely have to edit the cell address range too, to fit how many parameter names are in your list. There are more advanced ways that could be used for determining that range size by code, so you don't have to hard-code cell addresses in there, but this should be a good starting point. It creates the user parameters first, and just assigns 1 inch as their default starting values. Then it sets up the link after that point. However, for some reason, it is not updating the parameter values to match the excel file values yet when the rule finishes. If I open the Excel file, save it, then close it, then update the part, that seems to update the parameter values to the values they should be though.
Dim oXLFile As String = "C:\Temp\Params 2.xlsx"
Dim oSheet As String = "Sheet1"
GoExcel.Open(oXLFile, oSheet)
GoExcel.DisplayAlerts = False
PNames = GoExcel.CellValues("A1", "A3")
If PNames.Count = 0 Then Exit Sub
Dim oPDoc As PartDocument = ThisDoc.Document
Dim oParams As Inventor.Parameters = oPDoc.ComponentDefinition.Parameters
Dim oUParams As UserParameters = oParams.UserParameters
For Each PName In PNames
Dim oUParam As UserParameter = Nothing
Try
oUParam = oUParams.Item(PName)
Catch
oUParam = oUParams.AddByExpression(PName, "1", UnitsTypeEnum.kInchLengthUnits)
End Try
Next
oPDoc.Rebuild
Dim oPTables As ParameterTables = oParams.ParameterTables
Dim oPTable As ParameterTable
Try
oPTable = oPTables.AddExcelTable(oXLFile, "A1", True)
Catch
MsgBox("Linking Excel table failed!", vbCritical, "Excel Link Failed")
End Try
oPDoc.Rebuild
GoExcel.ClearCache
GoExcel.Close
'GoExcel.QuitApplicationWesley Crihfield
(Not an Autodesk Employee)
