10 Replies Latest reply on Aug 22, 2011 5:50 PM by Brian Dalton

    Trouble with EC form in Excel: Serial Number field

    Brian Dalton

      With much help from the giant brains on this forum, I've been working on a simple Engineering Change form in Excel.  It is set up with each cell as a named range, linked to a Custom Property.  Using Office 2007 (xlsx).

       

      I created a file card for xlsx with all the fields mapped to variables, just like other cards.  I created a Template Card with all the input fields except for the ECNumber field, which will get its value from a Serial Number, not user input.

       

      I created a Serial Number for automatically generating the new ECNumber.  It is set up the same way as those I use for new part numbers, etc.

       

      I created a Template that has Template Variables of type Format String for all fields except ECNumber, which has a Template Variable of type Serial Number, linked to the ECNumber Serial Number I created above.

       

      I set the filename to EC%ECNumber%.xlsx and mapped the Template Variables to their corresponding File Card Variables.

       

      So far, so good, but here's the problem:

       

      When I use the template from the menu item, it creates the file with the correct number (next in line in the Serial Number), and it correctly populates the file card, but no values show in the cells in the Preview Pane.  If I check out the new EC file and open it in Excel, all the fields are correctly populated except the ECNumber cell, which is blank.  Then I save the EC file, check it back in, and suddenly the File Card shows the value of the Date field where the ECNumber should be.  Somehow the value is getting corrupted when I save the Excel file.

       

      Symptoms:

      1. ECNumber never shows in the linked cell in the Excel file
      2. After saving the Excel file, the ECNumber field in the file card takes the value from the Date field (which is just text).

       

      I'm so new to this that I have no clue where to look for the problem.  The mechanisms I'm using are working 95%, but what the heck is wrong with the ECNumber ?

       

      Brian-

        • Re: Trouble with EC form in Excel: Serial Number field
          Brian Dalton

          After a break, I've come back to this problem, but so far I've had no breakthroughs..  It seems that it should work as well with s/n defines card fields as with the others, but it doesn't.

           

          I'll comb through the (minimal) documentation some more and see if I missed something.

            • Re: Trouble with EC form in Excel: Serial Number field

              Have you looked at the examples of the Excel files in this thread? In the example I posted, there is a macro that is called when the workbook is opened. It updates named ranges with the properties from the file. I think you need to do that, as Excel won't show the vaule unless you update it.

               

              When you open the Excel file, look at the custom properties of the file and see if your data is there. (For Office 2007, Office button -> Prepare -> Properites -> Custom Properties.)

               

              Jeff posted a solution as well.

               

              Your property sounds like it is updating but isn't being displayed.

               

              If you still have issues with it, post your Excel file and we can take a look at it. Let us know what the ECN number should be as well.

                • Re: Trouble with EC form in Excel: Serial Number field
                  Brian Dalton

                  I'm trying this again, with a different file and a different version of EPDM (2011)...  I've looked at the examples but mine still doesn't act the way I think it should.

                   

                  I have created named ranges/custom properties for DCRNumber, Initiator and Description.  DCRNumber is created by the Template using a serial number. 

                   

                  When I use the template to create a new DCR the fields don't appear in the Preview pane, but they're good on the data card.  If I check out and open the new Excel file I see that the Intiator and Description values made it through to the cells, but not the DCRNumber (seems to be something different about linking a value that is created from a Serial Number generator).

                   

                  If I close the file and check it back in, I see that the Preview Pane still doesn't show the values.  In order to get the values to trickle down into the Preview Pane is to check out the file, change something in it, then save it and check it back in.  Then the preview pane is OK.

                  • Re: Trouble with EC form in Excel: Serial Number field
                    Brian Dalton

                    So, essentially, I have no preview at all until I check out, open, change, save and check in the file.  Is that what you'd normally expect from this method?

                     

                    As regards the DCRNumber created from the Serial Number generator, it won't go to the Excel file, period.  Just blank.  If I change the value in the data card, then that value is sent to the Excel file, but the data card reverts to its previous value (???).

                     

                    And still, nothing shows in the Preview Pane until I change the file and save it and check it in.  Then, the Preview Pane shows the same value as the Excel file, but the Data card still shows the original value!!??!!

                     

                    Maybe a weekend will shed some light on this, but for now I'm totally confused..

                • Re: Trouble with EC form in Excel: Serial Number field
                  Brian Dalton

                  OK, I managed to get it working, but I still don't know WHY it works...

                   

                  My variable (named 'Number') is mapped to an Excel custom property called 'DCRNumber'.  This I linked to a range that I also named 'DCRNumber'.  Didn't work.

                   

                  I renamed the custom property to 'Number' (the same name as the card variable I'm mapping to it) and suddenly it works.  The strange thing is that the variable mapping attribute still refers to the custom property 'DCRNumber'.  But that custom property doesn't exist.  I changed it to 'Number'.

                   

                  If anyone can explain why this works I'd be happy to learn.  For the meantime I'm satisfied that I can create a functional - if illogical - mapping of a serial number generated value into my Excel spreadsheet cell.

                   

                  Thanks to everyone who contributed to this discussion.