datawindow saveas to excel

[mgo]

I am trying to save a datawindow to excel. I need to also
add
formulas to the output. If I save as xls, the formula shows
as text. I seen where I could save a HTMLTable, but it has
a different look and the row headers are all in a line now
instead of offset with tabs in certain places. I seen where
I should be able to use OLEObject on the HTMLTable file and
use SaveAs to save it in the native xls. But, I get the
powerbuilder application execution error R0035 when it does
the SaveAs command. Here is the snippet of code I am trying
to use.
li_ret = dw_6.SaveAs(ls_save_pathname, HTMLTable!, TRUE)
OLEObject excel
excel = CREATE OLEObject
if excel.ConnectToObject(ls_save_pathname) = 0 then
excel.application.DisplayAlerts=FALSE
excel.application.workbooks(1).Parent.Window(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'm a novice and have no clue how to fix this issue...

[Paul Horan[Sybase]]

There's a 3rd-party utility that does this. Google DW2XLS...

--
Paul Horan[Sybase]
http://blogs.sybase.com/phoran/

<mgo> wrote in message news:4a5df9b2.25c...@sybase.com...

[Ivaylo Ivanov]

We've bought DW2XLS and are pretty satisfied with the results it gives. The
speed is very improved in the latest versions compared to what it was a few
years ago (the core logic is taken out of the PBL to a DLL). It works with
different DW presentation styles, it works even in .NET webform.

However, I am sorry to say that making Excel formulae is not in its current
features.

I haven't found a common way to make a pretty XLS with some formulae using
the native PB functionality and/or DW2XLS. The only solution which comes to
my mind for a native PB app is the OLE technique. Go to Excel and select
"Tools --> Macro --> Record new macro". Then enter a formula in some cell.
Stop the recording and go to "Tools --> Macro --> Macros". Locate your macro
name and observe the commands recorded during the formula creation and
eventually use them in PowerBuilder.

For example: putting a formula for C1 = A1 + B1 would be something similar
to:

OLEObject excel
excel = CREATE OLEObject

if excel.ConnectToNewObject("Excel.Application") <> 0 then
DESTROY excel
// error processing
MessageBox("error", "error connecting to excel")
return
end if
try
excel.Workbooks.Open("myfile.xls")
catch (RuntimeError lRTE_any)
excel.DisconnectObject()
DESTROY excel
// error processing
MessageBox("error", "error opening input file")
return
end try

excel.Range("C1").Select()
excel.ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]" // the row/col indexes are
relative to the current cell. In out case, row is the same
excel.Cells.Select()
excel.Selection.Columns.AutoFit()
excel.Range("A1").Select()

try
if FileExists(ls_save_pathname) then FileDelete(ls_save_pathname)
excel.ActiveWorkbook.SaveAs(ls_save_pathname, -4143) // -4143 = xlNormal
and xlWorkBookNormal
catch (RuntimeError lRTE_any2)
excel.ActiveWorkbook.Close(FALSE)
excel.DisconnectObject()
DESTROY excel
// error processing
MessageBox("error", "error saving output file")
return
end try

excel.ActiveWorkbook.Close(FALSE)
excel.DisconnectObject()
DESTROY excel

That lack of the possibility to tell PB's SaveAs() method to export a column
or compute as a formula (for example, through some XLS export specification
in the DW definition or through a certain column's property or directly
entered formula text; expressions) is the reason to submit an ISUG
enhancement request. It would be something very useful to the end
application users - sometimes they wish to further analyze and modify the
result exported from PB to XLS.

Good luck!
Ivaylo

"Paul Horan[Sybase]" <phoran_remove@remove_sybase.com> wrote in message
news:4a5e36e2$1...@forums-3-dub.sybase.com...