Re: Datawindow SaveAs Excel - Not saving more than 65536 rows

[Scott Morris]

> I am using Microsoft office 2007, but the saveas function is not working
> for
> Excel 2007 and shows error. I have tried the same in design time also. The
> "Save Rows As" dialog shows only "Excel8 (97 / 2000 / XP / 2003)" and
> "Excel8 (97 / 2000 / XP / 2003) with headers" options. The Excel 2007
> option
> is not disaplayed. (Attached the "excel saveas dialog.zip which has excel
> saveas dialog.bmp file")
>
> I want the datawindow saveas function to save rows as Microsoft Excel 2007
> file for more rows. (As per the help, it should save the rows in latest
> excel version, for max of 1000000 rows).

Per PB Help (10.2.1 9004), there is no discussion of row limits nor the
latest Excel version. The "8" in Excel8 doesn't refer to a specific version
of Excel. Given that the last 10.2 MR version was released in 2005, it
would be difficult for it to support any software / specification that came
afterwards.

The saveas type of Excel8 supports versions of Excel that are limited to
sheets of a maximum of 65k rows. As the saveas dialog indicates (though
online help doesn't), Excel8 is backwards compatible with Office97. There
is no specific type (as best I know) that supports the later versions of
Excel and their larger row limits. In short, you can't do what you want
with the version of PB you are using. You can try the latest version of PB
to see if there have been any enhancements in this area. Alternatively, use
any format that does not have this limit and which Excel can open. Other
alternatives have been suggested previously (e.g., using COM) - investigate
the newsgroup archives if you want more information.

[Roland Smith [TeamSybase]]

The code that allows the DataWindow to save in Excel file format is entirely
internal to the PB runtime .dlls. It doesn't matter which version of Excel
is installed.

Microsoft started using the year as part of the name beginning with 'Excel
for Windows 95'. Internally to the program it continues to have a version
number. Version 8 is Excel 97. Excel 2007 is version 12.

"RMK.B" <rammurali...@zylog.co.in> wrote in message
news:485a6ea6@forums-1-dub...
> Hi all,
>
> I am using PowerBuilder 10.2.0 Build 7516.
>
> I am having problem with datawindow saveas function for excel8! filetype.
> I
> am using Microsoft Excel 2007 which supports a maximum of 1048576 rows in
> a
> excel sheet. The help for saveas function for excel filetype states
> excel8!
> filetype will work for "Microsoft Excel Version 8 and higher format". But,
> the datawindow saveas function for excel8! filetype is not saving more
> than
> 65536 rows. (Also I have tried Excel! filetype and Excel5! filetype of
> Saveas. But it didn't work out)
>
> My datawindow query is returning 212536 rows. When I try to save the
> datawindow rows as excel8! type, "Save Rows As Failed" error occurs. The
> saveas excel8! type is showing error if the number of rows are greater
> than
> 65536.

>
> I am using Microsoft office 2007, but the saveas function is not working
> for
> Excel 2007 and shows error. I have tried the same in design time also. The
> "Save Rows As" dialog shows only "Excel8 (97 / 2000 / XP / 2003)" and
> "Excel8 (97 / 2000 / XP / 2003) with headers" options. The Excel 2007
> option
> is not disaplayed. (Attached the "excel saveas dialog.zip which has excel
> saveas dialog.bmp file")
>
> I want the datawindow saveas function to save rows as Microsoft Excel 2007
> file for more rows. (As per the help, it should save the rows in latest
> excel version, for max of 1000000 rows).
>

> Any solutions to resolve this. Any additional EBFs required. Any
> roundabouts. Any suggestion or feedback is appreciated.
>
> TIA,
> Rammurali Krishnan B
>
>
>
>
>

[Pat Madigan]

You could try using the OLE method of saving data to excel. I can't test
that for >65K rows, as I don't have Excel 2007, but I would think that would
work.

There's some examples of this on CodeExchange, and I prefer that over the
SaveAs.

Pat

[kenbreneman]

You could try using the CSV! format. Your Excel 2007
program can open csv files directly.

> [Attachment: excel saveas dialog.zip]

[Wheeley]

All current released versions of Powerbuilder nave no support for
Office 2007. I had this confirmed by Tech support when I was trying to
use an olecontrol with the .xlsm file type. Maybe 11.5 has support,
but Tech support said no support was to e found up through 11.2. I ask
when support would be added and they said they didn't know. So you are
going to have no way of doing what you want with Excel 2007 and
SaveAs.

Wheeley

On 19 Jun 2008 07:35:18 -0700, "RMK.B"

[Bruce Armstrong [TeamSybase]]

The SaveAs functionality of the DataWindow and support for Office 2007
file formats in OLE Controls are completely seperate features.

In any event, if you use OLE Automation to transfer the data over to
Excel, you are only limited by Excel's limits.

[pattysua...@gmail.com]