Topic
10 replies Latest Post - ‏2014-03-18T13:33:46Z by KBSri
SystemAdmin
SystemAdmin
3180 Posts
ACCEPTED ANSWER

Pinned topic Import from Excel to DOORS

‏2008-12-08T15:57:19Z |
Hello,
Hope any one can give me a hint on how to get the task done.
I know - normally it is suggested to convert the Excel sheet into CSV file format and then import the file. But this won't work in this case, as the Excel sheet is filled from different Macros, and when I want to export data to CSV, the information is lost or reformatted in a way that I can no longer make any use of it.

OK, I know that using VBA one can import Word Documents from WORD to DOORS. Now I hope that something similar also may be done with Excel Books. Therefore I had a short look at the Telelogic DOORS API Manual, and found in chapter 5 "DXL API integration features" the "eval_" and "return_" commands, which somehow appear to be familiar to me from a former WORD to DOORS import script I wrote a couple of years ago, but I can no longer get hold of the script :(, so I cannot verify it.
Anyway there seems to be something more to it than I can remember, as it does not work at the moment.
Can anyone please just give me a short warm-up on how to get back started.
Ta
Kerstin
Updated on 2011-11-17T14:46:01Z at 2011-11-17T14:46:01Z by kabr
  • kbmurphy
    kbmurphy
    160 Posts
    ACCEPTED ANSWER

    Re: Import from Excel to DOORS

    ‏2008-12-08T17:19:53Z  in response to SystemAdmin
    Kerstin,

    You can try this script:

    Import from Excel

    Another idea is to do a copy/paste_special into a new sheet and then PASTE VALUES. This will remove some of the CSV trickiness that occurs.

    Good luck.
    • jlnddn
      jlnddn
      14 Posts
      ACCEPTED ANSWER

      Re: Import from Excel to DOORS

      ‏2014-03-05T09:08:47Z  in response to kbmurphy

      Hello,

       

      I used this script which worked perfectly but since I have DOORS 9.5 and Office 2010 it doesn't work anymore...

      Even after modifying the script so it can read .xlsx files, nothing happens...

      Do you have a solutions please?

  • dpechacek
    dpechacek
    121 Posts
    ACCEPTED ANSWER

    Re: Import from Excel to DOORS

    ‏2008-12-11T15:56:52Z  in response to SystemAdmin
    Just make sure you get the most up to date version. I updated it in the past few days.

    AAI Services, Textron
    dpechacek@sc-aaicorp.com
    David.Pechacek@gmail.com
    • SystemAdmin
      SystemAdmin
      3180 Posts
      ACCEPTED ANSWER

      Re: Import from Excel to DOORS

      ‏2008-12-16T09:39:55Z  in response to dpechacek
      Hello,
      many thanks for your support. I now got most of the importing of my excel spread sheet working. Sorry, I coul not use your script, as the information to be imported is mixed between columns and rows. Although, I found your include file for accessing excel is very useful - thanks!
      Now only one probably pretty stupid question - but is there any option to extend the DXL timeout for DOORS 7.1? It is pretty annoying to click about ten times the continue button if the excel spread sheet is as large as the one I was provided.
      Regards
      Kerstin
      • SystemAdmin
        SystemAdmin
        3180 Posts
        ACCEPTED ANSWER

        Re: Import from Excel to DOORS

        ‏2008-12-16T11:00:34Z  in response to SystemAdmin
        You can modify the timeout with a "pragma", set the runLim to 0.

        From DXL Help:

        Pragmas
        Pragmas modify the background behavior of the DXL interpreter, for example:

        pragma runLim, int cyc
        sets the time-out interval cyc as a number of DXL execution cycles. The time-out is suppressed if cyc is set to zero, as shown in the following example:

        pragma runLim, 0 // no limit
        pragma runLim, 1000000 // explicit limit
  • kabr
    kabr
    115 Posts
    ACCEPTED ANSWER

    Re: Import from Excel to DOORS

    ‏2011-11-10T16:10:50Z  in response to SystemAdmin
    Hi,

    we've been using David Pechacek's great Import from Excel for quite some time.
    But lately our company has updated all client machines from Office 2003 to Office 2007 and the option to import the Excel Print Area does not seem to work anymore.

    The function 'getPrintArea' from 'ExcelFunctions.inc' always returns an empty string, no matter if the print area is set or not in the excel file.

    Probably something in Excel has changed (e.g. columns are numbered in Excel 2007 and no longer have letters), if I only knew what.
    I know it is not the most important option, but it still is disappointing that it does not work anymore.

    Can anybody shed some light on this issue?

    many thanks in advance

    Karl
    • kbmurphy
      kbmurphy
      160 Posts
      ACCEPTED ANSWER

      Re: Import from Excel to DOORS

      ‏2011-11-10T18:45:57Z  in response to kabr
      Yes there are differences between Excel 2003 and 2007, but the columns being numbered? That's a user preference.

      I'm on 2010, but in one of the options you'll see a line called "R1C1 reference style". Uncheck it. You now have your letters back.

      Kevin
    • Mathias Mamsch
      Mathias Mamsch
      1938 Posts
      ACCEPTED ANSWER

      Re: Import from Excel to DOORS

      ‏2011-11-11T09:24:58Z  in response to kabr
      Hi Karl,

      I had issues with Excel compatibility from 2003 to 2007 too with some of my own applications. Microsoft changed its data model, so you might need to adapt some OLE calls. If you tell us the error message that you are getting, then we might be able to help you better.

      Regards, Mathias

      Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS
      • kabr
        kabr
        115 Posts
        ACCEPTED ANSWER

        Re: Import from Excel to DOORS

        ‏2011-11-11T12:15:10Z  in response to Mathias Mamsch

        Hi Mathias,

        the thing is, I do not get an error message.
        David's Import from Excel has a function in the ExcelFunctions.inc that retrieves the print area if it is set in the excel sheet.

        oleGet(objPageSetup, cPropertyPrintArea, printArea)
        


        When the columns in the Excel sheet have numbers instead of letters this function returns an empty string even when the print area is set.

        With Kevin's suggestion to uncheck "R1C1 reference style" in the Excel options the function returns a string like '$E$1:$K$7'.
        As a workaround solution this works fine (thanks Kevin!) but of course I would like it to work in both cases.

        Karl

        Updated on 2013-12-18T18:13:35Z at 2013-12-18T18:13:35Z by iron-man
  • kabr
    kabr
    115 Posts
    ACCEPTED ANSWER

    Re: Import from Excel to DOORS

    ‏2011-11-17T14:46:01Z  in response to SystemAdmin

    some more info:

    English installation of Excel 2007

    • file saved in 2003 compatibility mode: no problem
    • print area in "$A$1:$D$7" format causes no problem
    • print area in "R1C1:R7C4" format: was able to solve by myself - no problem


    German installation of Excel 2007

     

    • file saved in 2003 compatibility mode: no problem
    • no matter which format, the string returned by

     

    oleGet(objPageSetup, cPropertyPrintArea, printArea)
    


    is empty.

    Updated on 2013-12-18T18:13:08Z at 2013-12-18T18:13:08Z by iron-man
    • This reply was deleted by KBSri 2014-07-26T13:29:41Z.
      • This reply was deleted by KBSri 2014-07-26T13:29:33Z.