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

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

    Re: Import from Excel to DOORS

    ‏2008-12-08T17:19:53Z  
    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.
  • dpechacek
    dpechacek
    121 Posts

    Re: Import from Excel to DOORS

    ‏2008-12-11T15:56:52Z  
    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

    Re: Import from Excel to DOORS

    ‏2008-12-16T09:39:55Z  
    • dpechacek
    • ‏2008-12-11T15:56:52Z
    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
    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

    Re: Import from Excel to DOORS

    ‏2008-12-16T11:00:34Z  
    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
    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

    Re: Import from Excel to DOORS

    ‏2011-11-10T16:10:50Z  
    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

    Re: Import from Excel to DOORS

    ‏2011-11-10T18:45:57Z  
    • kabr
    • ‏2011-11-10T16:10:50Z
    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
    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
    1969 Posts

    Re: Import from Excel to DOORS

    ‏2011-11-11T09:24:58Z  
    • kabr
    • ‏2011-11-10T16:10:50Z
    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
    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

    Re: Import from Excel to DOORS

    ‏2011-11-11T12:15:10Z  
    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

    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

    Re: Import from Excel to DOORS

    ‏2011-11-17T14:46:01Z  

    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
  • jlnddn
    jlnddn
    14 Posts

    Re: Import from Excel to DOORS

    ‏2014-03-05T09:08:47Z  
    • kbmurphy
    • ‏2008-12-08T17:19:53Z
    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.

    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?