Excel howto

Question

Tips and examples for communicating with MS-Excel.

Answer

by Jeff Pilant

Sites with information to write *.XLS directly:

http:sc.openoffice.org/excelfileformat.pdf

http:www.sector13.org/jason/xls/

http:www.wotsit.org/search.asp?s=CEL

http:chicago.sourceforge.net/xlhtml/

----

Some Excel tricks:

1) To speed up the excel generation, you can disable the screen updating with the following:

chExcelApplication:ScreenUpdating = False.

and restore it with

chExcelApplication:ScreenUpdating = True.

2) If you don't need to see the spreadsheet at all while it is being created, then remove the following from your code:

chExcelApplication:Visible = true.

This makes Excel go a bit faster because it doesn't do any
displaying. Be careful of this one if you are still debugging.

3) Some Excel COM commands take more time than others. Note which take long amounts and maybe find a way around or without them.

4) Grouping assignments into a single ASSIGN statement helps a little bit.

5) Some commands can be applied to a range of cells, like the

formatting of a set of cells to the same format. A single
assignment to set all the cells is faster than several
assignments to a single cell each.

----------

In the Progress COM Object Viewer, select the help file,
select a method/attribute/... and press F1, if help is
properly installed you will get a help page about the
method/attribute/etc.
At least it is like this for Word/Excel...

Juan Nunez

----------

KB# 18762 ACTIVEX - One Way to Manage Your COM-HANDLE References

KB# 19563 ACTIVEX - Reference Suggestion

----------

How to find parameter info.

SaveAs example:

- Open the corresponding COM help file for the application: Vbaxl8.hlp
- Select the tab
- Type "SaveAs"
- Double click on "SaveAs Method". It brings up a window that breaks down the various parameters.
- Click on the link under the FileFormat property description. You will see a screen that lists all the variable/constant names used for this parameter.
- Run the Progress COM Object Viewer (proobjvw.exe) on the corresponding com object file: Excel8.olb
- Select the entry "XlFileFormat" (object name found on the help page above)
- Select the entry "xlExcel5" (again found on the help page)
- Read the info in the window
- Use the value listed (xlExcel5 = 39) as the value for the parameter

Result:

chWorkBook:SaveAs(chFileName, -4143,,,,,,, TRUE)

----------

How to open an excel file program Progress:

    DEFINE VARIABLE chExcel AS COM-HANDLE.
    CREATE "Excel.Application" chExcel.
    chExcel:Workbooks:open(chFileName).
    chExcel:Visible = TRUE.

----------

How to close an excel file program Progress:

    /* all prompts will be shut off/on */
    /* this prevents unsaved check     */
    chExcel:DisplayAlerts = false.
    /* Close the workbook, Excel still open */
    chExcel:Close().
    /* Exit Excel */
    chExcel:QUIT().

----------

Progress COM Object Viewer:

%DLC%\bin\proobjvw.exe

----------

How to acces the MS Help file for info:

MS Excel:

- Run "C:\Program Files\Microsoft Office\Office\VBAXL8.HLP"
- On the find tab, type: Microsoft Excel Objects
- Double-click: Microsoft Excel Objects

MS Word:

- Run "C:\Program Files\Microsoft Office\Office\VBAWRD8.HLP"
- On the find tab, type: Microsoft Word Objects
- Double-click: Microsoft Word Objects

----------

Win32 Constants program contains many constants for SDK and Office: download here

----------

Standard practice for MS Office:

- Start recording a VB macro
- Select the item you want
- Make the mods you need
- Stop recording
- Edit the macro for it's text
- Convert to Com-Handle style syntax for Progress


Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Progress Programming to Excel

I have an excel file I want to open as a template then save it to a newly named excel file. I can do that but.. I wish to update a few cells in my new file before I save it. Problem I am having is the changes I make also update my original excel file I am using as a template. any suggestions? I tried opening the original, saving it as a new name, then opening the newly named file, making changes there and saving. But, still.. somehow, the change makes it back to my original spreadsheet.


OE 10.2B05 Linux DB Windows

OE 10.2B05 Linux DB Windows Clients

Dan,
If you are already saving as a new filename then this shouldn't be a problem.

Using the example below you assign a new name to chFileName then do the SaveAs.
The only way you could save over your template is to do the SaveAs, then change chFileName, then do another SaveAs. Which would be the wrong way to do it.

chWorkBook:SaveAs(chFileName, -4143,,,,,,, TRUE)

Also you'll want to check out libooxml in oehive to take this to the next level. I stopped using Activex updates like this 5 years ago.

libooxml will even let you use templates, then you save your data as tt files and send it out to Excel. Doesn't even need a local copy of excel to be running so it frees you up to do more things.

It's cool if you still want to keep going with Activex Excel Programming. Just letting you know there are other options.