×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Automation - not saving all rows from existing spreadsheet?

thread184-1826109

Excel Automation - not saving all rows from existing spreadsheet?

Excel Automation - not saving all rows from existing spreadsheet?

(OP)
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 Smileing

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

RE: Excel Automation - not saving all rows from existing spreadsheet?

(OP)
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 Smileing

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

RE: Excel Automation - not saving all rows from existing spreadsheet?

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

RE: Excel Automation - not saving all rows from existing spreadsheet?

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

Hmmmmm?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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!

RE: Excel Automation - not saving all rows from existing spreadsheet?

(OP)
This is Excel 2000

Regards

Griff
Keep Smileing

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

RE: Excel Automation - not saving all rows from existing spreadsheet?

Just read the help of COPY TO regarding XL5:

Quote (VFP help)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close