AnsweredAssumed Answered

Auto fill into excel file

Question asked by Jaray Mekkaew on Jun 10, 2008
Latest reply on Feb 15, 2011 by Adrian Velazquez

Like • Show 2 Likes2
Comment • 24

I attach file that show auto fill text into MS Word file (User fill into template input form of ECN).
My template is Excel file, How do i set it?

Attachments

ecn.jpg38.4 KB

No one else has this question

Outcomes

24 Replies

Corey Hinman Jun 10, 2008 11:20 AM
Mark Correct
Correct Answer
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:

=getproperty("filename")
Like • Show 0 Likes0
Actions
Corey Hinman @ Corey Hinman on Jun 10, 2008 11:28 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
Jeff Sweeney Jun 10, 2008 10:08 PM
Mark Correct
Correct Answer
Corey has a pretty good plan.

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.
Like • Show 1 Like1
Actions
Kyle Strong @ Jeff Sweeney on Dec 29, 2009 12:37 PM
Mark Correct
Correct Answer
This is exactly what i want to do but I am having trouble with the linking. Is there any documentation as to how to do this task? Thanks!
Like • Show 0 Likes0
Actions
Jeff Sweeney @ Kyle Strong on Dec 30, 2009 2:26 PM
Mark Correct
Correct Answer
Hmmm...I can't find any documentation either. However, did you see my example "Book1.zip" file in this post? It is (IMHO) a good example.
Like • Show 0 Likes0
Actions
Kyle Strong @ Jeff Sweeney on Dec 30, 2009 2:29 PM
Mark Correct
Correct Answer
Hey Jeff,

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
Like • Show 0 Likes0
Actions
Jeff Sweeney @ Kyle Strong on Dec 30, 2009 2:52 PM
Mark Correct
Correct Answer
Does this image answer your question?
Like • Show 1 Like1
Actions
Kyle Strong @ Jeff Sweeney on Dec 30, 2009 2:57 PM
Mark Correct
Correct Answer
Yeah I went into that but how do I link it to my data card's variable? Am I an idiot? I guess I already know the answer there. haha thanks
Like • Show 0 Likes0
Actions
Jeff Sweeney @ Kyle Strong on Jan 4, 2010 12:02 PM
Mark Correct
Correct Answer
Check out the topic "Variable Mapping" in the administration guide. It gets into linking Enterprise with MS Office files.
Like • Show 0 Likes0
Actions
Kyle Strong @ Jeff Sweeney on Jan 4, 2010 2:31 PM
Mark Correct
Correct Answer
That did it! Thanks! After I realized how easy it was I really feel like an idiot. Thanks for your help. This is a huge step in the right direction.
Like • Show 0 Likes0
Actions
Adrian Velazquez @ Jeff Sweeney on Feb 15, 2011 4:53 PM
Mark Correct
Correct Answer
Jeff,

This works beautifully! Thanks for sharing.
Like • Show 0 Likes0
Actions
Jaray Mekkaew Jun 10, 2008 11:38 PM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
1-A5CHD1 @ Jaray Mekkaew on Jun 11, 2008 10:58 AM
Mark Correct
Correct Answer
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.

Good luck.
Attachments
- ECN-000002.zip118.0 KB
Like • Show 0 Likes0
Actions
Jeff Sweeney @ Jaray Mekkaew on Jun 13, 2008 1:29 AM
Mark Correct
Correct Answer
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.)
Attachments
- Book1.zip3.6 KB
Like • Show 0 Likes0
Actions
Jaray Mekkaew Jun 11, 2008 12:07 PM
Mark Correct
Correct Answer
wow, this file so nice. it's a complicate case but good sample for me.

Thanks, Lee Young
Like • Show 0 Likes0
Actions
Corey Hinman @ Jaray Mekkaew on Jun 11, 2008 12:16 PM
Mark Correct
Correct Answer
Lee,

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?
Like • Show 0 Likes0
Actions
1-A5CHD1 Jun 11, 2008 12:52 PM
Mark Correct
Correct Answer
Corey,

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!
Like • Show 0 Likes0
Actions
1-A5CHD1 Jun 13, 2008 10:32 AM
Mark Correct
Correct Answer
Jeff,

How do you handle checkboxes without macros? I don't think one can.
Like • Show 0 Likes0
Actions
Jeff Sweeney Jun 13, 2008 12:31 PM
Mark Correct
Correct Answer
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!
Like • Show 1 Like1
Actions
1-A5CHD1 Jun 13, 2008 12:39 PM
Mark Correct
Correct Answer
Good point!

I never thought about just trying to use named ranges without macros. One learns something new everyday.
Like • Show 0 Likes0
Actions
dan burmenko @ 1-A5CHD1 on Aug 11, 2008 9:43 PM
Mark Correct
Correct Answer
Have you considered just using data card attributes to represent the ECO and not relying on having a full representation in Word or Excel?
Like • Show 0 Likes0
Actions
Jeff Sweeney Aug 12, 2008 8:40 PM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
Ben Gardner Nov 18, 2008 3:07 PM
Mark Correct
Correct Answer
Jeff -

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...
Like • Show 0 Likes0
Actions
Jeff Sweeney Nov 18, 2008 11:07 PM
Mark Correct
Correct Answer
What do you mean by a built in property? You mean something like seen in the "Summary" tab of the file?

If so, reference the "Variable Mapping" section in the Administration Guide. It should get you where you are going.
Like • Show 0 Likes0
Actions

Auto fill into excel file

Attachments

Outcomes

Attachments

Attachments

Related Content

Recommended Content

Incoming Links