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/2002 | Last Modified: 10/16/2008 |
| Status: Verified | |
Goals:
| |
Facts:
| |
Fixes:
CREATE "excel.application" chExcel. /* Open an Excel document */ chExcel:Workbooks:Open("c:\test1.xls"). /* Sets the number of sheets that will be chExcel:SheetsInNewWorkbook = 5. /* Add a new workbook */ chWorkbook = chExcel:Workbooks:Add(). /* Add a new worksheet as the last sheet */ chWorksheet = chWorkbook:Worksheets(5). /* Select a worksheet */ chWorkbook:Worksheets(2):Activate. /* 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;@". chWorksheet:Columns("A:A"):Interior:ColorIndex = 5. /* Change the cell's format */ ASSIGN /* Set underline: StyleSingle = 2 */ chWorksheet:Columns("A:A"):FONT:UNDERLINE = 2 . /* Add data */ ASSIGN /* Add a Formula */ chWorksheet:Range("A8"):VALUE = "Total:". /* Set Cell's format to Number */ chWorksheet:Range("B8"):NumberFormat = 0. /* Set horizontal alignment chWorksheet:Range("B:B"):HorizontalAlignment = -4152. /* Freeze Pane */ chWorksheet:Range("A2"):SELECT. /* Save the new workbook without displaying alerts */ chExcel:DisplayAlerts = FALSE. /* Quit Excel */ chExcel:quit(). /* Release Com-handle */ RELEASE OBJECT chWorksheet. | |
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