Rich.Uchytil.com

Progress | Excel For Dummies

Excel For Dummies

When I first wanted to learn how to read an Excel spreadsheet from Progress, I asked on the PEG and was given many resources. Unfortunately, my feeble brain needed something very simple, step 1 do this, step 2 do that, etc. Now that I've learned how to do this, all that other information makes sense. So here's a very, very simple article on how to read from and write to Excel from Progress.

first you have to create some COM-HANDLE variables. One for Excel, one for the WorkBook (file), and one for the WorkSheet. Something like this:

DEF VAR vchExcel AS COM-HANDLE NO-UNDO. DEF VAR vchWorkBook AS COM-HANDLE NO-UNDO. DEF VAR vchWorkSheet AS COM-HANDLE NO-UNDO.

You will also need to keep track of what Excel row you are on. Usually you're reading from some starting row down to the end of the spreadsheet. So this is done easiest with an INTEGER var you will increment.

DEF VAR vRow AS INT NO-UNDO.

Next you have to create the Excel object.

CREATE "Excel.Application":U vchExcel.

What you do next depends on if you are writing or reading.

READING

Open the WorkBook (file) and tell Excel which WorkSheet you'll be getting the data from:

ASSIGN vchExcel:VISIBLE = FALSE vchWorkBook = vchExcel:WorkBooks:OPEN("c:\tmp\SalesForecast.xls") vchWorkSheet = vchExcel:Sheets:ITEM(1).

Notice I set the VISIBLE attribute to FALSE. Normally when I read the data, I don't want Excel to open, no need to see the data while I'm importing it. the default is to have Excel open, so if you want it open, leave this statement out.

Now you have your Excel spreadsheet open and can start reading the data. The basic command for getting the data is:

variable = vchWorkSheet:Range("A":U + STRING(vRow)):TEXT

Note there are two ways to get the data, TEXT and VALUE. While VALUE is nice in that it will format things for you, if you have a formula in a cell, it will get that formula instead of what is being displayed. It's easiest to use TEXT and then use Progress to convert the type, especially when you're just starting out. Here's some quick code for reading from Excel (ttSuppName is a temp-table, I just didn't show the definition for that):

GetExcel-BLK: REPEAT TRANSACTION: ASSIGN vError = FALSE vRow = vRow + 1. /** QUIT WHEN REACH A BLANK LINE **/ IF vchWorkSheet:RANGE("A":U + STRING(vRow)):TEXT EQ "" THEN LEAVE GetExcel-BLK. CREATE ttSuppName. /* IMPORT FROM EXCEL */ ASSIGN ttSuppName.tSupplierCode = vchWorkSheet:Range("A":U + STRING(vRow)):TEXT ttSuppName.tSupplierName = vchWorkSheet:Range("B":U + STRING(vRow)):TEXT NO-ERROR. /** WAS THERE AN ERROR READING THE SPREADSHEET? **/ IF ERROR-STATUS:ERROR THEN DO: ASSIGN vError = YES. MESSAGE ERROR-STATUS:GET-MESSAGE(1) VIEW-AS ALERT-BOX. END. END. /** END OF REPEAT TRANSACTION - GetExcel-BLK **/

The ERROR-STATUS part isn't necessary, but when I was learning how to do this, I liked having it in so I could see if any errors came up and what they were.

Once you are done, you have to close Excel and release the objects - or you'll have problems later:

vchWorkBook:CLOSE. RELEASE OBJECT vchWorkSheet. RELEASE OBJECT vchWorkBook. vchExcel:QUIT. RELEASE OBJECT vchExcel.

And that's all there is to reading the spreadsheet!

WRITING

This is very similar. You can either open an existing Excel spreadsheet, or create a new one. We'll create a new one and tell Excel we want to use the first WorkSheet. Notice this time we aren't setting the VISIBLE attribute. You could set this to FALSE, have it create the spreadsheet, then save it all without the user seeing what's going on. For demonstation purposes, we'll have it be visible so we can watch it being built:

ASSIGN vchExcel:VISIBLE = TRUE vchWorkBook = vchExcel:WorkBooks:ADD() vchWorkCheet = vchExcel:Sheets:ITEM(1).

The basic command for writing data is:

vchWorkSheet:Range("A":U + STRING(vRow)):VALUE = shp_item.pro-type.

So writing might look something like this (using the Sports db):

FOR EACH Customer NO-LOCK: ASSIGN vRow = vRow + 1 vchWorkSheet:Range("A":U + STRING(vRow)):VALUE = Customer.CustNum vchWorkSheet:Range("B":U + STRING(vRow)):VALUE = Customer.Name vchWorkSheet:Range("C":U + STRING(vRow)):VALUE = Customer.CreditLimit vchWorkSheet:Range("D":U + STRING(vRow)):VALUE = Customer.Balance. END.

And again, once you're done writing the data, you need to close everything. In this case, you can also choose to save the spreadsheet if you want. If you DO want to save it, use this command (and yes, you do need all of those comma's - 7 of them):

vchWorkBook:SaveAs("c:\tmp\millnet2.xls",,,,,,,).

This time we're not telling Excel to quit because we want to leave it open. Again, you could create this spreadsheet and save it without the user seeing it being done. In that case you'd probably want to quit Excel when you're done, as we did above with reading data. So let's just release our objects and leave Excel open:

RELEASE OBJECT vchWorkSheet. RELEASE OBJECT vchWorkBook. RELEASE OBJECT vchExcel.

And that's all there is to it! now there's TONS more you can do, formatting, formula's, charts, etc. That's for another article. Hope this was helpful!