Question Using class Microsoft.Office.Interop.Excel

Hello guys,

When I need to output some formatted data on excel I used the excel API that progress give us. But it's very slow.

Do find maybe a speed up way to do it by using the Microsoft.Office.Interop.Excel .

So I found some code on progress KB that was demonstrating the error you get when trying to use a range object (OE 10.2/11)
Code:
USING Microsoft.Office.Interop.Excel.* .
 
DEFINE VARIABLE objExcel    AS CLASS Microsoft.Office.Interop.Excel.Application.
DEFINE VARIABLE objWorkbook AS CLASS Workbook.
DEFINE VARIABLE objRange    AS Range.
DEFINE VARIABLE objSheet    AS CLASS WorksheetClass.
 
objExcel = NEW Microsoft.Office.Interop.Excel.ApplicationClass().
objWorkbook = objExcel:Workbooks:Add("").
objExcel:VISIBLE = TRUE.
objSheet = CAST(objWorkbook:ActiveSheet,WorkSheetClass).
objRange = objSheet:Range("A1", "A2").
But on my station the compiler is not able to found the class.

I'm on windows server 2012 with office 2016 and OE 10.2B08 (soon I will go to OE 11.7 and maybe OE 12).

Best Regards,

- BobyIsProgress -
 
thank you @Osborne .

It worked.

I'm struggling on how to use it now. I manage to open an excel file but I have some issue to get a cell of the first Sheet ^^.

If you have any tips or workaround :)

Best Regards,

- BobyIsProgress -
 

Cecil

19+ years progress programming and still learning.
An alternative is EPPlus. It can read and write to excel files and it does not require Microsoft Excel to be installed.
 

Osborne

Active Member
I'm struggling on how to use it now. I manage to open an excel file but I have some issue to get a cell of the first Sheet ^^.
I think maybe something along these lines:
Code:
objSheets = CAST(objWorkbook:Worksheets,Microsoft.Office.Interop.Excel.Worksheets).
objSheet = CAST(objSheets:Item[1],Microsoft.Office.Interop.Excel.WorksheetClass).
objSheet:Range["A1"]:Value2 = "Cell Value".
MESSAGE objSheet:Range["A1"]:Value2 VIEW-AS ALERT-BOX INFORMATION.
 
I think maybe something along these lines:
Code:
objSheets = CAST(objWorkbook:Worksheets,Microsoft.Office.Interop.Excel.Worksheets). 
objSheet = CAST(objSheets:Item[1],Microsoft.Office.Interop.Excel.WorksheetClass).
objSheet:Range["A1"]:Value2 = "Cell Value".
MESSAGE objSheet:Range["A1"]:Value2 VIEW-AS ALERT-BOX INFORMATION.
He says that he can't cast in objSheets .
Code:
 DEFINE VARIABLE objSheets AS Worksheets .
objSheets = CAST(objWorkbook:Worksheets,Microsoft.Office.Interop.Excel.Worksheets).
It's a bit weird .

I tried to adapt c# code into VB but it's very hard
 
I'm a bit surprised because when I tried to use the class:
USING System.IO.DirectoryInfo .
USING System.IO.FileInfo .

It work perfectly but not with this one
 
So,

There is some news on my project.
Because I'm running OE10.2B08, and the fact that OpenEdge just add the .net class interface in OE 10. it's possible that I will have more chance of success with OE 11.7 or 12 .

I will keep you updated.

Best Regards,

- BobyIsProgress -
 

Lechat

New Member
He says that he can't cast in objSheets .
Code:
 DEFINE VARIABLE objSheets AS Worksheets .
objSheets = CAST(objWorkbook:Worksheets,Microsoft.Office.Interop.Excel.Worksheets).
It's a bit weird .

I tried to adapt c# code into VB but it's very hard
You cannot use WorksheetClass as it is "Reserved for internal use ".
You can use:

Define variable oWorkbook as class Microsoft.Office.Interop.Excel.Workbook no-undo.
Define variable oWorksheet as class Microsoft.Office.Interop.Excel.Worksheet no-undo.
...
oWorkbook = oExcel:Workbooks:Open( NewFileName, ...
oWorksheet = cast (oWorkbook:Worksheets:Item[1], Worksheet).
...
 
Top