Add to Existing XLS

Phillip · Apr 17, 2014

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.

Osborne · Apr 17, 2014

This will open an existing workbook:

Code:

vchWorkBook = vchExcel:Workbooks:Open("c:\test1.xls").

Then to add a new worksheet after the first worksheet:

Code:

DEF VAR vchWorkSheet2 AS COM-HANDLE NO-UNDO.

vchWorkSheet2 = vchWorkBook:Worksheets(1).
vchWorksheet = vchWorkBook:Worksheets:Add(,vchWorksheet2).

Phillip · Apr 24, 2014

Osborne said:
This will open an existing workbook:

Code:

vchWorkBook = vchExcel:Workbooks:Open("c:\test1.xls").

Then to add a new worksheet after the first worksheet:

Code:

DEF VAR vchWorkSheet2 AS COM-HANDLE NO-UNDO. vchWorkSheet2 = vchWorkBook:Worksheets(1). vchWorksheet = vchWorkBook:Worksheets:Add(,vchWorksheet2).

do you know how I can rename the new worksheet?

Osborne · Apr 24, 2014

vchWorksheet:Name = "New Name".

Add to Existing XLS

Phillip

Member

Osborne

Active Member

Phillip

Member

Osborne

Active Member

Similar threads