Add a new sheet on Excel with OLE automations

Hello,

For now 2 days, I try to add a new sheet on Excel from DOORS OLE Automations.

The more I modified my code the more I found some mistakes. So I stopped it and begin again from 0 and read again the DXL Reference Manual.

I know that I was mistaken about OleAutoObj...

So I try to run the exemple on DXL Reference Manual but it doesn't work !!!!!

#include <utils/ole.inc>

OleAutoObj objExcel = oleCreateAutoObject("Excel.Application")
OleAutoObj objWorkbooks
OleAutoObj objCharts
OleAutoObj objChart
OleAutoObj objActiveChart
OleAutoObj objSpreadSheet
OleAutoObj objRange
OleAutoArgs autoArgs = create
bool excelVisible = false
string result

string SetExcelCell(OleAutoObj objSheet,int xCellLoc,int yCellLoc,string property,string value)
{
OleAutoObj objCell = null
OleAutoArgs autoArgs = create
string result = null
put(autoArgs, yCellLoc)
put(autoArgs, xCellLoc)
result = oleMethod(objSheet,"Cells",autoArgs, objCell)
if (result == null){
        OleAutoObj objInterior = null
        result = olePut(objCell, property, value)
}
return result
} /* SetExcelCell */

/* Make Excel visible to the user */
oleGet(objExcel, "Visible", excelVisible)
if (!excelVisible) olePut(objExcel,"Visible", true)
/* Add new workbook */
oleGet(objExcel,"Workbooks", objWorkbooks)
oleMethod(objWorkbooks,"Add")

put(autoArgs, "Sheet1")
/* Add new worksheet and activate it */
oleMethod(objWorkbooks,"Sheets", objSpreadSheet)
oleMethod(objSpreadSheet, "Activate")
/* Add some data to the spreadsheet */
for (i = 1; i < 8; i++){
        for (j = 1; j < 8; j++){
                string value = ( ( 10*i) + j ) ""
                SetExcelCell(objSpreadSheet, i, j,"Value", value)
        }
}
clear(autoArgs)
/* Selected the data that has been entered */
put(autoArgs, "a1:h8")
oleMethod (objSpreadSheet, "Range", autoArgs,objRange)
oleMethod (objRange, "Select")
clear(autoArgs)
/* Create a chart object */
put(autoArgs, "Chart1")
result = oleGet(objSpreadSheet,"ChartObjects",objCharts)
if (result != null) print result "\n"
clear(autoArgs)
/* Define the size and location of the new chart */
put(autoArgs, 50)
put(autoArgs, 80)
put(autoArgs, 400)
put(autoArgs, 200)
oleMethod(objCharts,"Add", autoArgs, objChart)
oleMethod(objChart, "Activate")
oleGet(objExcel, "ActiveChart", objActiveChart)
clear(autoArgs)
/* Use named arguments this time round - then we
don't have to fill them all in
*/
put(autoArgs, "source", objRange)
put(autoArgs, "gallery", -4100)
put(autoArgs, "format", 5)
put(autoArgs,"categoryLabels", 2)
put(autoArgs,"seriesLabels",2)
put(autoArgs,"HasLegend",true)
result = oleMethod(objActiveChart, "ChartWizard", autoArgs)
if (result != null) print result "\n"

On line 40, I think I would replace "Activate" by "Add" but that is no more successful....

Please can someone give me the right line to add a new sheet ?

Re: Add a new sheet on Excel with OLE automations
Mathias Mamsch - Mon Sep 21 10:30:42 EDT 2015

Ok. It seems you don't seem to like explanations a lot - Here you are: 

void oleCheck (string s) { 
   if (!null s) {
      print "An Error occurred: " s
      halt;
   }
}

OleAutoObj objExcel = oleCreateAutoObject("Excel.Application")

// Make it visible
olePut(objExcel, "Visible", true)

OleAutoArgs args = create(); 

// get Application.Workbooks
OleAutoObj objWorkbookCollection= null; 
oleCheck oleGet ( objExcel, "Workbooks", objWorkbookCollection)

// Add a workbook --> Application.Workbooks.add ()
oleCheck oleMethod(objWorkbookCollection, "Add")

// Get a handle to the newly added workbook --> Application.ActiveWorkbook
OleAutoObj objWorkbook = null
oleCheck oleGet (objExcel, "ActiveWorkbook", objWorkbook)

// Get a handle to the Sheets collection --> ActiveWorkbook.Sheets
OleAutoObj objSheetsCollection = null
oleCheck oleGet (objExcel, "Sheets", objSheetsCollection )

// Add a sheet ... ActiveWorkbook.Sheets.Add
oleCheck oleMethod(objSheetsCollection , "Add") 

// Get a handle to the sheet ...
OleAutoObj objSheet = null
oleCheck oleGet (objWorkbook, "ActiveSheet", objSheet)

// Get the Sheets Name
string sSheetName = null; 
oleCheck oleGet (objSheet, "name", sSheetName)

print "New Sheet with name " sSheetName " added!"

Hope this helps, regards, Mathias

Re: Add a new sheet on Excel with OLE automations
Estebell - Mon Sep 21 11:26:02 EDT 2015

Well I'm sorry having spent your time, but 

I searched in the forum a topic about my problem first and I have found one,

I replied on it because I didn't understand the way to correct my code.

Then, you told me to open a new topic, so I've done it. 

For your information, I have spent 1 week before posting in this forum to have some help

Your answer was that 

Workbook = Application.ActiveWorkbook

Sheets = Workbook.Sheets

So as Workbook = Application.ActiveWorkbook is get by "oleCheck oleGet (objExcel, "ActiveWorkbook", objWorkbook)"

I thought that Sheets = Workbook.Sheets would have been "oleCheck oleGet (objWorkbook, "Sheets", objWorksheet)", but it did not.

That's why after some hours to try to have the good line, I asked for because I was completely wrong !!!!!

At last, I would want to post a thanks later, after my meeting, but instead I answer your post.

So thank you very mutch to have answered me a lot of time this day, I hope that Ole Automation will not be a problem now by inspiring me from some examples.

Estebell

Re: Add a new sheet on Excel with OLE automations
Mathias Mamsch - Mon Sep 21 11:47:58 EDT 2015

Sorry - did not want to be nit-picking - but if you checkout the code you posted, it was 

    ...
    checkRes(oleMethod(

objWorkbooks, cMethodAdd))
    ...

and the relevant part of my answer (first sentence was): You are using "Workbook.Add" instead of "Sheets.Add"

On the original post, in the question of SystemAdmin there already was the working code for adding a worksheet and on that other post you could also find the code. I figured there might be some confusion about the Automation, thats why I hinted to MSDN and gave some extra information. I found it weird that all of this did not seem to solve your problem. Anyway ... Hope you will be making good progress from now ;-) Regards, Mathias

Re: Add a new sheet on Excel with OLE automations
Christopher Cote - Thu May 16 11:47:04 EDT 2019

I know this was written about 4 years ago, but I have a quick question about the code that Matthias wrote... How does the oleCheck method work?  Is it just an error-checking function so that you don't get run-time errors?  I haven't been able to find this function in my reference manual.

Chris

Re: Add a new sheet on Excel with OLE automations
Mike.Scharnow - Thu May 16 12:51:57 EDT 2019

oleCheck is self written function, it is located at the top of the code, lines 1-6.

if no error occurs, the built-in ole functions (oleGet, oleMethod, ...) return nothing.If something went wrong, they return an error message as a string. oleCheck will ensure that these result messages will not be overlooked silently but that they will RAISE a run time error.

In your own code you might want to use a different check function that creates entries in a log file or do something else.