Inserting datas in Excel

Elite237

New Member
Hi guys. I have a little problem when I try to send data to Excel. At beginning I did a program, and declared global variables because I was calling to different procedures that were inside of my program .w and without problems, but now all these procedures I have to use them as .p.

The issue is that appears error messages:
component-handle referenced while not valid method / statement is processed: shapes

unable to establish com-handle property.

In each procedures I have declared:
DEFINE VARIABLE chExcel AS COM-HANDLE.
DEFINE VARIABLE chWorkbook AS COM-HANDLE.
DEFINE VARIABLE chWorksheet AS COM-HANDLE.

what I am doing wrong?

Thanks
 

Cringer

ProgressTalk.com Moderator
Staff member
Just defining the handles isn't enough. You need to initialise them.
 

Cringer

ProgressTalk.com Moderator
Staff member
You'll need to look at how you did that in the previous version that did work.
 

Elite237

New Member
I did it. Now doesn't show error message, just don't put the data in Excel. My code is:
Code:
DEFINE VARIABLE chExcel  AS COM-HANDLE.
DEFINE VARIABLE chWorkbook  AS COM-HANDLE.
DEFINE VARIABLE chWorksheet AS COM-HANDLE.
DEFINE VARIABLE registro  AS CHAR FORMAT "X(316)".
DEFINE VARIABLE ubicacion  AS CHAR NO-UNDO.
DEFINE VARIABLE sexo  AS CHAR NO-UNDO.
DEFINE VARIABLE area_op  AS CHAR NO-UNDO.
DEF INPUT PARAMETER ficha AS INT NO-UNDO.

CREATE "Excel.Application" chExcel.
chExcel:VISIBLE = TRUE.
chWorkbook = chExcel:Workbooks:Add(1).
  ASSIGN
  chWorkSheet = chExcel:Sheets:ITEM(1).
  chWorkSheet:COLUMNS("C" + ":XFD"):HIDDEN = true.
 
  chWorksheet:Range('A1'):ColumnWidth = 17.71.
  chWorksheet:Range('A6:B16'):FONT:Bold = True.
  chWorksheet:Range('A6:B31'):FONT:NAME = "Arial".
  chWorksheet:Range('B1'):HorizontalAlignment = 1.
  chWorksheet:Range('B1:B18'):ColumnWidth = 14.29.
 
  chWorksheet:Range('A6:B6'):FONT:SIZE = 12.
  chWorksheet:Range('A6:B6'):HorizontalAlignment = 1.

  chWorksheet:Range('A7:B8'):FONT:SIZE = 12.
  chWorksheet:Range('A7:B8'):HorizontalAlignment = 3.
  chWorksheet:Range('A7:B8'):VerticalAlignment = 5.

  chWorksheet:Range('A9:B10'):FONT:SIZE = 12.
 
  chWorksheet:Range('A15:B20'):FONT:SIZE = 11.
  chWorksheet:Range('A15:B20'):FONT:Bold = True.

  chWorkSheet:range('A23:B29'):FONT:Bold = True.
  chWorkSheet:range('A25:A29'):Font:Size = 10.
  chWorkSheet:range('A25:A29'):Font:Size = 10.
  area_op = "ADMINISTRACION".
 
 
  FIND FIRST empleado WHERE Empleado.ficha = ficha NO-LOCK NO-ERROR.
  IF AVAILABLE empleado THEN
  FIND FIRST Area WHERE area.area = empleado.area NO-LOCK NO-ERROR.
  IF AVAILABLE area THEN 
 
  FIND FIRST Categos WHERE Categos.catego = Empleado.catego NO-LOCK NO-ERROR.
  IF  AVAILABLE Categos THEN
  FIND FIRST Tabula WHERE Categos.nivel = Tabula.nivel NO-LOCK NO-ERROR.
  IF  AVAILABLE Tabula THEN
 
  FIND FIRST Deptos WHERE Deptos.depa = Empleado.depa NO-LOCK NO-ERROR.
  IF AVAILABLE Deptos THEN
 
  FIND FIRST Beneficiarios WHERE Beneficiarios.ficha = Empleado.ficha NO-LOCK NO-ERROR.
  IF AVAILABLE Beneficiarios THEN
 
  FIND FIRST Datos_emp WHERE Datos_emp.ficha = Empleado.ficha NO-LOCK NO-ERROR.
  IF AVAILABLE Datos_emp THEN
 
  chWorksheet:Range('A6'):VALUE = "No. Emp" + " " + STRING(Empleado.ficha).
  chWorkSheet:range('A6'):Interior:Color = 192.
  chWorkSheet:range('A6'):FONT:COLOR = -460552.
  chWorkSheet:range('A6'):FONT:Italic = true.
  IF area_op = "ADMINISTRACION" THEN DO:
  RUN Inserta_Datos_Admon_SMC.p(Empleado.ficha).
  END.
  ELSE
  IF area_op = "OPERACION" THEN DO:
  RUN Inserta_Datos_Oper_SMC.p(Empleado.ficha).
  END.
  ELSE
  IF area_op = "OCEANIC 1" THEN DO:
  RUN Inserta_Datos_Oceanic1_SMC.p(Empleado.ficha).
  END.
  ELSE
  IF area_op = "GARZPROM" THEN DO:
  RUN Inserta_Datos_Garzprom_SMC.p(Empleado.ficha).
  END.
  ELSE
  IF area_op = "TOLTECA" THEN DO:
  RUN Inserta_Datos_Tolteca_SMC.p(Empleado.ficha).
  END.
  ELSE
  IF area_op = "BUCCANEER" THEN DO:
  RUN Inserta_Datos_Buccaneer_SMC.p(Empleado.ficha).
  END.
 
  chExcel:Range("A23"):rows:pageBreak = -4135.
 
  chWorksheet:Range('A23'):VALUE = "DATOS GENERALES".
  chWorksheet:Range('A23:B23'):Font:Size = 12.
  chWorksheet:Range('A23:B23'):HorizontalAlignment = 3.
  chWorksheet:Range('A23:B23'):MERGE.
  chWorksheet:Range('A23:B23'):Font:Color = 192.
  chWorksheet:Rows("24"):RowHeight = 10.
 
  chWorksheet:Range('A25'):VALUE = "Ingreso:  " + STRING (Empleado.f_alta, "99/99/9999").
  chWorksheet:Range('A26'):VALUE = "IMSS:  " + Empleado.afilia_seg.
  chWorksheet:Range('A27'):VALUE = "Sangre:  " + Datos_emp.tipo_sangre.
  chWorksheet:Range('A28'):VALUE = "Alergias:  " + Datos_emp.alergias.
  chWorksheet:Range('A29'):VALUE = "Tel:  " + SUBSTRING(Datos_emp.telefono,1,12).
 
  chWorksheet:Rows("24"):RowHeight = 10.
  chWorksheet:Rows("30"):RowHeight = 10.
 
  chWorksheet:Range('A31'):VALUE = "En caso de emergencia contactar a:".
  chWorksheet:Range('A31:B31'):HorizontalAlignment = 1.
  chWorksheet:Range('A32'):VALUE = "  " + Beneficiarios.nom_avisar1.
  chWorkSheet:range('A31:B33'):Font:Size = 7.
  chWorksheet:Range('A31:B31'):HorizontalAlignment = 1.
  chWorksheet:Range('A31:B31'):MERGE.
  chWorksheet:Range('A32:B32'):MERGE.
  chWorksheet:Rows("31:33"):RowHeight = 11. /*Ancho de la fila*/
  chWorksheet:Range('A33'):VALUE = "Telefono:  " + "" + SUBSTRING(Datos_emp.telefono,16,13).
 
  chWorksheet:Range('A41'):VALUE = "*" + "SMC" + "-" + STRING (Empleado.ficha)+ "*".
  chWorksheet:Range('A41:B41'):FONT:NAME = "IDAutomationHC39M".
  chWorksheet:Range('A41:B41'):MERGE.
  chWorkSheet:range('A41:B41'):Font:Size = 13.
  chWorksheet:Range('A41:B41'):HorizontalAlignment = 3.
 
  chWorkSheet:Shapes:AddPicture("C:\nom_v10\smc_carm\Iconos\SMC-Firma.jpg", FALSE, TRUE,  2.64, 462.76, 171, 103):VISIBLE = TRUE.
  chWorkSheet:rows("A42:XFD1048576"):HIDDEN = true.
 
  chWorksheet:pageSetup:TopMargin  = chExcel:CentimetersToPoints(0).
  chWorksheet:pageSetup:LeftMargin  = chExcel:CentimetersToPoints(0).
  chWorksheet:pageSetup:RightMargin  = chExcel:CentimetersToPoints(0).
  chWorksheet:pageSetup:BottomMargin = chExcel:CentimetersToPoints(0).
  chWorksheet:pageSetup:FooterMargin = chExcel:CentimetersToPoints(0).
  chWorksheet:pageSetup:HeaderMargin = chExcel:CentimetersToPoints(0).
  chWorksheet:pageSetup:Zoom = 79.
  chWorksheet:pageSetup:FitToPagesWide = 1.
  chWorksheet:pageSetup:FitToPagesTall = 39.
  chWorksheet:pageSetup:printArea = "A1:B41".
  chWorksheet:pageSetup:CenterHorizontally = TRUE.


RELEASE OBJECT chWorksheet.
RELEASE OBJECT chWorkbook.
RELEASE OBJECT chExcel.
 

Elite237

New Member
And this my other procedure that I am trying bring and fill the cells:
Code:
DEFINE VARIABLE chExcel  AS COM-HANDLE.
DEFINE VARIABLE chWorkbook  AS COM-HANDLE.
DEFINE VARIABLE chWorksheet AS COM-HANDLE.
DEF INPUT PARAMETER ficha AS INT NO-UNDO.

CREATE "Excel.Application" chExcel.
chExcel:VISIBLE = FALSE.
chWorkbook = chExcel:Workbooks:Add(1).
ASSIGN
 
  chWorkSheet = chExcel:Sheets:ITEM(1).
  chWorkSheet:COLUMNS("C" + ":XFD"):HIDDEN = true.
 
  chWorksheet:Range('A1'):ColumnWidth = 17.71.
  chWorksheet:Range('A6:B16'):FONT:Bold = True.
  chWorksheet:Range('A6:B31'):FONT:NAME = "Arial".
  chWorksheet:Range('B1'):HorizontalAlignment = 1.
  chWorksheet:Range('B1:B18'):ColumnWidth = 14.29.
 
  chWorksheet:Range('A6:B6'):FONT:SIZE = 12.
  chWorksheet:Range('A6:B6'):HorizontalAlignment = 1.

  chWorksheet:Range('A7:B8'):FONT:SIZE = 12.
  chWorksheet:Range('A7:B8'):HorizontalAlignment = 3.
  chWorksheet:Range('A7:B8'):VerticalAlignment = 3.

  chWorksheet:Range('A9:B10'):FONT:SIZE = 12.
   
  chWorksheet:Range('A15:B20'):FONT:SIZE = 11.
  chWorksheet:Range('A15:B20'):FONT:Bold = True.
 
  chWorkSheet:range('A23:B29'):FONT:Bold = True.
  chWorkSheet:range('A25:A29'):Font:Size = 10.
  chWorkSheet:range('A25:A29'):Font:Size = 10.
 
  FIND FIRST empleado WHERE Empleado.ficha = ficha NO-LOCK NO-ERROR.
  IF AVAILABLE empleado THEN
  FIND FIRST Area WHERE area.area = empleado.area NO-LOCK NO-ERROR.
  IF AVAILABLE area THEN 
 
  FIND FIRST Categos WHERE Categos.catego = Empleado.catego NO-LOCK NO-ERROR.
  IF  AVAILABLE Categos THEN
  FIND FIRST Tabula WHERE Categos.nivel = Tabula.nivel NO-LOCK NO-ERROR.
  IF  AVAILABLE Tabula THEN
 
  FIND FIRST Deptos WHERE Deptos.depa = Empleado.depa NO-LOCK NO-ERROR.
  IF AVAILABLE Deptos THEN
 
  FIND FIRST Beneficiarios WHERE Beneficiarios.ficha = Empleado.ficha NO-LOCK NO-ERROR.
  IF AVAILABLE Beneficiarios THEN
 
  FIND FIRST Datos_emp WHERE Datos_emp.ficha = Empleado.ficha NO-LOCK NO-ERROR.
  IF AVAILABLE Datos_emp THEN
 
  chWorksheet:Range('A6'):VALUE = "No. Emp" + " " + STRING(Empleado.ficha).
  chWorkSheet:range('A6'):Interior:Color = -3407872.
  chWorkSheet:range('A6'):FONT:COLOR = -460552.
  chWorkSheet:range('A6'):FONT:Italic = true.


chWorkSheet:range('A7:B8'):FONT:COLOR = -460552. /*Color Blanco*/
chWorkSheet:range('A7:B8'):MERGE.
chWorksheet:Range('A7:B8'):VALUE = "ADMINISTRACIÓN".
chWorkSheet:range('A7:B8'):Interior:Color =  16737792. /*Color Azul cielo*/

/*chWorkSheet:range('A8'):FONT:COLOR = -460552.  /*Color Blanco*/
chWorkSheet:range('A8:B8'):MERGE.*/
 
chWorksheet:Range('A9'):VALUE = Empleado.nombre.
chWorkSheet:range('A9'):Font:Italic = true.
 
chWorksheet:Range('A10'):VALUE = Empleado.ape_pat + " " + Empleado.ape_mat.
chWorkSheet:range('A10'):Font:Italic = true.
 
chWorksheet:Range('A11'):VALUE = Categos.desc_cat.
chWorksheet:Rows("13:14"):RowHeight = 10.
 
chWorksheet:Range('A15'):VALUE = "PROYECTO:  428234811".
chWorksheet:Rows("16"):RowHeight = 22.
chWorksheet:Rows("17:18"):RowHeight = 10.
 
chWorksheet:Range('A19'):VALUE = "VIGENCIA".
chWorksheet:Range('A20'):VALUE = "31/01/2015".
chWorkSheet:Shapes:AddPicture("C:\nom_v10\smc_carm\Iconos\logoSMC.JPG", FALSE, TRUE, 1.64, 1.076, 95, 75):VISIBLE = TRUE.
chWorkSheet:Shapes:AddPicture("C:\nom_v10\smc_carm\Fotos\" + "SMC" + STRING(empleado.ficha) + ".JPG", FALSE, TRUE, 101.12, 1.05, 80, 90):VISIBLE = TRUE.
  /*Arriba, Abajo, Ancho, Alto*/
chWorkSheet:Shapes:AddPicture("C:\nom_v10\smc_carm\Iconos\pie_rojo1.JPG", FALSE, TRUE, 98.12, 254.76, 78, 65):VISIBLE = TRUE.
  /*Arriba, Abajo, Ancho, Alto*/
chWorkSheet:Shapes:AddPicture("C:\nom_v10\smc_carm\Iconos\pie_rojo2.JPG", FALSE, TRUE, 6.95, 298.76, 90.75, 21):VISIBLE = TRUE.
 

Osborne

Active Member
A couple of things to try. Instead of this:
Code:
chWorkSheet = chExcel:Sheets:ITEM(1).
use:
Code:
chWorkSheet = chWorkbook:Worksheets(1).
If still a problem try using a COM-HANDLE for adding the pictures:
Code:
DEFINE VARIABLE chPicture AS COM-HANDLE.

chPicture = chWorkSheet:Shapes:AddPicture("C:\nom_v10\smc_carm\Iconos\logoSMC.JPG", FALSE, TRUE, 1.64, 1.076, 95, 75).
chPicture:Visible = TRUE.
 
Last edited:

Rob Fitzpatrick

ProgressTalk.com Sponsor
A couple of other points:

You use the ASSIGN keyword and indent all the assignments under it as if they are part of the same statement, but they are not. In earlier releases of Progress, and maybe even today (I confess I don't know if this is still an optimization), it was better for performance to consolidate multiple consecutive assignments into one statement. Multiple assignment statements:
Code:
ASSIGN

  chWorkSheet = chExcel:Sheets:ITEM(1).
  chWorkSheet:COLUMNS("C" + ":XFD"):HIDDEN = true.

  chWorksheet:Range('A1'):ColumnWidth = 17.71.
  chWorksheet:Range('A6:B16'):FONT:Bold = True.
can become one:
Code:
ASSIGN
  chWorkSheet = chExcel:Sheets:ITEM(1)
  chWorkSheet:COLUMNS("C" + ":XFD"):HIDDEN = true
  chWorksheet:Range('A1'):ColumnWidth = 17.71
  chWorksheet:Range('A6:B16'):FONT:Bold = True.

Worse, however, is the series of FIND and IF statements. Example:
Code:
FIND FIRST empleado WHERE Empleado.ficha = ficha NO-LOCK NO-ERROR.
IF AVAILABLE empleado THEN
  FIND FIRST Area WHERE area.area = empleado.area NO-LOCK NO-ERROR.
IF AVAILABLE area THEN
  FIND FIRST Categos WHERE Categos.catego = Empleado.catego NO-LOCK NO-ERROR.
IF  AVAILABLE Categos THEN
  FIND FIRST Tabula WHERE Categos.nivel = Tabula.nivel NO-LOCK NO-ERROR.
IF  AVAILABLE Tabula THEN
  FIND FIRST Deptos WHERE Deptos.depa = Empleado.depa NO-LOCK NO-ERROR.

You only do a FIND FIRST Area if a record is available in the empleado buffer. If that condition is false, you still check whether the Area record is available even though you didn't attempt to FIND it. If these were intended to be nested IF statements then you will need to add DO/END brackets to your IF AVAILABLE statements to achieve the desired logic.

Lastly, your last code sample looks incomplete. You create an Excel COM object, set its VISIBLE property to FALSE, and make some changes to it, but you don't clean up your COM handles with RELEASE statements and you don't save the workbook or set VISIBLE to TRUE. So either we aren't seeing all of your code or your code is incomplete.
 

Elite237

New Member
Thanks for all the comments. I will change properly for to have my code better tan before

Also I want tell you, that I did only one procedure.p and put all my other 6 procedures inside him. With this option I colud achieve work my procedure and open my Excel with the correct information.

I thought that I was limited in the codes line as in the Section Editor of a Window. For that reason I was trying call to my differents procedures.

Regards
 
Top