setting excel formula from code

  • Thread starter Thread starter Ralf
  • Start date Start date
R

Ralf

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...
 
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
 
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)")
 
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)")
Click to expand...

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
 
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_pathname, 60)
excel.application.workbooks(1).close()
end if
DESTROY excel

I am open to other ways of doing this.
 
Back
Top