Filling an Excel-Sheet with an dynamic browse

[Storzum]

Hello,

it`s me again.


I want to put the data from a dynamic browse into an Excel-Sheet.
I work under Progress 91.d

My code (the query was opened and prepared before...):

DEFINE VARIABLE temp AS HANDLE NO-UNDO.
CREATE "Excel.Application" chExcelApplication.
chExcelApplication:Visible = FALSE.
chWorkbook = chExcelApplication:Workbooks:Add().
chWorkSheet = chExcelApplication:Sheets:Item(1).


myqry:GET-FIRST().

REPEAT WITH FRAME fmain:

IF (myqry:QUERY-OFF-END) THEN LEAVE.

temp = mybrowse:BUFFER-FIELD("Field - Name").

chWorkSheet:Range("A" + cColumn):Value = string(temp:BUFFER-VALUE()) NO-ERROR.


myqry:GET-NEXT.
end.

But it doesn`t work.
I have allready filled an Excel-Sheet with a dynamic browse, but I filled it with
the whole table and went trough all fields in a loop:
temp = mybuffer:BUFFER-FIELD(_field._field-name).
With this statement it worked.

But when I need only a few fields of the table I do this:
temp = mybrowse:BUFFER-FIELD("Field - Name").
And this doesn`t work.

I get the error:
"No temp data available (91)."
and:
"Buffer-field is a no queryable attribute for browse-widget."

Why it works with _field._field-name
but not with Field - Name?

Greets
Storzum

 

[bendaluz2]

In the instance that works, you are using the BUFFER-FIELD method of a BUFFER. In the second case, you are using the BUFFER-FIELD attribute of a BROWSE. They are not the same

 

[Storzum]

OMG

Thank you.



But with the Field - Name it do not work.
Now I have used the index-Number of the field.
This works.

Is there a way I can speed up the "Excel - Fill"?
It is very slow.

 

[bendaluz2]

Sorry, OLE is SLooooooooOOOOOW :tear:

You could try doing something like......

Export your data as a csv. Invoke an instance of Excel, read in the csv file then do any manipulation you need to do, ie formatting, formula's etc....

This will probably give you a lot more speed

HTH

 

[Storzum]

Oh.
Aehm.
:blush:
Can you give me a short example how I export data in a Txt-file and how I open it in Excel?

 

[Storzum]

OK.
I have create the csv-File with the put-command.

Wow.
It runs very fast.
Nice.

But one thing:
When I open the file in Excel, there is one (text-)field that sometimes was converted by excel into date.
Example: original: 10/05
in excel: Oct. 2005

When I use the OLE-Output I have put only a "'" before the 10/05 and excel shows the correctly 10/05.
But how can I do this, after excels opened the csv-File?

 

[bendaluz2]

Yeah, Excel will do that tries to determine data types for the columns.

Once the file is loaded, you will need to get a com handle to the column and change the datatype

or.... this is a longshot....

Stick quotes round the date and put a space at the front or back. hopefully excel wont realize that it is a date :awink:

 

[Storzum]

OK. It was very hard.

you will need to get a com handle to the column and change the datatype

Excel changed the data.

Stick quotes round the date and put a space at the front or back

Excel changed the data.

Now fasten your seatbelts when you hear how I have done it:
In the txt-File I have put a ' before the data.
After Excel have imported my File I changed the datatype of this column in "Text".
Then I have recorded a macro and looked (in VB) which statement I need to search and replace in Excel.
And now:
Search ' and replace with nothing doesnt work:
Again Excel means I have Numbers, after I cut the leading '.

I HAVE SEARCHED for ' and REPLACE IT WITH '.
THIS WORKS!!!!
The ' is gone and Excel lets any leading zeros.
Now I don`t understand anything.
But it works.

Excel is really silly, isn`t it???