Error Leaving Excel Automation

[Alvaro]

I am using Excel "Sort Method" with excel 2000...

chworksheet:range("1:30"):sort....

I always get an error message when leaving excel.

If i don`t use this method i don't get the error message when leaving excel.

Could anyone please help me?

 

[Andrew Price]

Sorting in Excel

What is the error message?

I have performed a sort in excel 2000 using the following code example:

CHWORKSHEET:Range("A2:H4"):Sort(
CHWORKSHEET:Range("B2"), /* Key1 */
1, /* Order1 */
, /* Type1 */
CHWORKSHEET:Range("A2"), /* Key2 */
1, /* Order2 */
, /* Type2 */
, /* Key3 */
, /* Order3 */
, /* Type3*/
,
,

).

/* exit excel code: */

RELEASE OBJECT chWorkSheet NO-ERROR.
RELEASE OBJECT chWorkBook NO-ERROR.
chExcelApplication:Quit().

RELEASE OBJECT chExcelApplication NO-ERROR.

/* code end. */

I do not recieve any errors using this method.

 

[Alvaro]

Still the error message

Hi,

First of all thanks for your help. I have tried your source code and still get the same error message...

This message appears when i close excel.. and tells:

"The Program has done an invalid operation and will close"

if i don`t use the sort method i don`t get the message..

Thanks very much,

Alvaro.

 

[Andrew Price]

perhaps this will help...

I am not sure what the problem is... if the error is an excel error you could try writing this line under your create excelapplication code line:

chExcelApplicationisplayAlerts = FALSE.

It turns off excels alerts, i use it when saving my files in excel from progress.

Apart from that I cant reproduce the error my self and so am a bit stuck as to what might be wrong.

Hope this helps

 

[Alvaro]

Still Nothing...

Thanks for your help again Andrew !!!

It didn´t work too... I think that it's a Windows error Code not an Excel code because it appears after closing excel... It's like when you don`t free a Com-Handle...

Here you have a sample of my code... It could help you to find the problem

&SCOPED-DEFINE xlGues 0
&SCOPED-DEFINE xlAscending 1
&SCOPED-DEFINE xlAscending 1
&SCOPED-DEFINE xlPinYin 1

chworksheet:range("3:300"):Sort (chworksheet:Range("G1"), {&xlAscending}, chworksheet:Range("I1"),,{&xlAscending}, chworksheet:Range("D1"), {&xlAscending}, {&xlNo}, 1, False, {&xlTopToBottom},).


/*CHAPPLICATION:QUIT NO-ERROR.*/
release object chworkbook no-error.
release object chworksheet no-error.
release object chapplication no-error.

Best Regards Andrew,

Alvaro.

 

[Andrew Price]

Code example that works...

I put your code into one of my old programmes... I changed it a bit to fit in with my data that i upload from a text file using the variable txtfile. The code worked fine so I have cut it out and pasted it here:

/* ------ Start ---- */

DEF VAR chExcelApplication AS COM-HANDLE NO-UNDO.
DEF VAR chWorkbook AS COM-HANDLE NO-UNDO.
DEF VAR chWorkSheet AS COM-HANDLE NO-UNDO.
&SCOPED-DEFINE xlGues 0
&SCOPED-DEFINE xlAscending 1
&SCOPED-DEFINE xlAscending 1
&SCOPED-DEFINE xlPinYin 1


CREATE "Excel.Application" chExcelApplication.
chExcelApplicationisplayAlerts = FALSE.

ASSIGN chExcelApplication:Visible = false.

chExcelApplication:Workbooks:OpenText(txtFile,3,,,,,TRUE).
chWorkSheet = chExcelApplication:Sheets:Item(1).
chWorkSheet:Range("A1:H1"):Font:Bold = TRUE.
chworksheet:range("3:300"):Sort (chworksheet:Range("B1"),
1,
chworksheet:Range("C1"),
,
1, chworksheet:Range("D1"),
1, {&xlNo}, 1, False, {&xlTopToBottom},).

RELEASE OBJECT chWorkSheet NO-ERROR.
RELEASE OBJECT chWorkBook NO-ERROR.
chExcelApplication:Quit().
RELEASE OBJECT chExcelApplication NO-ERROR.

/* ---- END ------ */

If this doesn't work perhaps you can tell me what other procedures you are running. Are you using any other ole commands perhaps? or OCX files?

I have had a conflict with an OCX file before that caused windows errors although exactly what it says I can`t tell you as I have a German version!

Andrew

 

[Alvaro]

Still The Error Message

Hi Andrew,

The Error message still appear when leaving Excel... I am not using any Ocx. I don´t know what could be the proble...

Here you have my source code to see if there is anything wrong...

This is Mfg/Pro code:

/* xxcamt.p - Programa Estadisticas Mantenimiento de Llamadas */
/* */
/* ARO. SEPTIEMBRE DEL 2000 */
/* */
/* */
/*V8:ConvertMode=Report */


/* Mfdtitle.i Define Strip Menu y variables globales */


/* Definicion de variables del programa */

def var fechi as CHAR.

define variable chapplication as com-handle.
define variable chworkbook as com-handle.
define variable chworksheet as com-handle.
define variable fila as integer.
define variable celda as char.
define variable orden as decimal.

/* PROCEDIMIENTOS */
procedure inicia_Excel:

create "Excel.APplication" chapplication. /* Creación de la hoja Excell */
chapplication:windowstate=-4137. /* Maximizado */
chapplication:visible = true. /* Visualización de la Hoja */
chworkbook=chapplication:workbooks:add(). /* Creación del Libro */
chworksheet = chapplication:sheets:item(1). /* Hoja del Libro en la que se coloca */

chworksheet:range("A1:L2"):interior:colorindex = 50. /* Pone el fondo en Verde */
chworksheet:range("A1:L2"):font:colorindex = 2. /* Pone la letra en Blanco */

chworksheet:range("A1:L2"):font:name = "Comic Sans Ms". /* Tipo de letra */
chworksheet:range("A1:L1"):font:size = 18. /* Tamaño de la letra */
chworksheet:range("G1"):font:size = 12. /* Tamaño de la letra PARA LA FECHA*/

chworksheet:range("A2:L2"):font:size = 12. /* Tamaño de la letra */
chworksheet:range("A1:L2"):font:shadow = true. /* Sombra */
chworksheet:range("A1:L2"):font:BOLD = true. /* Negrita */

chworksheet:range("c1"):font:SHADOW = true. /* Negrita */
chworksheet:range("c1"):font:BOLD = true. /* Negrita */
chworksheet:range("c1"):font:COLORINDEX = 4. /* COLOR ROJO */


chworksheet:range("A:A"):Columnwidth = 23. /* Anchura de la celda */
chworksheet:range("B:B"):Columnwidth = 10.
chworksheet:range("C:C"):Columnwidth = 10.
chworksheet:range("D"):Columnwidth = 15.
chworksheet:range("E:E"):Columnwidth = 20.
chworksheet:range("F:F"):Columnwidth = 20.
chworksheet:range("G:G"):Columnwidth = 10.
chworksheet:range("H:H"):Columnwidth = 40.
chworksheet:range("I:I"):Columnwidth = 30.
chworksheet:range("J:J"):Columnwidth = 40.
chworksheet:range("J:J"):Columnwidth = 40.
chworksheet:range("K:K"):Columnwidth = 22.
chworksheet:range("L:L"):Columnwidth = 60.


chworksheet:range("A:A"):HorizontalAlignment = 3. /* Alineado a la centrado "" */
chworksheet:range("B:B"):HorizontalAlignment = 3. /* Alineado a la Izda */
chworksheet:range("C:C"):HorizontalAlignment = 3. /* Alineado a la Izda */
chworksheet:range("D"):HorizontalAlignment = 3. /* Alineado a la Izda */
chworksheet:range("E:E"):HorizontalAlignment = 3. /* Alineado a la Izda */
chworksheet:range("F:F"):HorizontalAlignment = 3. /* Alineado a la Izda */
chworksheet:range("G:G"):HorizontalAlignment = 3. /* Alineado a la Izda */
chworksheet:range("H:H"):HorizontalAlignment = 3. /* Alineado a la Izda */
chworksheet:range("I:I"):HorizontalAlignment = 3. /* Alineado a la Izda */
chworksheet:range("J:J"):HorizontalAlignment = 3. /* Alineado a la Izda */
chworksheet:range("K:K"):HorizontalAlignment = 3. /* Alineado a la Izda */
chworksheet:range("L:L"):HorizontalAlignment = 3. /* Alineado a la Izda */

chworksheetictures:insert("\\egep\mfg\devdlr\gui\cs\src\DLR.BMP"). /* Logo DLR */
chworksheetictures:height = 30. /* Altura en pixels del logo */
chworksheetictures:width = 40. /* Ancho en pixels del logo */

fechi = string (today).

chworksheet:range("c1") = "Listado de Llamadas Pendientes". /**** CABECERAS ****/
chworksheet:range("G1") = fechi.
chworksheet:range("A2") = "Número de Llamada".
chworksheet:range("B2") = "Estado".
chworksheet:range("C2") = "Cola".
chworksheet:range("D2") = "Usuario Final".
chworksheet:range("E2") = "Fecha Llamada".
chworksheet:range("F2") = "Hora Llamada".
chworksheet:range("G2") = "Zona".
chworksheet:range("H2") = "Dirección".
chworksheet:range("I2") = "Ciudad".
chworksheet:range("J2") = "Alias".
chworksheet:range("k2") = "Código de Trabajo".
chworksheet:range("L2") = "Comentario".

chworkSheetageSetuprintTitleRows = "$1:$2". /* Las dos primeras filas como cabecera para todas las páginas */
chworkSheetageSetuprientation = 2. /* Horientación Vertical */
chworkSheetageSetup:ZOOM = FALSE. /* Desactiva el zoom */
chworkSheetageSetup:fittopagesWIDE = 1. /* Páginas de ancho */
ChworkSheetageSetup:fittopagesTALL = 100. /* Páginas de alto */

fila = 3. /* Fila en la que empezamos a meter datos */


End procedure.

procedure Carga_Datos:

FOR EACH CA_MSTR WHERE CA_STATUS="NUEVA" AND CA_QUE = "GENERAL" AND CA_INT_TYPE = "AVERIA" AND CA_NBR <> "LL11275" AND CA_NBR <> "LL13176"
AND CA_NBR <> "LL13375" AND CA_NBR <> "LL14225" AND CA_NBR <> "LL14570" AND CA_NBR <> "LL15578" AND CA_NBR <> "LL6154" AND CA_NBR <> "LL6157" AND CA_NBR <> "LL9923" NO-LOCK:


find first eu_mstr where ca_eu_nbr = eu_addr no-lock.

find first eud_det where EU_ADDR = eud_addr no-lock.

find first tbl_mstr where /*TBL_PARTYPE = "SERVICEAREA" AND*/ TBL_COMPCODE = CA_AREA no-lock no-error.

for each eu_mstr where eu_addr = ca_eu_nbr no-lock, each ad_mstr where eu_addr = ad_addr /*and ad_type = "enduser"*/ no-lock:


/* EUD_SORT "" EN ALGUNOS END USERS * TB CA_AREA = "" EN ALGUNAS LLAMADAS */

IF AVAILABLE TBL_MSTR THEN DO:

celda = "A" + string(fila).
chworksheet:range(celda) = ca_nbr.
celda = "B" + string(fila).
chworksheet:range(celda) = ca_status.
celda = "C" + string(fila).
chworksheet:range(celda) = ca_que.
celda = "e" + string(fila).
chworksheet:range(celda) = ca_opn_date.
celda = "f" + string(fila).
chworksheet:range(celda) = ca_opn_time.
celda = "g" + string(fila).
chworksheet:range(celda) = tbl_parcode.
celda = "h" + string(fila).
chworksheet:range(celda) = eud_sort.
celda = "i" + string(fila).
chworksheet:range(celda) = ad_city.
celda = "j" + string(fila).
chworksheet:range(celda) = eu_sort.
celda = "k" + string(fila).
chworksheet:range(celda) = ca_int_type.
celda = "l" + string(fila).
chworksheet:range(celda) = ca_desc.
celda = "D" + string(fila).
chworksheet:range(celda) = ca_eu_nbr.

fila = fila + 1.

end.

else do:

celda = "A" + string(fila).
chworksheet:range(celda) = ca_nbr.
celda = "B" + string(fila).
chworksheet:range(celda) = ca_status.
celda = "C" + string(fila).
chworksheet:range(celda) = ca_que.
celda = "e" + string(fila).
chworksheet:range(celda) = ca_opn_date.
celda = "f" + string(fila).
chworksheet:range(celda) = ca_opn_time.
celda = "g" + string(fila).
chworksheet:range(celda) = "".
celda = "h" + string(fila).
chworksheet:range(celda) = eud_sort.
celda = "i" + string(fila).
chworksheet:range(celda) = "ad_city".
celda = "j" + string(fila).
chworksheet:range(celda) = "eu_sort".
celda = "k" + string(fila).
chworksheet:range(celda) = "ca_int_type".
celda = "l" + string(fila).
chworksheet:range(celda) = "ca_desc".
celda = "D" + string(fila).
chworksheet:range(celda) = "ca_eu_nbr".

end.

end. /* del for each */
end.

/* Ordeno */

&SCOPED-DEFINE xlGues 0
&SCOPED-DEFINE xlAscending 1
&SCOPED-DEFINE xlAscending 1
&SCOPED-DEFINE xlPinYin 1

chworksheet:range("3:300"):Sort (chworksheet:Range("G1"), {&xlAscending}, chworksheet:Range("I1"),,{&xlAscending}, chworksheet:Range("D1"), {&xlAscending}, {&xlNo}, 1, False, {&xlTopToBottom},).

end procedure.


Procedure cierra_excel:

/*CHAPPLICATION:QUIT NO-ERROR.*/
release object chworkbook no-error.
release object chworksheet no-error.
release object chapplication no-error.

end procedure.


RUN Inicia_Excel.
Run Carga_Datos.
Run Cierra_Excel.

Thanks for all.

Alvaro

 

[Andrew Price]

Think i solved it!!!

I managed to manipulate your code to get it running without mfg/PRO and got the same problem...

I played a bit and then realised that you haven't closed your workbook. The workbook can not of course remain open when excel is closed and therefore you get an error! The correct code to end excel in your program is:


chWorkBook:Close().

release object chworksheet no-error.
release object chworkbook no-error.

chApplication:Quit().
release object chapplication no-error.

 

[Alvaro]

Now it Works !!!

Hi Andrew,

Thanks very much for your help !!! Now it works !!

I don´t belive i haven´t seen the problem before !!! It was trivial !!

I apologise for asking you for help all day.

If you ever need some help please contact me:

alvaro.rivero@es.delarue.com

Best Regards,

Alvaro.

 

[Unregistered]

When you are using the Excel object Range, you must declare explicitely a COM object of this type.

The problem is that Excel creates in memory an object of this type and when you live the Progress program this object is not released correctly.

Antonio Pérez
Barcelona(Spain)