Ok, I just set this up in Excel. You need to create
attributes for the variable...use customproperty and xls. (I'm
trying to do this off the top of my head, as I didn't document it
very well.....)
Then you need to set up a macro for when you open the workbook...it
will look something like this:
Private Sub Workbook_Open()
Sheet2.Calculate
Sheet2.Range("b4").Value = "getproperty(""Filename"")"
Sheet9.Range("n6").Value = "getproperty(""ECN_description"")"
End Sub
Then in the individual cells of the workbook type in the following:
I guess this is only half the equation, you still need to set
up template variables and push them to the XLS template document
(where you put that macro), but if you've done it in word, then you
should know how to do that again.
You could also name the cell ranges in your Excel template, then
link those named ranges to the file's properties. Then link the
PDMWE variable to the custom properties of the file.
The link works both ways (change the card, the cells update; change
the cells, the card updates.)
I haven't figured out how to do this with Excel 2007 though.
Yeah I saw your example but for some reason am not sure how to link A1 to my variable. I have a variable "Getman_Description" in my data card that I want linked to A1 but for some reason I must be missing something. Thanks Jeff
Thanks Corey Hinman and Jeff Sweeney.
In MS Word it's so easy, you can type any word and select menu
Insert>Field and select any field name , and save into template.
but, in excel it's difference. Could you attached sample excel file
template or anything solve this for me. Thanks
Attached is a copy of one of our ECNs. We use Excel 2007 but
the file is saved in compatibility mode, and we use named ranges
for everything. Take a look at the VBA code in the file, it should
get you started. I am sure this could be cleaned up in some places,
but I just haven't had the time.
This example does not require any macros AND it works both
ways. I can change the value in cell A1 and the data card will
update or I could change the datacard and the cell will update.
Here is the trick, go to the file properties and link them to a
named range. The file property is then linked to the data card.
By the way, it is easy to do in 2007, just you have to go to the
advanced area of the custom properties. (It is easier in earlier
versions.)
I think this would be a good idea for session at the 2009
Solidworks Conference. We use Enterprise for our Cost Estimates,
and ECN's. According to our VAR we use it to a greater depth than
most of the companies that he has done Enterprise work with.
Maybe we should start a workflow, (or specifically ECN) best
practice thread, as it relates to Enterprise?
I agree that it would be a good idea for a session on the
conference. As for a best practice thread, I am not sure how much I
could offer. I'm still tweaking our system and it's been over a
year!
I bet you are correct Lee, I am pretty sure it would take a
macro to modify values in Excel controls. Which is a compelling
reason to just put an "X" in a cell instead!
I'd love to be able to do this, but you run into some
limitations:
1. What if an ECO affects several parts?
2. I really don't want to have to make a new version of a file
every time someone wants to add information to an ECO datacard
3. If the ECO needs to contain a lot of text, reading/editing in a
data card is not going to be as easy as in another
application
Your method of creating named ranges and linking them to custom
properties works well for **custom properties**. Does anyone know
how to link to a built-in property? Seems so simple...
Then you need to set up a macro for when you open the workbook...it will look something like this:
Private Sub Workbook_Open()
Sheet2.Calculate
Sheet2.Range("b4").Value = "getproperty(""Filename"")"
Sheet9.Range("n6").Value = "getproperty(""ECN_description"")"
End Sub
Then in the individual cells of the workbook type in the following:
=getproperty("filename")