setting excel formula from code
 

I am working on an existing app using Powerbuilder 8. It outputs data
to an excel spreadsheet. The customer is adding over 100 lines of
calculations to the spreadsheet and lots of them are just the
summation of existing rows. I can insert for example, =SUM(D5:D6),
and the sum doesn't show, its the actual formula text that shows. If
I go to the cell and hit 'enter', the sum shows up correctly. This
spreadsheet is created on the fly with existing code.
Is there a way to register the formula from my code? The cells are of
type general, so I think they should show up correctly...

setting excel formula from code
 

hi
yes but post your code that is giving you the problem.
we don't know what to do until we see what the problem is.

Regards
FSt1

"Ralf" wrote:

I am working on an existing app using Powerbuilder 8. It outputs data
to an excel spreadsheet. The customer is adding over 100 lines of
calculations to the spreadsheet and lots of them are just the
summation of existing rows. I can insert for example, =SUM(D5:D6),
and the sum doesn't show, its the actual formula text that shows. If
I go to the cell and hit 'enter', the sum shows up correctly. This
spreadsheet is created on the fly with existing code.
Is there a way to register the formula from my code? The cells are of
type general, so I think they should show up correctly...

setting excel formula from code
 

Here is the code I am using to set the formula. It shows as text, not
as the sum of the 2 cells as I am wanting.


dw_6.SetItem(2, 1, " =SUM(D5:D6)")

setting excel formula from code
 

On Jul 14, 8:25*am, Ralf wrote:
Here is the code I am using to set the formula. *It shows as text, not
as the sum of the 2 cells as I am wanting.

dw_6.SetItem(2, 1, " =SUM(D5:D6)")

Sorry, bad info. The SetItem is used to set the values in a data
window.
I am not sure just yet how the data window is used to post the values
to excel.
I will post that info when found

setting excel formula from code
 

BTW, tasked to do PowerBuilder enhancements and have zero experience
in PB....
I am seeing where others are doing what I am doing with success. I am
not able to do it just yet.
Here is what I am doing.
I have the datawindow and I first save it as an HTMLTable. This keeps
the formula and works correctly, can view in EXCEL. But, the look is
different and the customer will not accept.

li_ret = dw_6.SaveAs(ls_save_pathname, HTMLTable!, TRUE)

Then, create and oleobject and connect to the file and save as excel.
I have gotten this code from elsewhere on the web to test, not sure
why it is doing it this way, but it gives an error at the SaveAs
portion.
Error: Error calling external object functin saveas at line xxx in
ue_ok event of object xxxx

OLEObject excel
excel = CREATE OLEObject
if excel.ConnectToObject(ls_save_pathname) = 0 then
excel.application.DisplayAlerts=FALSE
excel.application.workbooks(1).Parent.Windows
(excel.application.workbooks (1).Name).Visible=TRUE
excel.application.workbooks(1).SaveAs(ls_save_path name, 60)
excel.application.workbooks(1).close()
end if
DESTROY excel

I am open to other ways of doing this.