24 Replies Latest reply on Feb 15, 2011 4:53 PM by Adrian Velazquez

Auto fill into excel file

Jaray Mekkaew Jun 10, 2008 1:28 AM

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?

ecn.jpg 38.4 KB

Auto fill into excel file

Corey Hinman Jun 10, 2008 11:20 AM (in response to Jaray Mekkaew)

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 Likes(0)

Actions
- Auto fill into excel file
  
  Corey Hinman Jun 10, 2008 11:28 AM (in response to Corey Hinman)
  
  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 Likes(0)
  
  Actions
Auto fill into excel file

Jeff Sweeney Jun 10, 2008 10:08 PM (in response to Jaray Mekkaew)

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 Likes(1)

Actions
- Re: Auto fill into excel file
  
  Kyle Strong Dec 29, 2009 12:37 PM (in response to Jeff Sweeney)
  
  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 Likes(0)
  
  Actions
  - Re: Auto fill into excel file
    
    Jeff Sweeney Dec 30, 2009 2:26 PM (in response to Kyle Strong)
    
    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 Likes(0)
    
    Actions
    - Re: Auto fill into excel file
      
      Kyle Strong Dec 30, 2009 2:29 PM (in response to Jeff Sweeney)
      
      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 Likes(0)
      
      Actions
      - Re: Auto fill into excel file
        
        Jeff Sweeney Dec 30, 2009 2:52 PM (in response to Kyle Strong)
        
        Does this image answer your question?
        
        Like Show 1 Likes(1)
        
        Actions
        
        Re: Auto fill into excel file
        
        Kyle Strong Dec 30, 2009 2:57 PM (in response to Jeff Sweeney)
        
        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 Likes(0)
        
        Actions
        
        Re: Auto fill into excel file
        
        Jeff Sweeney Jan 4, 2010 12:02 PM (in response to Kyle Strong)
        
        Check out the topic "Variable Mapping" in the administration guide. It gets into linking Enterprise with MS Office files.
        
        Like Show 0 Likes(0)
        
        Actions
        
        Re: Auto fill into excel file
        
        Kyle Strong Jan 4, 2010 2:31 PM (in response to Jeff Sweeney)
        
        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 Likes(0)
        
        Actions
- Re: Auto fill into excel file
  
  Adrian Velazquez Feb 15, 2011 4:53 PM (in response to Jeff Sweeney)
  
  Jeff,
  
  This works beautifully! Thanks for sharing.
  
  Like Show 0 Likes(0)
  
  Actions
Auto fill into excel file

Jaray Mekkaew Jun 10, 2008 11:38 PM (in response to Jaray Mekkaew)

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 Likes(0)

Actions
- Auto fill into excel file
  
  1-A5CHD1 Jun 11, 2008 10:58 AM (in response to Jaray Mekkaew)
  
  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.
  
  ECN-000002.zip 118.0 KB
  
  Like Show 0 Likes(0)
  
  Actions
- Auto fill into excel file
  
  Jeff Sweeney Jun 13, 2008 1:29 AM (in response to Jaray Mekkaew)
  
  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.)
  
  Book1.zip 3.6 KB
  
  Like Show 0 Likes(0)
  
  Actions
Auto fill into excel file

Jaray Mekkaew Jun 11, 2008 12:07 PM (in response to Jaray Mekkaew)

wow, this file so nice. it's a complicate case but good sample for me.

Thanks, Lee Young
Like Show 0 Likes(0)

Actions
- Auto fill into excel file
  
  Corey Hinman Jun 11, 2008 12:16 PM (in response to Jaray Mekkaew)
  
  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 Likes(0)
  
  Actions
Auto fill into excel file

1-A5CHD1 Jun 11, 2008 12:52 PM (in response to Jaray Mekkaew)

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 Likes(0)

Actions
Auto fill into excel file

1-A5CHD1 Jun 13, 2008 10:32 AM (in response to Jaray Mekkaew)

Jeff,

How do you handle checkboxes without macros? I don't think one can.
Like Show 0 Likes(0)

Actions
Auto fill into excel file

Jeff Sweeney Jun 13, 2008 12:31 PM (in response to Jaray Mekkaew)

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 Likes(1)

Actions
Auto fill into excel file

1-A5CHD1 Jun 13, 2008 12:39 PM (in response to Jaray Mekkaew)

Good point!

I never thought about just trying to use named ranges without macros. One learns something new everyday.
Like Show 0 Likes(0)

Actions
- Auto fill into excel file
  
  dan burmenko Aug 11, 2008 9:43 PM (in response to 1-A5CHD1)
  
  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 Likes(0)
  
  Actions
Auto fill into excel file

Jeff Sweeney Aug 12, 2008 8:40 PM (in response to Jaray Mekkaew)

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 Likes(0)

Actions
Auto fill into excel file

Ben Gardner Nov 18, 2008 3:07 PM (in response to Jaray Mekkaew)

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 Likes(0)

Actions
Auto fill into excel file

Jeff Sweeney Nov 18, 2008 11:07 PM (in response to Jaray Mekkaew)

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 Likes(0)

Actions