Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

datawindow saveas to excel

3 posts in DataWindow Last posting was on 2009-07-16 08:02:07.0Z
mgo Posted on 2009-07-15 15:45:54.0Z
Sender: 22f0.4a5df5b1.1804289383@sybase.com
From: mgo
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: datawindow saveas to excel
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a5df9b2.25ce.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 15 Jul 2009 08:45:54 -0700
X-Trace: forums-3-dub.sybase.com 1247672754 10.22.241.188 (15 Jul 2009 08:45:54 -0700)
X-Original-Trace: 15 Jul 2009 08:45:54 -0700, forums-3-dub.sybase.com
Lines: 23
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89639
Article PK: 418827

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]" <phoran_remove Posted on 2009-07-15 20:06:58.0Z
From: "Paul Horan[Sybase]" <phoran_remove@remove_sybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4a5df9b2.25ce.1681692777@sybase.com>
Subject: Re: datawindow saveas to excel
Lines: 32
Organization: Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5e36e2$1@forums-3-dub.sybase.com>
Date: 15 Jul 2009 13:06:58 -0700
X-Trace: forums-3-dub.sybase.com 1247688418 10.22.241.152 (15 Jul 2009 13:06:58 -0700)
X-Original-Trace: 15 Jul 2009 13:06:58 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89640
Article PK: 418829

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.25ce.1681692777@sybase.com...
>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...


Ivaylo Ivanov Posted on 2009-07-16 08:02:07.0Z
From: "Ivaylo Ivanov" <n0_S_p_a_m_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4a5df9b2.25ce.1681692777@sybase.com> <4a5e36e2$1@forums-3-dub.sybase.com>
Subject: Re: datawindow saveas to excel
Lines: 108
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5ede7f@forums-3-dub.sybase.com>
Date: 16 Jul 2009 01:02:07 -0700
X-Trace: forums-3-dub.sybase.com 1247731327 10.22.241.152 (16 Jul 2009 01:02:07 -0700)
X-Original-Trace: 16 Jul 2009 01:02:07 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89645
Article PK: 418833

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...
> 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.25ce.1681692777@sybase.com...
>>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...
>
>