Spreadsheet functions in Tools->Expressions

[man2007]

I have couple of questions regarding Spreadsheet functions available in Tools->Expreassions...

1) Can I refer modeling spreadsheet (in the same file), i.e. I have written two columns of data in Tools->spreadsheet and want to refer the cells in the spreadsheet. Yes, I can refer an external spraedsheet, but that is not what I am looking for.

I tried selecting part file (while using ug_cell_vlookup function) for "specify a spreadsheet" dialog, but I am getting the error like "Failed to open ...prt".

2)In the function ug_cell_vlookup, I want to use an extression, (say x), for "Specify the lookup target", but it is asking for a string.

I am using NX4.

 

[man2007]

Well, I got solution for the 2nd question. I used char(ASCII("1")+x)

 

[JohnRBaker]

No, the internal spreadsheet is not actually a 'spreadsheet'. The various 'internal' spreadsheet functions (Modeling, Gateway and Part Family) merely uses an 'external' spreadsheet, such as Excel on Windows and XESS on UNIX/LINUX, as the mechanism for entering and setting up formulas and relationships. However, it is only an 'active' element when the external spreadsheet is open and NX is placed in a sort of 'pause' mode. During this time your only access to the data is via the spreadsheet, but once the spreadsheet task is halted and the contents are downloaded to the Part file, the 'spreadsheet' data becomes a 'read-only' data record stored in a proprietary and 'neutral' NX format.

Now that being said, by definition, ANY expression can be linked to that data (at least in the case of the Modeling and Part Family 'spreadsheets') but it's still only read-only.

Now when accessing an 'external' spreadsheet by an NX expression function, that data is also basically 'read-only' since again it's just a data record, only this time being a disk record stored in a native spreadsheet format, rather than in some proprietary CAD-vendor specific format.

The advantage of accessing an external spreadsheet is that a) you don't have to be running NX when you make changes or updates to the spreadsheet which means it can be part of processes and tools used by non-CAD users, b) the same spreadsheet and it's data can be accessed by many different NX part files thus making it easier to share common data with either many parts of one project or several different projects at once.

Anyway, I hope this explains the relationship between 'internal' and 'external' spreadsheets and why we implemented the functionality the way we did.

John R. Baker, P.E.
Product 'Evangelist'
Product Design Solutions
Siemens PLM Software Inc.
Cypress, CA

http://www.siemens.com/plm

http://www.plmworld.org/museum/

To an Engineer, the glass is twice as big as it needs to be.

 

[man2007]

Thanks for your immediate reply.

I am creating law curve, by refering excel data. I find no reason for sending this "External Spreadsheet" along with ug file (to my customer), which is difficult to "maintain". It is much more convenient if I can use "Modeling Spreadsheet" of the same part, so that the data is stored in the part file itself.

BTW, what is the meaning of 3 file type options (.xls, .prt, All files) that are available when "specify a spreadsheet" button is clicked? I searched thru documentation, but no luck.

What my understanding is, when we select a part file (in the above dialog), we should be able to refer to "Part File's Spreadsheet", otherwise what is the need for giving the option of selecting the Part file when Spreadsheet is needed?

 

[JohnRBaker]

I think that may be a mistake as I get a 'fail to open' error if I enter a part file name even if that part contains a spreadsheet record. I won't be able to check this out until next week when I've back in the office.

Note that Knowledge Fusion supports reading the internal spreadsheet record, but looking at the documentation for the expression spreadsheet functions it only mentions external spreadsheets where as KF talks about accessing either internal or external spreadsheets. Perhaps the file selection dialog was implemented in anticipation of the expression functions being enhanced to support the same data as the KF calls and they just never got around to supplying the additional routines. But like I said, I won't be able to talk to anyone who would know the details of this until Monday at the earliest.

John R. Baker, P.E.
Product 'Evangelist'
Product Design Solutions
Siemens PLM Software Inc.
Cypress, CA

http://www.siemens.com/plm

http://www.plmworld.org/museum/

To an Engineer, the glass is twice as big as it needs to be.

 

[man2007]

John,

Can you confirm with your technical team whether we can use internal spreadsheet.

I have one more question,

As of now I am trying to create law curve by using external spreadsheet, As the value of t varies from 0 to 1, I am selecting different rows of spreadsheet. But this is not working because, the values are not "Automatically" changing based on the value of t, until I hit "Refresh values from external spreadsheet" button on Expressions dialog. Is there any option for Automatically updating an expression based on spreadsheet values.

 

[JohnRBaker]

Not that I'm aware of. The expression function, which reads the spreadsheet value, will only read that value once every time the model is updated, or in this case, for only one, and probably the final, value of 't'.

As for the internal versus external spreadsheet issue, I'll have to get back to you.

John R. Baker, P.E.
Product 'Evangelist'
Product Design Solutions
Siemens PLM Software Inc.
Cypress, CA

http://www.siemens.com/plm

http://www.plmworld.org/museum/

To an Engineer, the glass is twice as big as it needs to be.