I need to add another sheet to an existing excel workbook. I have a temp table that needs to be dumped to a new sheet in a workbook, rename the sheet, and then exit. I use the following code to create a new excel document but I'm not sure how to open an existing one and work on that.
Code:
DEF VAR vchExcel AS COM-HANDLE NO-UNDO.
DEF VAR vchWorkbook AS COM-HANDLE NO-UNDO.
DEF VAR vchWorkSheet AS COM-HANDLE NO-UNDO.
DEF VAR vRow AS INT NO-UNDO.
CREATE "Excel.Application":U vchExcel.
ASSIGN
vchExcel:VISIBLE = FALSE
vchWorkBook = vchExcel:WorkBooks:ADD()
vchWorkSheet = vchExcel:Sheets:ITEM(1)
vchWorkSheet:Range("A1:D1"):FONT:Bold = TRUE
vchWorkSheet:Range("A:D"):HorizontalAlignment = 3.
ASSIGN
vchExcel:Range("A1"):VALUE = "Day"
vchExcel:Range("B1"):VALUE = "Date"
vchExcel:Range("C1"):VALUE = "Work Done"
vchExcel:Range("D1"):VALUE = "Hours"
.
vRow = 1.
FOR EACH rhours:
ASSIGN
vRow = vRow + 1
vchWorkSheet:Range("A":U + STRING(vRow)):VALUE = rhours.DAY
vchWorkSheet:Range("B":U + STRING(vRow)):VALUE = rhours.DATE
vchWorkSheet:Range("C":U + STRING(vRow)):VALUE = rhours.workdone
vchWorkSheet:Range("D":U + STRING(vRow)):VALUE = rhours.hours
.
END.
vchWorkSheet:Range("A:D"):EntireColumn:AutoFit.
vchWorkBook:SaveAs(t-file,,,,,,,,).
vchWorkBook:CLOSE.
RELEASE OBJECT vchWorkSheet.
RELEASE OBJECT vchWorkBook.
vchExcel:QUIT.
RELEASE OBJECT vchExcel.
OUTPUT CLOSE.