13 Replies Latest reply on Nov 6, 2012 2:49 PM by Tim Webb

    Populate Excel Cells with Referenced File Data?

    Brian Dalton

      I don't know if this is possible, but it would be pretty cool...

       

      If I have an Excel file in the vault and I create references to other files (specifically, SW drawings), can I somehow have cells in the Excel file populated with values from properties in the drawing file?

       

      I'm thinking of, say, a Work Order form in Excel.  I'd like to be able to specify which parts are to be made by pasting the drawings as references to the Excel WO file and have EPDM automatically read P/N and Description info from the drawing files and fill the line items of the form with it.

       

      Am I dreaming?

       

      B-

        • Re: Populate Excel Cells with Referenced File Data?
          Jeremy Feist

          yes, this can be done. No, I do not know the details of how. we had something very similar set up for an ECO coversheet.

           

          a search for "link variable excel epdm" on this forum comes up with a few related discusions.

          • Re: Populate Excel Cells with Referenced File Data?
            Jeff Sweeney

            I hope you aren't dreaming about PDM...otherwise we need to talk.

             

            The best method I have found to achieve this is through Excel's VBA.

            • Re: Populate Excel Cells with Referenced File Data?
              Tim Read

              Hi Brian,

               

              The only way I can think of to achieve the result you are after is to have Excel query the EPDM SQL database directly to obtain the information. I would first create a user on the SQL Server that only has read access so that you don't mess things up.

               

              It should be absolutely do-able.


              Tim

              • Re: Populate Excel Cells with Referenced File Data?
                Tim Webb

                Brian, I agree with Jeff Sweeney.

                 

                VBA is the easiest method for accomplishing what you're trying to do. I did it in Word by creating "form fields" in the document and populate them from file properties. Excel is even easier because you can address the cells directly.

                 

                The ECO form is a word document that follows this lifecycle

                1. CM (config manager) uses an EPDM template to create the document number & open the empty ECO.
                2. CM fills in all the pertinent information.
                3. CM checks in ECO which starts the ECO on approval path emailing approval groups.
                4. ECO remains in an uneditable state throughout approval path.
                5. After final approval, CM checks out ECO in final state to open the document and allow the VBA code to populate the signatures from the electronic datacard signatures while it's in an editable condition (checked out).
                6. CM checks ECO in and initiates procedures to execute the changes detailed.

                 

                Use this code to read and write custom properties in Word:

                • Read the custom property data
                  • ActiveDocument.CustomDocumentProperties("<CustomPropName>").Value

                 

                • Write the data to a form field
                  • ActiveDocument.FormFields("<FormFieldName>").Result = "<CustomPropName>"

                 

                Use this code to read and write custom properties in Excel:

                • Read the custom property data
                  • ActiveWorkbook.CustomDocumentProperties.Item("<CustomPropName>")
                • Write the data to a cell
                  • Range("A1").Value = "<CustomPropName>"

                 

                 

                ECO Template.jpg

                • Re: Populate Excel Cells with Referenced File Data?
                  Wayne Matus

                  It may not be a pretty as the customization options, but the simplest solution would be to use the BOM capabilitires of EPDM. If you paste as reference the drawing files to your Excel spreadsheet, the drawings would appear in the BOM. You could create a special Bill of Material that would capture the data you want from each of the drawings. The BOM could be exported to Excel and copied and pasted into your Work Order spreadsheet.

                    • Re: Populate Excel Cells with Referenced File Data?
                      Brian Dalton

                      This sounds pretty close to what I'm looking for.  In our Work Order form (as in our ECO form) there needs to be the ability to list multiple documents, ie, multiple parts to be made or multiple drawings to be released under the same ECO.

                       

                      I was envisioning something like this:

                       

                      Use a template to create the Excel file and have a Template Card query the initiator for 'header' information, such as date, WO# (could be a serial), project code, initiator name, date required, etc.  This info would go into variables and be linked to header fields in the XL sheet for bi-directional updating.  So far so good.

                       

                      When it comes to creating the body of the document (the list of parts to be made) I had thought to paste references from the drawing files to the XL file so that one could see all the past work orders where a particular part was made (where used) or in the case of an ECO, it would allow all the files to move together through the release workflow by simply moving the ECO XL file through the workflow.

                       

                      However, in either case I would need some mechanism for informing EPDM that it should run through the list of files in the BOM, extract pertinent data from the custom properties of those files ($PRPSHEET properties, to be exact) and use them to populate the line items of the XL Sheet.  The fact that the total quantity of these files/references/line items would not be known in advance complicates the process even more.

                       

                      Wayne, by your suggestion, could a special BOM format be created that would allow a header section (with the info listed above) at the top followed by the list of parts?  Could extra columns be added (via an input card or manually) that would include info such as quantity required or disposition of stock?

                       

                      Finally, (because I really AM dreaming of EPDM...) if all of this could be wrapped up into an interface that allowed the initiator to select the parts desired by means of a search function and have the paste as reference applied en masse to all the parts identified by that searching?

                       

                      Boy, I don't ask for much, do I???

                       

                      B-

                        • Re: Populate Excel Cells with Referenced File Data?
                          Wayne Matus

                          Brian,

                           

                          When defining a Bill of Material in the Administration tool all you can do is define the columns. You cannot have a custom header. Also the quanity would not be right. To get the right quanity you would have to create a Named BOM of this Work Order BOM and modify the quanity. This would be a manual process.

                           

                          Rather than using Excel or Word as your WO or ECO form, you can use xml files with xsl stylesheets to format the forms. Advantages of xml is that they do not have to be opened and updated if custom properties are updated to get the text in the document that is linked to the updated custom properties. Disadvantage is that you need to know syntax of the stylesheets to format the forms. I did a presentation at SolidWorks World 2011 on using xml for ECO forms. You can see the recorded presentation on the World website. Joy Garon also did something a week ago.

                           

                          https://forum.solidworks.com/community/data_management/enterprise_pdm/blog/2011/07/11/using-xml-documents-in-epdm

                           

                          I have the sample files from my presentation on my website on the download page.

                           

                          Automating your ECx Process in SolidWorks Enterprise PDM

                           

                          I even have the source code for an add-in that gets the attached referencxe files and adds it ot the ECO from. It would have to be modified to also get the additional variables of the reference files such as description. It would take some work, but it could even get quanities. If you cannot do it yourself, I could do it for a fee.

                           

                          That is one thing I like about EPDM out of the box you can manually do anything you want. Using the API you can automate almost anything.

                           

                          On yes, one more thing. Yes you can do a search and from the search results select multiple files and copy. Then when the WO form is checked out, paste as reference all of the files at once.

                            • Re: Populate Excel Cells with Referenced File Data?
                              Brian Dalton

                              This sounds very interesting, Wayne..  Thanks for the suggestion.

                               

                              I have no experience with XML so perhaps you could answer a few questions I have.

                               

                              1.  What application reads/displays XML files?  I know they can be viewed in EPDM but is there a native XML program?

                               

                              2.  You mention that they are printable... How is that done? (similar to the previous question, ie, what program prints them?)

                               

                              3.  Are XLS style sheets strictly necessary?  In HTML, I have found using CSS to be highly confusing and cumbersome. Maybe I'm an old fart (no maybe about it ;O ) but I prefer formatting inline for simplicity and easy code reading.

                               

                              4.   It seems that data from EPDM can be updated, formatted, viewed and printed in an XML file, but can the XML file actually be used for data input?  That is, can it be used as a live form or must all data input be done through template cards, add-in dialogs, etc.?  If so, will it update bi-directionally?

                               

                              Please forgive all the rookie questions.  There may not be such a thing as a stupid question, but there certainly can be annoying ones!   This rookie appreciates all the guidance he can get.

                               

                              Brian-

                                • Re: Populate Excel Cells with Referenced File Data?
                                  Wayne Matus

                                  Brian Dalton wrote:

                                   

                                  This sounds very interesting, Wayne..  Thanks for the suggestion.

                                   

                                  I have no experience with XML so perhaps you could answer a few questions I have.

                                   

                                  1.  What application reads/displays XML files?  I know they can be viewed in EPDM but is there a native XML program?

                                   

                                  I use IE or Firefox to view and print

                                   

                                  2.  You mention that they are printable... How is that done? (similar to the previous question, ie, what program prints them?)

                                   

                                  3.  Are XLS style sheets strictly necessary?  In HTML, I have found using CSS to be highly confusing and cumbersome. Maybe I'm an old fart (no maybe about it ;O ) but I prefer formatting inline for simplicity and easy code reading.

                                   

                                  Yes, they are required but they could be embedded into the XML file. It is not like HTML. The problem with HTML format is that we cannot map EPDM variables to custom properties. I wish we could somehow!

                                   

                                  4.   It seems that data from EPDM can be updated, formatted, viewed and printed in an XML file, but can the XML file actually be used for data input?  That is, can it be used as a live form or must all data input be done through template cards, add-in dialogs, etc.?  If so, will it update bi-directionally?

                                   

                                  Yes, the XML file can be edited and the data is bi-directional. You can use Notepad, Word. or an XML editor. You can also edit the variable values on the file data card and update the XML file.

                                   

                                  Please forgive all the rookie questions.  There may not be such a thing as a stupid question, but there certainly can be annoying ones!   This rookie appreciates all the guidance he can get.

                                   

                                  Brian-

                            • Re: Populate Excel Cells with Referenced File Data?
                              Tim Webb

                              Hi Wayne

                               

                              I have been using paste as reference to create BOM info just like this to link a job card traveler to released drawings. The issue I ran into lately is interesting and unexpected or maybe it is a bug.

                               

                              Check out a job card traveler

                              Open a search window to find a drawing it refers to

                              RMB copy on the drawing

                              RMB paste as reference on the job card traveler

                              Check in the job card traveler

                               

                              Sounds great doesn't it? Problem arises when the drawing gets revised or the next version. As soon as the next version of the drawing is created, the reference link disappear.

                               

                              I do not want to have to recreate the links when drawings REV because around my shop they REV often.

                               

                              Have you (or anyone else) experienced this? If so, is there a workaround? Or do we need to do the legwork to reattach?

                               

                              Confusing

                              Thanks man.

                              Tim