Copy / Paste Excel OLE objects from DOORS into a new Excel spreadsheet

Hello,

I've been trying to figure this out for a while now, but I am stuck. We're trying to extract all of our Excel-type OLE objects from DOORS and place them into a new workbook. I'm not a DXL whiz by any stretch of the imagination, so please bear with me.

I have the code to iterate through the module and find any Excel OLEs. I can also open them up, but I don't know how to copy the data from one workbook to another.

Most of this code is stitched together from various forum threads. I can't really take credit for most of it.

This is where I try to initialize a new workbook: 

#include <utils/ole.inc>
 
// Define OLE Objects
OleAutoObj Excel = null
OleAutoObj Workbooks = null
OleAutoObj Workbook = null
OleAutoObj Worksheets = null
OleAutoObj Worksheet = null
OleAutoObj Selection = null
OleAutoArgs oleArgs = create
 
// Initialize Excel application
if(null Excel)
{
    Excel = oleCreateAutoObject("Excel.Application")
        if(null Excel)
        {
                infoBox "Unable to start the Excel application. Please contact the DOORS administrator."
                halt
        }
}
 
// Add a blank worksheet to Excel.
olePut(Excel, "Visible", true)
oleGet(Excel, "Workbooks", Workbooks)
oleMethod(Workbooks, "Add")
oleGet(Excel, "ActiveWorkbook", Workbook)
// Get a handle on sheet 1 in the workbook.
clear oleArgs
put(oleArgs, 1)
oleGet(Workbook, "Sheets", oleArgs, Worksheet)
oleMethod(Worksheet, "Add")
clear oleArgs

 


This is the function that determines the type of OLE in object o:

 

  

OleAutoObj findOleType(Object o, OleAutoObj WB)
{    
        OleAutoObj objApp = null, objWorkbook, objSelection, objSheet
        
        bool bIsStatic
    bool bFoundKeyword = false
    bool bIsActivated = false
    string sErr, sName
    
        string reHeader = "{\\\\object\\\\objemb{\\\\\\*\\\\objclass ([^}]+)}" 
        Regexp reOle = regexp reHeader 
        Buffer b = create() 
        b = richTextWithOle o."Object Text"
        string str = richTextWithOle o."Object Text"
        
        string sub = "Excel"
        int offset = null, length = null
        bool matchCase = false, reverse = false, isExcel = false
 
        if (reOle b) 
        { 
                val = b[match 1]             
                if (findPlainText( val, "Excel", offset, length, matchCase, reverse))
                { 
 
                        oleActivate(o)
                        objApp = oleGetAutoObject(o)
                        if(null objApp)
                        {
                                print "null\n"
                        }
                        else 
                        {
                                //objApp = oleGetAutoObject(o)
                                olePut(objApp, "Visible", true)
                                oleGet(objApp, "Application", objWorkbooks)
                                //oleMethod(WB, "Add")
                                oleGet(objApp, "ActiveWorkbook", objWorkbook)
                                // Get a handle on sheet 1 in the workbook.
                                clear oleArgs
                                put(oleArgs, 1)
                                oleGet(objApp, "ActiveSheet", oleArgs, objWorksheet)
                                oleMethod(objWorksheet, "Copy", oleArgs, WB)
                                //showOlePropertiesDialog(o)
                                //return objSelection   
                        }
                                
                                oleCloseAutoObject(objApp)
                                oleCloseAutoObject(objWorksheets)
                                oleCloseAutoObject(objWorkheet)
                }
        } 
        
return null
}



Here's the part that iterates through the module, and does the garbage collection.



 

  

for obj in current Module do {
    findOleType(obj, Excel)     
}
 
oleCloseAutoObject(Excel)
oleCloseAutoObject(Workbooks)
oleCloseAutoObject(Workbook)
oleCloseAutoObject(Worksheets)
oleCloseAutoObject(Workheet)
oleCloseAutoObject(Selection)
oleCloseAutoObject(objCell)


Any help at all would be nice. Sorry for the messy code.

~John

 


SystemAdmin - Fri Jul 20 15:57:42 EDT 2012

Re: Copy / Paste Excel OLE objects from DOORS into a new Excel spreadsheet
Mathias Mamsch - Fri Aug 03 07:14:28 EDT 2012

What you want to use is the Copy/Move methods of Worksheet as described here:

http://support.microsoft.com/kb/288402/en-us
Therefore I would replace your line:
  

...
oleMethod(objWorksheet, "Copy", oleArgs, WB)
...

 


by something like:

 

 

  

clear oleArgs
put(oleArgs, "After", oleWorksheetInWB)
oleMethod(objWorksheet, "Copy", oleArgs)



where oleWorksheetInWB must be a worksheet in the destination Workbook WB, like the one you add in this code:



 

 

 

  

...
oleGet(Workbook, "Sheets", oleArgs, Worksheet)
 
// Get one Sheet to the workbook
clear oleArgs
OleAutoObj oleWorksheetInWB
oleMethod(Worksheet, "Add", oleArgs, oleWorksheetInWB)
...



I did not test that code, so I hope that helps, regards, Mathias



 

 

 

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

 

Re: Copy / Paste Excel OLE objects from DOORS into a new Excel spreadsheet
Estebell - Fri Sep 18 09:27:53 EDT 2015

Mathias Mamsch - Fri Aug 03 07:14:28 EDT 2012

What you want to use is the Copy/Move methods of Worksheet as described here:

http://support.microsoft.com/kb/288402/en-us
Therefore I would replace your line:
  

...
oleMethod(objWorksheet, "Copy", oleArgs, WB)
...

 


by something like:

 

 

  

clear oleArgs
put(oleArgs, "After", oleWorksheetInWB)
oleMethod(objWorksheet, "Copy", oleArgs)



where oleWorksheetInWB must be a worksheet in the destination Workbook WB, like the one you add in this code:



 

 

 

  

...
oleGet(Workbook, "Sheets", oleArgs, Worksheet)
 
// Get one Sheet to the workbook
clear oleArgs
OleAutoObj oleWorksheetInWB
oleMethod(Worksheet, "Add", oleArgs, oleWorksheetInWB)
...



I did not test that code, so I hope that helps, regards, Mathias



 

 

 

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

 

Hello, 

I try to add a new sheet after the last one but my code doesn't run well.

Here is my code (inspired from your's) 

// Called functions :

pragma runLim, 0

#include <utils/ole.inc>

OleAutoObj objExcel = null
OleAutoObj objWorkbooks = null
OleAutoObj objWorkbook = null
OleAutoObj objSheet = null
OleAutoArgs args   = create
//-------------------------------------------------------------------
/***********************************
        excelGetSheetOLE
***********************************/
OleAutoObj excelGetSheetOLE( OleAutoObj objExcelWorkbook, int sheetNumber ) {
   OleAutoObj objExcelSheet
   clear(args)
   put( args, sheetNumber )
   checkRes( oleGet( objExcelWorkbook, "Sheets", args, objExcelSheet ) )
   return objExcelSheet
}
/***********************
   connectToApp
***********************/
OleAutoObj connectToApp(string appObj, appName) {
    OleAutoObj obj = null

    obj = oleCreateAutoObject(appObj)
    if (null obj) {
        ack dxlStrformat(LS_("String_Unable_to_communicate_with_sn_Is_it_installed",NLSTEMP_("Unable to communicate with %s\nIs it installed?")), appName)
    }
    return obj
}
/***********************
   makeVisible
***********************/
void makeVisible(OleAutoObj o) {
    bool        bIsVisible = false
    checkRes(oleGet(o, cPropertyVisible, bIsVisible))
    if (!bIsVisible) {
        checkRes(olePut(o,cPropertyVisible, true))
    }
}
/***********************
   excelInit
***********************/
bool excelInit() {
    objExcel = connectToApp(cObjExcelApplication, (NLS_("Excel")))
    if (null objExcel) return false
    makeVisible objExcel
    checkRes(oleGet(objExcel,cPropertyWorkbooks,objWorkbooks))
    if (null objWorkbooks) {
        ack getExtMsg("String_Unable_to_get_workbooks_collection",NLSTEMP_("Unable to get workbooks collection"))
        return false
    }
        checkRes(oleMethod(objWorkbooks, cMethodAdd))
    checkRes(oleGet(objExcel,cPropertyActiveWorkbook,objWorkbook))
    if (null objWorkbook) {
        ack getExtMsg("String_Unable_to_get_active_workbook",NLSTEMP_("Unable to get active workbook"))
        return false
    }

    checkRes(oleGet(objWorkbook,cPropertyActiveSheet,objSheet))
    if (null objSheet) {
        ack getExtMsg("String_Unable_to_get_active_sheet",NLSTEMP_("Unable to get active sheet"))
        return false
    }
    return true    
}

Here is my main code : 

if(excelInit == true){
        Module m = current Module
        string nomVue
        int nFeuille = 1
        OleAutoObj objCount = null
        OleAutoObj objSheets = null
        OleAutoObj oleWorksheetInWB
        OleAutoObj objFooter = null
        OleAutoObj objAfter = null
        for nomVue in views (m) do {
                objSheet = excelGetSheetOLE (objWorkbook, nFeuille)
                
                // Excel has 3 sheets 
                if (nFeuille > 3){

                        checkRes(oleGet(objWorkbook, "Sheet", objSheet))
                        
                        clear args
                        put (args, nFeuille)
                        checkRes(oleGet(objWorkbook,cPropertyActiveSheet,args,oleWorksheetInWB))
                
                        clear args
                        put(args, "After", oleWorksheetInWB)
                        checkRes(oleMethod(objSheet, "Add", args)) //this line is wrong ???

                        clear args
                }
                        checkRes(oleMethod(objSheet, cMethodSelect))
                        checkRes(oleGet(objWorkbook,cPropertyActiveSheet,objSheet))
                        nFeuille ++ 

        }
}
else halt

 

Re: Copy / Paste Excel OLE objects from DOORS into a new Excel spreadsheet
Mathias Mamsch - Mon Sep 21 04:56:56 EDT 2015

Estebell - Fri Sep 18 09:27:53 EDT 2015

Hello, 

I try to add a new sheet after the last one but my code doesn't run well.

Here is my code (inspired from your's) 

// Called functions :

pragma runLim, 0

#include <utils/ole.inc>

OleAutoObj objExcel = null
OleAutoObj objWorkbooks = null
OleAutoObj objWorkbook = null
OleAutoObj objSheet = null
OleAutoArgs args   = create
//-------------------------------------------------------------------
/***********************************
        excelGetSheetOLE
***********************************/
OleAutoObj excelGetSheetOLE( OleAutoObj objExcelWorkbook, int sheetNumber ) {
   OleAutoObj objExcelSheet
   clear(args)
   put( args, sheetNumber )
   checkRes( oleGet( objExcelWorkbook, "Sheets", args, objExcelSheet ) )
   return objExcelSheet
}
/***********************
   connectToApp
***********************/
OleAutoObj connectToApp(string appObj, appName) {
    OleAutoObj obj = null

    obj = oleCreateAutoObject(appObj)
    if (null obj) {
        ack dxlStrformat(LS_("String_Unable_to_communicate_with_sn_Is_it_installed",NLSTEMP_("Unable to communicate with %s\nIs it installed?")), appName)
    }
    return obj
}
/***********************
   makeVisible
***********************/
void makeVisible(OleAutoObj o) {
    bool        bIsVisible = false
    checkRes(oleGet(o, cPropertyVisible, bIsVisible))
    if (!bIsVisible) {
        checkRes(olePut(o,cPropertyVisible, true))
    }
}
/***********************
   excelInit
***********************/
bool excelInit() {
    objExcel = connectToApp(cObjExcelApplication, (NLS_("Excel")))
    if (null objExcel) return false
    makeVisible objExcel
    checkRes(oleGet(objExcel,cPropertyWorkbooks,objWorkbooks))
    if (null objWorkbooks) {
        ack getExtMsg("String_Unable_to_get_workbooks_collection",NLSTEMP_("Unable to get workbooks collection"))
        return false
    }
        checkRes(oleMethod(objWorkbooks, cMethodAdd))
    checkRes(oleGet(objExcel,cPropertyActiveWorkbook,objWorkbook))
    if (null objWorkbook) {
        ack getExtMsg("String_Unable_to_get_active_workbook",NLSTEMP_("Unable to get active workbook"))
        return false
    }

    checkRes(oleGet(objWorkbook,cPropertyActiveSheet,objSheet))
    if (null objSheet) {
        ack getExtMsg("String_Unable_to_get_active_sheet",NLSTEMP_("Unable to get active sheet"))
        return false
    }
    return true    
}

Here is my main code : 

if(excelInit == true){
        Module m = current Module
        string nomVue
        int nFeuille = 1
        OleAutoObj objCount = null
        OleAutoObj objSheets = null
        OleAutoObj oleWorksheetInWB
        OleAutoObj objFooter = null
        OleAutoObj objAfter = null
        for nomVue in views (m) do {
                objSheet = excelGetSheetOLE (objWorkbook, nFeuille)
                
                // Excel has 3 sheets 
                if (nFeuille > 3){

                        checkRes(oleGet(objWorkbook, "Sheet", objSheet))
                        
                        clear args
                        put (args, nFeuille)
                        checkRes(oleGet(objWorkbook,cPropertyActiveSheet,args,oleWorksheetInWB))
                
                        clear args
                        put(args, "After", oleWorksheetInWB)
                        checkRes(oleMethod(objSheet, "Add", args)) //this line is wrong ???

                        clear args
                }
                        checkRes(oleMethod(objSheet, cMethodSelect))
                        checkRes(oleGet(objWorkbook,cPropertyActiveSheet,objSheet))
                        nFeuille ++ 

        }
}
else halt

 

I think you will need to access the Worksheets collection and call "add" on it. Not ActiveSheet.add ...

It might help if you look at a VBA example and then translate to DXL. Regards, Mathias

Re: Copy / Paste Excel OLE objects from DOORS into a new Excel spreadsheet
Estebell - Mon Sep 21 05:34:10 EDT 2015

I try to correct my mistakes but it still does not work ...

In VBA it would be : Sheets.Add   After:=Sheets(Sheets.Count) 

if (nFeuille > 3){
                        int feuille = nFeuille-1
                        objSheet = excelGetSheetOLE (objWorkbook, feuille)
                        //I take the last sheet present on my worksheets
                                        
                        clear args
                        put(args, "After", objSheet)
                        // I put "After argument to prepare the "Add function"
                        checkRes(oleMethod(objWorkbook, "Add", args))
                        // This line does not add any new sheet !
                        clear args
                }

So I dont understand where it is faulty !!!

Re: Copy / Paste Excel OLE objects from DOORS into a new Excel spreadsheet
Mathias Mamsch - Mon Sep 21 06:34:10 EDT 2015

Estebell - Mon Sep 21 05:34:10 EDT 2015

I try to correct my mistakes but it still does not work ...

In VBA it would be : Sheets.Add   After:=Sheets(Sheets.Count) 

if (nFeuille > 3){
                        int feuille = nFeuille-1
                        objSheet = excelGetSheetOLE (objWorkbook, feuille)
                        //I take the last sheet present on my worksheets
                                        
                        clear args
                        put(args, "After", objSheet)
                        // I put "After argument to prepare the "Add function"
                        checkRes(oleMethod(objWorkbook, "Add", args))
                        // This line does not add any new sheet !
                        clear args
                }

So I dont understand where it is faulty !!!

You are using "Workbook.Add" instead of "Sheets.Add".

You need to be a bit more precise when translating your VBA to DXL. The first thing you need to know, when trying to translate VBA code to DXL is, that VBA uses the "Application" object as a default object for resolving properties. I.e. when you encounter code like this:  

ActiveSheet...
Sheets...

That this translates to:  

Application.ActiveSheet...
Application.Sheets...

In DXL your "Application" object translates to the "Excel.Application" instance you created using COM. Ok, so far so good. So the next thing, is that you need to understand, that there are multiple ways to create Worksheets in Excel. Look at the Excel Reference: 

https://msdn.microsoft.com/EN-US/library/office/ff194068.aspx

Here you can read on the properites of the Application Object that the "Application.Sheets" object is simply are shortcut for "Application.ActiveWorkbook.Sheets".  And you can see, that Sheets is a property (not a method), which means you need to access it using oleGet. If you follow the link to the Sheets collection and look at the methods, then you will see, that there is in fact two methods to add a sheet. "Add" and "Add2" ... So choose one and you will be fine. 

So for DXL your path is: 

Application.Workbooks.Add

Workbook = Application.ActiveWorkbook

Sheets = Workbook.Sheets

Sheets.Add ...

Sheet = Workbook.ActiveSheet

 

And of course you can try to use the shortcuts on the application object (Application.ActiveWorkbook.ActiveSheet = Application.ActiveSheet). Just not try to get confused and try (Workbooks.Add) or something. 

 

Regards, Mathias

Generic Remark: