Opening an excel sheet using Progress code

Question

I want to open 3 sheets in an excel file using progress 4gl and export data into it. Currently i am generating 3 csv files which i have to mail to users. I want to generate the data in the 3 csv in a single csv or excel file with 3 sheets. Can some 1 help me out with this??

OUTPUT TO VALUE(l_csv3). 
FOR EACH tt_stock 
   WHERE (tt_prodline = "ZMLC" OR 
          tt_prodline ="ZMSC" OR 
          tt_prodline = "ZZZZ" OR 
          tt_prodline ="ZLPG") 
   BREAK BY tt_nbr: 
   IF tt_po <> "" THEN 
      EXPORT DELIMITER "," 
         tt_part tt_um tt_desc tt_site tt_qtoh 
         tt_rop tt_sfst tt_nbr tt_qtor tt_vend 
         tt_line tt_name tt_prodline. 
   ELSE 
      EXPORT DELIMITER "," 
         tt_part tt_um tt_desc tt_site tt_qtoh 
         tt_rop tt_sfst "NO PO" tt_qtor tt_vend 
         tt_line tt_name tt_prodline. 
END. 
OUTPUT CLOSE.

Answer 1

Look what I ended up creating. A code to join excel worksheets. In the example are 4 worksheets files that will be united with the first one. It is not a merging, just a simple union.

    DEFINE VARIABLE chExcel      AS COM-HANDLE NO-UNDO.
    DEFINE VARIABLE chWorksheet1 AS COM-HANDLE NO-UNDO.
    DEFINE VARIABLE chWorksheet2 AS COM-HANDLE NO-UNDO.
    DEFINE VARIABLE chWorkbook1  AS COM-HANDLE NO-UNDO.
    DEFINE VARIABLE chWorkbook2  AS COM-HANDLE NO-UNDO.
    DEFINE VARIABLE iQtArq       AS INTEGER     NO-UNDO.
    DEFINE VARIABLE iPos         AS INTEGER     NO-UNDO.
    DEFINE VARIABLE idx          AS INTEGER     NO-UNDO.

    DEFINE TEMP-TABLE tt-arq
        FIELD nomeArq AS CHAR.
    CREATE tt-arq.
    tt-arq.nomeArq = "C:\damgra\excel\Pasta1.xlsx".
    CREATE tt-arq.
    tt-arq.nomeArq = "C:\damgra\excel\Pasta2.xlsx".
    CREATE tt-arq.
    tt-arq.nomeArq = "C:\damgra\excel\Pasta3.xlsx".
    CREATE tt-arq.
    tt-arq.nomeArq = "C:\damgra\excel\Pasta4.xlsx".

    iQtArq = 0.

    FOR EACH tt-arq.
        iQtArq = iQtArq + 1.
    END.

    IF iQtArq = 1 THEN RETURN.

    CREATE "excel.application" chExcel.

    iQtArq = 0.

    FOR EACH tt-arq.
        iQtArq = iQtArq + 1.

        IF iQtArq = 1 THEN DO:    
            chWorkbook1=chExcel:Workbooks:Open(tt-arq.nomeArq).
            chWorksheet1=chWorkbook1:Worksheets(1).
            chWorksheet1:NAME = "plan" + STRING(iQtArq).
            NEXT.
        END.

        iPos = chWorkbook1:sheets:COUNT .

        chWorkbook2=chExcel:Workbooks:Open(tt-arq.nomeArq).

        DO idx = 1 TO (chWorkbook2:sheets:COUNT):
            iPos = iPos + 1.
            chWorksheet2=chWorkbook2:Worksheets(idx).
            chWorksheet2:NAME = "plan" + STRING(iPos).
        END.

        chWorksheet2=chWorkbook2:Worksheets(1).
        chWorksheet1=chWorkbook1:Worksheets(chWorkbook1:sheets:COUNT).
        chWorksheet1:Activate.
        chWorkbook2:Sheets:move(,chWorksheet1).    
    END.

    chWorksheet1=chWorkbook1:Worksheets(1).
    chWorksheet1:Activate.

    chExcel:visible=true.

    IF valid-handle(chWorksheet1) THEN RELEASE OBJECT chWorksheet1.
    IF valid-handle(chWorksheet2) THEN RELEASE OBJECT chWorksheet2.
    IF valid-handle(chWorkbook1 ) THEN RELEASE OBJECT chWorkbook1 .
    IF valid-handle(chWorkbook2 ) THEN RELEASE OBJECT chWorkbook2 .
    IF valid-handle(chExcel )     THEN RELEASE OBJECT c

Answer 2

DEFINE VARIABLE  chExcelApplication AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE  chWorkbook         AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE  chWorksheet        AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE  chQueryTable       AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE cConnection         AS CHARACTER  NO-UNDO.
DEFINE VARIABLE lResult             AS LOGICAL    NO-UNDO.
DEFINE VARIABLE cFilesToImport      AS CHARACTER  NO-UNDO.
DEFINE VARIABLE cSheetTitles        AS CHARACTER  NO-UNDO.
DEFINE VARIABLE iNumberOfFiles      AS INTEGER    NO-UNDO.

/* Initialize Excel, File and Title Lists */
CREATE "Excel.Application" chExcelApplication.
chExcelApplication:Workbooks:ADD.

ASSIGN
    cFilesToImport = "myfile1.txt,myfile2.txt,myfile3.txt"
    cSheetTitles   = "Alpha, Beta, Theta"
    chWorkbook     = chExcelApplication:WorkBooks:Item(1).

/* Import ecah file's data into a new sheet of the workbook */
DO iNumberOfFiles = 1 TO NUM-ENTRIES(cFilesToImport).
    ASSIGN
         FILE-INFO:FILE-NAME = ENTRY(iNumberOfFiles, cFilesToImport)
         cConnection = "TEXT;" + FILE-INFO:FULL-PATHNAME
         chWorkSheet = chExcelApplication:Sheets:Item(iNumberOfFiles)
         chWorkSheet:Name = ENTRY(iNumberOfFiles, cSheetTitles)
         lResult     = chWorkSheet:QueryTables:Add(cConnection,   chWorkSheet:cells(1,1)).

    ASSIGN
        chQueryTable = chWorkSheet:QueryTables(1)
        chQueryTable:FieldNames = TRUE
        chQueryTable:RowNumbers = False
        chQueryTable:FillAdjacentFormulas = False
        chQueryTable:PreserveFormatting = FALSE
        chQueryTable:RefreshOnFileOpen = FALSE
        chQueryTable:RefreshStyle = 1
        chQueryTable:SavePassword = False
        chQueryTable:SaveData = True
        chQueryTable:AdjustColumnWidth = True
        chQueryTable:RefreshPeriod = 0
        chQueryTable:TextFilePromptOnRefresh = FALSE
        chQueryTable:TextFilePlatform = 437
        chQueryTable:TextFileStartRow = 1
        chQueryTable:TextFileParseType = 1
        chQueryTable:TextFileTextQualifier = 1
        chQueryTable:TextFileConsecutiveDelimiter = False
        chQueryTable:TextFileTabDelimiter = True
        chQueryTable:TextFileSemicolonDelimiter = False
        chQueryTable:TextFileCommaDelimiter = False
        chQueryTable:TextFileSpaceDelimiter = False
        chQueryTable:TextFileTrailingMinusNumbers = True
        lResult = chQueryTable:Refresh
        chQueryTable:BackgroundQuery = False.
END.

/* Make Spreadsheet Visible */
chExcelApplication:Visible = true.

/* Release All Objects */
RELEASE OBJECT chQueryTable  NO-ERROR.
RELEASE OBJECT chWorkSheet  NO-ERROR.
RELEASE OBJECT chWorkBook   NO-ERROR.
RELEASE OBJECT chExcelApplication NO-ERROR.