Its pretty straight forward how to reference list data from a spreadsheet in windows, but I have had no success to connecting to spreadsheets in Teamcenter. Does anyone have an example of how to have a NX expression reference a teamcenter spreadsheet?
I have made excel datasets under item revisions and even stand alone, but I can never seem to reference them.
When defining the function from the expression editor, select the f(x) icon and enter "ug_cell". Select ug_cell_vlookup, hit OK and check the radio button for "Teamcenter Integration for NX". Select "Specify a spreadsheet" and browse to the TC folder to the item revision containing the spreadsheet. Select the dataset, choose OK and complete the remaining fields and choose OK, or leave the fields blank and hit ok to see the arguments:
ug_cell_vlookup( "<SPREADSHEET_NAME>", <TARGET>, <RANGE>, <OFFSET>, <MODE> )
For example, item 6522816, revision A contains a spreadsheet with the following: A1=1, A2=2 and A3=3.
Entering the following syntax for the expression Formula:
ug_cell_vlookup( "6522816/A", "2", "A1:A3", 0, 3 ), returns a value of 1.
Ben, I am having problem getting past - Select "Specify a spreadsheet" and browse to the TC folder.
I have imported spreadsheets as standalone MSExcel datasets and I have also tried making a MsExcel dataset under Itemrevisions in Teamcenter.
They are just not selectable (dont even show up) through folder selection in "Teamcenter Integration for NX".
I found my issue, I was using the wrong excel dataset type. Need to specify MSExcelX dataset, not MSExcel.
Ben, thanks for your reply.
Thanks, the MSExcel dataset default settings releationship was set to "Manifestations". I realize now comparing default settings between MSExcelX and MSExcel datasets that they need to be manually changed to "Specifications". Thats why one worked and the other did not.
I am not sure where to ask this and this thread seams closest to the issue i am facing so here goes..
@BenBroad in case I want to refference a sheet inside an excel file on my local system (say, like sheet A in example.xlsx containing multiple other sheets ) how would the the ug_cell_vlookup command change?
also what do following error mean whhen I try the above?
also now that we are discussing this,
What would be the right way to link the excel to the model expressions in case the the location of the spreadsheet is subject to frequent changes ? (because otherwise the moment the excel sheet is moved updating the expressions shows an error in the values column).
PS: I am new to the forum.
Welcome to the forum.
So "Trunion_Flange_Hd" is a string expression in your part whose value you're comparing to a range of cells "A0:F200" (Does Excel have a row zero?) in spreadsheet "BFV STD Design Calculation.xslx". Your offset is "1" and you want an exact match.
If you're telling the system to read sheet "Upstream Pipe" your syntax is incorrect and should read:
ug_cell_vlookup( "<path>BFV STD Design Calculation.xslx", Trunion_Flange_Hd, "Upstream Pipe!A1:F200", 1, 0 )
If the spreadsheet is moved to a new location you're going to have to update the expressions. I was able to find Enhancement Request (ER) 1414636, which asks for the ability to use an environment variable for the path to the spreadsheet such that if the locations of the spreadsheet files had to be moved that you would only need to update the variable value and any parts referencing the variable would update accordingly.
May I make a suggestion and ask that in the future, rather than commenting on an old thread, please start a new one and add link to the old thread for reference.
Thanks alot @BenBroad
Ill keep in mind to start a new thread for a query from next time.
I knew I was doing something wrong..thanks for pointing it out!
Has there been any action on the Enhancement Request (ER) 1414636. can I view these requests on this website. if yes, then how?
greateful for such a prompt reply.