Excel Automation - not saving all rows from existing spreadsheet?

GriffMG · Sep 22, 2023

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

Code:

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 · Sep 22, 2023

I think I have it.

I was using this to save the modified file

Code:

OEXCEL.ACTIVEWORKBOOK.SAVE

I changed it to this

Code:

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 · Sep 22, 2023

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 · Sep 22, 2023

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

Hmmmmm?

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"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 · Sep 22, 2023

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 · Sep 22, 2023

Just read the help of COPY TO regarding XL5:

VFP help said:
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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Excel Automation - not saving all rows from existing spreadsheet?

GriffMG

Programmer

GriffMG

Programmer

ggreen61

Programmer

SkipVought

Programmer

GriffMG

Programmer

Chris Miller

Programmer

Similar threads

Part and Inventory Search

Sponsor