[archive] Excell

[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

Hans,

I know that you can make this work using a tab delimited file. Just use H"08" instead of ";" as the delimiter and save the file as a .tab file. You will need to associate a .tab file with Excel the first time by clicking on the file and then selecting Excel as the program to always use to open this type of file. To open from a AcuCobol program just enter "spreadsheet.tab" on a command line and call C$system.

Duane

[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

One way to do it might be to use Excel to record a macro to do the import, then look at the VBA code it generates for the macro and try to simulate that using automation through Acucobol.

Another way is to write your data out in HTML format, naming the file .htm or .html, then start Excel with that filename on the command line.

Excel 2000 and higher understands HTML and allows some special tags to let you do formatting of the data.

See this page for details about using Excel to load HTML data.

[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

Originally posted by Hans
In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

May I ask why you prefer to go via a file? That is much more time consuming and error prone than transferring the data directly. Apart of that, if you insist, the method to use is Open, which you will find to be a member of the WorkBooks class. E.g. assuming you have an instance of workbooks named olWkBks:


MODIFY olWkBks Open("myimport.csv",
            BY NAME @Format xlCSV,
            BY NAME @Delimiter ";").


[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

I disagree about the time consuming part, at least from a run time point of view (maybe not from a programming point of view).

I did a project where I had to parse data from large ASCII reports and put the data into Excel. This was not an Acucobol project - I was using VB6.

I initially wrote it using automation, extracting data from the report files and placing the data into cells. It was taking 1 or 2 minutes (sometimes more) per report to complete.

I rewrote it to extract the data from the reports and write it out to a HTML (XML) file, then started Excel and had it open the file. This reduced the run time down to a few seconds.

For small amounts of data, the speed difference is not significant, but for large amounts of data, automation can be very slow.

[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

Joe, I'm curious. Was your VB program passing an array of cell data or were cells being populated individually? This could contribute to the automation slow down.

[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

I tried both ways. The arrays made it a little faster, but not enough. I also think late binding had an effect on speed, but I had to be able to support multiple versions of Excel and if I recall correctly, early binding required me to support a specific version of Excel (again, not positive about that, it was a while ago, but I recall some issue with one of the Excel objects I needed having a .7, .8. or .9 in the name depending on Excel version, and I didn't want to have a separate build for each).

I do know that I spent a lot of time messing with it, because I really wanted to use automation only. But once I tried the HTML/XML method, the speed difference was tremendous and I knew I was never going to touch it with automation based on what I had tried and what I had read from others with automation speed issues on Usenet.

Actually, in the end I did use some automation, but not for data loading. I wrote the data out to an HTML file, then used automation to load the HTML file and make some formatting and font changes that I could not do through HTML, then saved it out to a .xls file and deleted the temp HTML file. So it was all transparent to the user.

[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

I stand corrected. Thanks for the information about the various alternatives.

[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

This once again shows the awesome value of the acucorp forum community. Joe's tip will be a life saver when I have to deal with creating the inevitable "large" excel spreadsheet in the future. Way to go Joe!

[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

I appreciate all the help I get until now, but my knowledge isn't that big, certenly when it concerns translating VB to cobol.
I'm interested in understanding the DEF-file and I don't see any chains between the DEF-file and the Cobol-statements.

1. Where can I get information of understanding the DEF-file, and how to interprete it.

2. Ghisle, your approche is the one I need, but when I use the Open-method, I get an error saying : C:\\WLUCAS\\copylib\\WPGF257.evt, line 628: I-O expected, '(' found.

Is there something wrong in my code here ?

CREATE APPLICATION OF EXCEL
HANDLE IN olExcel.
MODIFY olExcel @VISIBLE = 1.
MODIFY olExcel workbooks::Add()
GIVING olWrkBk.
INQUIRE olWrkBk Worksheets::Item(1) IN
olWrkSh.

MODIFY olWrkBk Open("c:\\lucas\\test.csv",
BY NAME @Format xlCSV,
BY NAME @Delimiter ";").

Txs again for all your suggestions. I'm learning... learning

[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

Originally posted by Hans
I get an error saying : C:\\WLUCAS\\copylib\\WPGF257.evt, line 628: I-O expected, '(' found.
I'm learning... learning

You are learning and doing great! My fault really here because I didn't tell you the obvious when I suggested the solution. OPEN clashes with ACUCOBOL-GT reserved words, so, prefix it with @, e.g. @Open, and you should be set.

[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

Sorry to disturb again, but it's a matter of urgency.

I tried it before, putting a @ in front, but then I get an error saying 'Undefined data item'.

MODIFY olWrkBk @Open("filename",
BY NAME @Format xlCSV,
BY NAME @Delimiter ";").

[Migrated content. Thread originally posted on 25 January 2005]

In stead of sending data streight to excell, I'm writing a line sequential file with ";" as delimiter.

Now I want to start Excell from within my program and automaticly intruct it to import that file.

It must be possible but I don't find any exemples.
Any suggestions, or is there a better way to handle this kind of job ?

Txs

I must accuse the jetlag, really. The context class should be WorkBookS, not WorkBook. Thus:

Modify olExcel WorkBooks::Open(("filename",
BY NAME @Format xlCSV,
BY NAME @Delimiter ";") GIVING olWrkBk.

My sincere apologies.