Cancel
Showing results for 
Search instead for 
Did you mean: 

Referencing Excel spreadsheet from Teamcenter

Experimenter
Experimenter

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.

 

Thanks.

11 REPLIES

Re: Referencing Excel spreadsheet from Teamcenter

Siemens Phenom Siemens Phenom
Siemens Phenom

Hi @Cadmaster,

 

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.

 

Regards, Ben

Re: Referencing Excel spreadsheet from Teamcenter

Experimenter
Experimenter

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". 

 

Thanks.

 

 

Re: Referencing Excel spreadsheet from Teamcenter

Experimenter
Experimenter

I found my issue, I was using the wrong excel dataset type.  Need to specify MSExcelX dataset, not MSExcel.

Ben, thanks for your reply.

 

Cadmaster.

 

Re: Referencing Excel spreadsheet from Teamcenter

Siemens Phenom Siemens Phenom
Siemens Phenom

According to the construct call both dataset types are valid.  You might want to open an IR with GTAC and have this investigated:

 

Referencing Teamcenter Spreadsheet in Expressions.png

 

Regards, Ben

Re: Referencing Excel spreadsheet from Teamcenter

Solution Partner Phenom Solution Partner Phenom
Solution Partner Phenom
Ben,

maybe it is that a MSExcel dataset can't contain a .xlsx file and a MSexcelX cant contain a .xls file

Ruud van den Brand
Pre-sales NX CAD
cards PLM Solutions

Re: Referencing Excel spreadsheet from Teamcenter

Experimenter
Experimenter

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. 

 

Cadmaster

Re: Referencing Excel spreadsheet from Teamcenter

Experimenter
Experimenter

Hi

 

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?

circular reference 

image.pngerror message for circular reference

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).

 

Regards

 

Prismac

 

PS: I am new to the forum.

 

 

 

 

Re: Referencing Excel spreadsheet from Teamcenter

Siemens Phenom Siemens Phenom
Siemens Phenom

Hi @prismac,

 

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.

 

Regards, Ben

Re: Referencing Excel spreadsheet from Teamcenter

Experimenter
Experimenter

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.

 

Regards

Prismac.