Excel Automation - not saving all rows from existing spreadsheet?

[GriffMG]

I have a process that creates a table with 48,000 records, I copy that to an xls workbook less a few columns.

COPY TO (M.FILENAME) XL5 FIELDS EXCEPT DRGNO, FLAG, LINENO

If I do no more than that, there are 48,000 rows in the resultant worksheet

If I then open the xls using automation in VFP, change the column headers to match the soft ones in my app
and save the xls again there are only 16,384 in the spreadsheet.

If I open the initial export manually, not doing the automation bit, and paste the header line in and then save it
I get 48,000 rows.

What am I missing?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[GriffMG]

I think I have it.

I was using this to save the modified file

OEXCEL.ACTIVEWORKBOOK.SAVE

I changed it to this

OEXCEL.ACTIVEWORKBOOK.SAVEAS( m.FILENAME, -4143)

-4143 is the xlVBA const for xlNormal

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[ggreen61]

You might try using the VFPx Workbook class that I created (

https://github.com/ggreen86/XLSX-Workbook-Class).

You can use the method SaveTableToWorkbookEx() which has a parameter for selecting what fields to be exported. This method also allows you to specify what the column header title will be. You can also set a parameter for freezing the top row. This class supports creating xlsx files without any automation (all VFP code to directly write to the xlsx).

Greg

 

[SkipVought]

Interesting that the versions of Excel associated with 16,384 maximum rows are
Excel 5 & Excel 95.

Hmmmmm?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[GriffMG]

This is Excel 2000

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

Just read the help of COPY TO regarding XL5:

Though you can export a maximum of 65,535 rows, which includes one row reserved for the field header, versions of Excel earlier than 8.0 (Excel 97) display only the first 16,384 rows and cannot import files containing more than 32,767 rows.

So VFP is capable to put more rows into an Excel 5.0 file than some versions of excel actually can process. The help has no specific note on Excel2000, but as you see there are problems. I would guess if an Excel process reads in an Excel 5 file, it also by standard outputs one, with less rows than VFP can support, and there ou have a feasible explanation.

Saving as a sepcific newer Excel version obviously helps.

Chriss