How do I update a cell in an Excel file via DDE - Forum - OpenEdge General - Progress Community

How do I update a cell in an Excel file via DDE

 Forum

How do I update a cell in an Excel file via DDE

  • Good Day,

    We receive quotes from customers in Excel files. We currently update the data via DDE reading the Excel file for the necessary data for the quotes. We now need to return the same Excel file to the customers with the pricing information included. How can we update the Excel file we are reading in by updating a cell (row/col) with the correct data?

    Currently using  the command: ASSIGN v_range = w-aplha[ka-CusItmCol] + string(i).
                                                               wk-m = oExcel_Worksheet:Range(v_range):value()

    to get the information into the Progress app.

    Know there has to be an easy way to update hte Excel file.

    Thanks in advance for all your help.

    Chuck

  • Actually assigning data to the Range object should do:

    /* Add data */

    ASSIGN
      chWorksheet:Range("B1"):VALUE = "Value"
      chWorksheet:Range("B2"):VALUE = 255
      chWorksheet:Range("B3"):VALUE = 100
      chWorksheet:Range("B4"):VALUE = 250
      chWorksheet:Range("B5"):VALUE = 400
      chWorksheet:Range("B6"):VALUE = 100
      chWorksheet:Range("B7"):VALUE = 600.

    This K-Base entry has some more information on modifying Excel documents:

    ID: 21671
    Title: "Frequently Asked Questions About Microsoft Excel as an ActiveX Automation Server"
    Created: 01/11/2002Last Modified: 10/16/2008
    Status: Verified


    Goals: 
    • Frequently Asked Questions About Microsoft Excel as an ActiveX Automation Server
    • Microsoft Excel Frequently Asked Questions (FAQ)
    • How to open a MS Excel document?
    • How to create a workbook with a specific number of sheets?
    • How to insert a worksheet?
    • How to select a specific worksheet?
    • How to modify the cell's format (Font, Font's color, Cell's color, Horizontal Alignment)?
    • How to add data?
    • How to add a formula?
    • How to freeze pane?
    • How to save the file without any warning messages?



    Facts: 
    • Windows




    Fixes: 


    DEFINE VARIABLE chExcel    AS COM-HANDLE NO-UNDO.
    DEFINE VARIABLE chWorksheet AS COM-HANDLE NO-UNDO.
    DEFINE VARIABLE chWorkbook  AS COM-HANDLE NO-UNDO.

    CREATE "excel.application" chExcel.

    /* Open an Excel document  */

    chExcel:Workbooks:Open("c:\test1.xls").
    chExcel:visible = true.

    /* Sets the number of sheets that will be
      automatically inserted into new workbooks */

    chExcel:SheetsInNewWorkbook = 5.

    /* Add a new workbook */

    chWorkbook = chExcel:Workbooks:Add().

    /* Add a new worksheet as the last sheet */

    chWorksheet = chWorkbook:Worksheets(5).
    chWorkbook:Worksheets:add(, chWorksheet).
    RELEASE OBJECT chWorksheet.

    /* Select a worksheet */

    chWorkbook:Worksheets(2):Activate.
    chWorksheet =  chWorkbook:Worksheets(2).

    /* Rename the worksheet */

    chWorkSheet:NAME = "test".

    /* Modify the cell's format to Text */    

    chWorksheet:Cells:NumberFormat = "@".


    /* Modify the cell's format to Date */    

    chWorksheet:Cells:NumberFormat = "m/d/yy;@".

    /* Change the cell's color */

    chWorksheet:Columns("A:A"):Interior:ColorIndex = 5.

    /* Change the cell's format  */

    ASSIGN
      chWorksheet:Columns("A:A"):Font:ColorIndex = 2
      chWorksheet:Columns("A:A"):Font:Name = "Courrier New".
      chWorksheet:Columns("A:A"):Font:Bold = TRUE.
      chWorksheet:Columns("A:A"):Font:Italic = TRUE.

    /* Set underline: StyleSingle = 2 */

    chWorksheet:Columns("A:A"):FONT:UNDERLINE = 2 .

    /* Add data */

    ASSIGN
      chWorksheet:Range("B1"):VALUE = "Value"
      chWorksheet:Range("B2"):VALUE = 255
      chWorksheet:Range("B3"):VALUE = 100
      chWorksheet:Range("B4"):VALUE = 250
      chWorksheet:Range("B5"):VALUE = 400
      chWorksheet:Range("B6"):VALUE = 100
      chWorksheet:Range("B7"):VALUE = 600.

    /* Add a Formula */

    chWorksheet:Range("A8"):VALUE = "Total:".

    /* Set Cell's format to Number */

    chWorksheet:Range("B8"):NumberFormat = 0.
    chWorksheet:Range("B8"):Formula = "=SUM(B2:B7)".

    /* Set horizontal alignment
      Right Alignment: -4152 / Left Alignment :-4131  */

    chWorksheet:Range("B:B"):HorizontalAlignment = -4152.

    /* Freeze Pane */

    chWorksheet:Range("A2"):SELECT.
    chExcel:ActiveWindow:FreezePanes = TRUE.

    /* Save the new workbook without displaying alerts */

    chExcel:DisplayAlerts = FALSE.
    chWorkbook:SaveAs("c:\test2.xls",43,,,,,).

    /* Quit Excel */

    chExcel:quit().

    /* Release Com-handle  */

    RELEASE OBJECT chWorksheet.
    RELEASE OBJECT chWorkbook.
    RELEASE OBJECT chExcel.



    Notes: 
    The sample in the Fix statement has been tested with MS Excel 97, MS Excel 2000 and Progress version 9.1D. Compatibility with other versions of Microsoft Excel or Progress cannot be guaranteed.

    For further information, properties and methods of Microsoft Excel can be displayed in the COM Object Viewer (in Pro*Tools).
    You can also record a new macro and then translate the VBA code in 4GL.
    The Excel online help as well as the Microsoft web site (msdn.microsoft.com) may also be consulted.

  • Is there a way to get the COM to work on LINUX?

  • No. COM (or OLE Automation) is inter-process communication on Windows.

    CSV and SYLK are your options to create data for Excel on Linux. Or set up an Appserver running on Windows - that can talk to Excel via COM and your ABL process on Linux can connect to that appserver.

  • HiThis is abbas from sin heng chan.I want to insert a page break in the excel file via DDE.Can any one help.this is a macro from excel file    Rows("10:10").Select    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell    Rows("22:22").Select    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCellthis will insert a pagebreak at row 10 and row 22.thank youabbas