ASSIGN mnama = "d:\sytework\" +
fiitem +
STRING(DAY(TODAY),"99") +
STRING(MONTH(TODAY),"99") +
SUBSTRING(string(year(TODAY)),3) + '.xls'.
EMPTY TEMP-TABLE ttreport.
FOR EACH ITEM WHERE ITEM.ITEM BEGINS fiitem
NO-LOCK:
CREATE ttreport.
ASSIGN ttreport.ITEM = ITEM.ITEM
ttreport.DESCRIPTION = item.DESCRIPTION
ttreport.u-m = item.u-m
.
END.
{XCREATEexcel.i mnama}
MESSAGE "hasil berada di " + mnama
VIEW-AS ALERT-BOX INFO BUTTONS OK.
END PROCEDURE.
DEFINE VARIABLE hExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE hWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE hWorksheet AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE hBufferHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO
INITIAL "ttReport".
DEFINE VARIABLE iCounter AS INTEGER NO-UNDO.
DEFINE VARIABLE hFieldHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE hQueryHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE iRowNum AS INTEGER NO-UNDO.
DEFINE VARIABLE ColumnRange AS CHARACTER NO-UNDO.
/********Open Excel and initialize variables********/
OS-DELETE VALUE({1}).
CREATE "Excel.Application" hExcel.
ASSIGN
hExcel:VISIBLE = FALSE
hWorkbook = hExcel:Workbooks:Add()
hWorkSheet = hExcel:Sheets:Item(1)
iRowNum = 1.
/* Pump field names as EXCEL column headers*/
CREATE BUFFER hBufferHandle FOR TABLE cTableName.
DO iCounter = 1 TO hBufferHandle:NUM-FIELDS:
ASSIGN
hFieldHandle = hBufferHandle:BUFFER-FIELD(iCounter)
hExcel:Worksheets("Sheet1"):Cells(iRowNum,iCounter ) = CAPS(hFieldHandle:LABEL)
hExcel:Worksheets("Sheet1"):Cells(iRowNum,iCounter):Interior:ColorIndex = 5
hExcel:Worksheets("Sheet1"):Cells(iRowNum,iCounter):FONT:Bold = TRUE.
END.
/* Create dynamic query for the cTablename */
CREATE QUERY hQueryHandle.
hQueryHandle:SET-BUFFERS(hBufferHandle).
hQueryHandle:QUERY-PREPARE("for each " + cTableName ).
hQueryHandle:QUERY-OPEN.
/* Pump the table data into your worksheet */
REPEAT:
iRowNum = iRowNum + 1.
hQueryHandle:GET-NEXT().
IF hQueryHandle:QUERY-OFF-END THEN LEAVE.
ELSE DO iCounter = 1 TO hBufferHandle:NUM-FIELDS:
hFieldHandle = hBufferHandle:BUFFER-FIELD(iCounter).
hExcel:Worksheets("Sheet1"):Cells(iRowNum,iCounter ) =
hFieldHandle:BUFFER-VALUE.
END.
END.
/* Define populated column range and auto-format width */
/* Hint: First Column is column "A" hence CHR(65) usage*/
ASSIGN
ColumnRange = CHR(65) + ":" + CHR(65 + hBufferHandle:NUM-FIELDS - 1).
hExcel:COLUMNS(ColumnRange):SELECT.
hExcel:SELECTION:COLUMNS:AUTOFIT.
/* Perform housekeeping and cleanup steps */
hworkbook:SaveAs({1},1,?,?,?,?,?).
hExcel:Application:Workbooks:CLOSE() NO-ERROR.
hExcel:Application:QUIT NO-ERROR.
RELEASE OBJECT hWorksheet.
RELEASE OBJECT hWorkbook.
RELEASE OBJECT hExcel.
DELETE OBJECT hBufferHandle.
DELETE OBJECT hQueryHandle.
