Consultor Eletrônico

Status: Verified

GOAL:

How to export records to Excel using a SmartDataObjects where clause but not the SmartDataObject itself

GOAL:

How to send the records to Excel using ActiveX Automation

FACT(s) (Environment):

Progress 9.x

FIX:


DEFINE VARIABLE cTable   AS CHARACTER  NO-UNDO.
DEFINE VARIABLE cFields AS CHARACTER  NO-UNDO.
DEFINE VARIABLE hQuery   AS HANDLE     NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE     NO-UNDO.
DEFINE VARIABLE hField   AS HANDLE     NO-UNDO.
DEFINE VARIABLE iFields AS INTEGER    NO-UNDO.
DEFINE VARIABLE iRow     AS INTEGER    NO-UNDO.
DEFINE VARIABLE cRow     AS CHARACTER NO-UNDO INITIAL "A".
DEFINE VARIABLE chExcelApplication AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkbook         AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorksheet        AS COM-HANDLE NO-UNDO.

CREATE "Excel.Application" chExcelApplication.

ASSIGN chExcelApplication:Visible = TRUE
      chWorkbook                 = chExcelApplication:Workbooks:Add()
      chWorkSheet                = chExcelApplication:Sheets:Item(1)
      cTable                     = "Employee"
      cFields                    = "EmpNum,FirstName,LastName,WorkPhone".

CREATE QUERY hQuery.

CREATE BUFFER hBuffer FOR TABLE cTable.

hQuery:SET-BUFFERS(hBuffer).
hQuery:QUERY-PREPARE(DYNAMIC-FUNCTION('getQueryWhere' IN h_demployee)).
hQuery:QUERY-OPEN().

IF hQuery:GET-FIRST() = FALSE THEN
DO:
hQuery:QUERY-CLOSE().
chExcelApplication:QUIT().
RELEASE OBJECT chWorkSheet NO-ERROR.
RELEASE OBJECT chWorkBook NO-ERROR.
RELEASE OBJECT chExcelApplication NO-ERROR.
DELETE OBJECT hBuffer NO-ERROR.
DELETE OBJECT hQuery NO-ERROR.
RETURN.
END.

REPEAT:
   ASSIGN iRow = iRow + 1.
   DO iFields = 1 TO NUM-ENTRIES(cFields):
       hField = hBuffer:BUFFER-FIELD(ENTRY(iFields,cFields)).
        chWorkSheet:range((CHR(ASC(cRow) + iFields - 1)) + STRING(iRow)):Value = hField:BUFFER-VALUE.
   END.
   IF hQuery:GET-NEXT() =FALSE THEN
LEAVE.
END.

hQuery:QUERY-CLOSE().

DELETE OBJECT hField NO-ERROR.
DELETE OBJECT hBuffer NO-ERROR.
DELETE OBJECT hQuery NO-ERROR.

RELEASE OBJECT chExcelApplication NO-ERROR.
RELEASE OBJECT chWorkBook NO-ERROR.
RELEASE OBJECT chWorkSheet NO-ERROR.