export directly to Excel?

[WayneFrank]

Is there an example of a Progress program that does an export directly to Excel? Sort of like you can do for Word.

Thanks

 

[Rob Fitzpatrick]

If you do a search in the Development forum, including the child forums, for posts with titles containing "Excel" you will find lots of useful information and sample code.

At this point we know virtually nothing of your requirement. It would be helpful to know some details, such as:

• What is your Progress version?
• What is your Excel version?
• Can you describe how you want your Excel spreadsheet to look or function?
• Can you describe what you have tried so far, if anything?
• Have you read the Progress manual OpenEdge Development: Programming Interfaces?
  If not, read chapters 8 (Introduction to External Program Interfaces) and 14 (Using COM Objects in ABL).
• Are you familiar with the Excel Object Model for your version of Excel?

With more detailed requirements you may receive some more detailed assistance.

 

[Cringer]

I can't share code with you as it's not mine to share, but we actually find that for large volumes of data writing to Excel directly is very slow. We actually generate csv files, and then use the API to add them as worksheets in the workbook (and add any formatting we need).

 

[WayneFrank]

If you do a search in the Development forum, including the child forums, for posts with titles containing "Excel" you will find lots of useful information and sample code.

At this point we know virtually nothing of your requirement. It would be helpful to know some details, such as:

• What is your Progress version?
• What is your Excel version?
• Can you describe how you want your Excel spreadsheet to look or function?
• Can you describe what you have tried so far, if anything?
• Have you read the Progress manual OpenEdge Development: Programming Interfaces?
  If not, read chapters 8 (Introduction to External Program Interfaces) and 14 (Using COM Objects in ABL).
• Are you familiar with the Excel Object Model for your version of Excel?

With more detailed requirements you may receive some more detailed assistance.

Windows 7
Excel 2007
Progress vers 9 (I know old version, I can't do anything about it.)

I need to put values in certain cells of an Excel spreadsheet.

 

[Rob Fitzpatrick]

You don't seem interested enough in the challenge of solving this problem to answer all of my questions, but I'm going to help you anyway.

This sample code outputs a client's PROPATH to an Excel workbook. It could just as easily output values of variables or fields; it's just ASSIGN statements. Don't copy this code verbatim; it's just a simple, non-production-worthy example of what you can do. I agree with Cringer that this approach is slow, but it's probably the simplest approach if you're learning.

Take my advice and read the Progress documentation.

/*  Output the client's propath to an Excel workbook         
 *  rcf - 04/15/2009
 */
 
define variable v-i        as integer    no-undo.
define variable chExcel    as com-handle no-undo.
define variable chWorkbook  as com-handle no-undo.
define variable chWorksheet as com-handle no-undo.
 
create "Excel.Application" chExcel no-error.
 
if error-status:error = true then
  /* do error handling here: log error, exit, etc. */
  do:
    display "Microsoft Excel is not available on this platform." skip.
    return.
  end.
 
assign chWorkbook  = chExcel:Workbooks:Add()
       chWorksheet = chExcel:Sheets:Item(1).
 
chWorksheet:Range("a2"):Select.
 
repeat v-i = 1 to num-entries(propath):
    if length(entry(v-i,propath)) > 0 then
        chExcel:Selection:Value = entry(v-i,propath).
    else
        assign
            chExcel:Selection:Value          = "<blank line>"
            chExcel:Selection:Font:ColorIndex = 3.
 
    chExcel:Selection:Offset(1,0):Select.
end.
 
chWorksheet:Columns("a:a"):Select.
chExcel:Selection:Columns:AutoFit.
chWorksheet:Range("a2"):Select.
assign
    chExcel:ActiveWindow:FreezePanes            = true
    chWorksheet:Range("a1"):Value               = "Database: " +
                                                   DBNAME +
                                                   " (" +
                                                   string(v-i - 1) +
                                                   " entries)"
    chWorksheet:Range("a1"):Font:Bold           = true
    chWorksheet:Range("a1"):Interior:ColorIndex = 15
    chExcel:Visible                             = true.
 
release object chWorksheet.
release object chWorkbook.
release object chExcel.

This code works in 10.2B07/Excel 2010. I make no guarantees about ancient versions of Progress.

 

[WayneFrank]

What I am doing so far:

   CREATE "Excel.Application":U vchExcel.
   vchExcel:VISIBLE = FALSE.
   
   sprds_dir = "U:\PRG_DEVL\Scis\Forms\".
   excel-spreadsheet-name = "accounts-receivable.xlsx".
ASSIGN vchExcel:VISIBLE = TRUE
       vchWorkBook      = vchExcel:WorkBooks:OPEN(sprds_dir + excel-spreadsheet-name)
       vchWorkSheet     = vchExcel:Sheets:ITEM(1).
       
vchWorkSheet:Range("C8") = 4321.

This brings up the Excel file with the 4321 in the indicated cell.

What I need now is a way to open a new unnamed Excel file with the data in the cells.

Thanks

 

[WayneFrank]

OK, I have it working now. Rob the code you posted was a help, so thanks.