Copying to Excel 1

[rtipton12]

Hi.

I have several vfp programs that pull data from sql server using odbc and then copies the result cursor to an Excel file. My problem is I have a couple that are pulling 17,000+ records and only 16,384 get copied to Excel. Evidently this is a compatability issue, but is there a way to copy all of the data into Excel?

Any help is appreciated.

rt

 

[jimoo]

rt

I assume you are getting the data from SQLSever and placing it into a VFP Cursor. If so, the first thing you should check (you may have) is that you really have 17000+ records in the VFP cursor.

I am unaware of any instances where a VFP cursor does not get written to excel propery with a copy command.

Are you using a COPY TO TYPE XL5 type of command from VFP?

What version of VFP / Excel?

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[baltman]

There are several ways, but I suggest using faq184-4704, Export a Formatted Table to Excel using HTML, if you have Office 2000 +.

Otherwise, you can use type DIF or type DELIM or, if you're using VFP 7, type CSV.

There are a couple other FAQs that might interest you if these suggestions don't work.

Brian

 

[KeithTrangmar]

The issue is actually in Excel itself, which has a limit of 16384 rows. The solution is probably to export the table in 16k blocks to separate files, and if you're feeling clever you could then automate Excel & import them back in as separate sheets within the same workbook.

Keith.

 

[StewartUK]

I did a keyword search for excel 16384, setting the search criteria to all words, and got some results.

Why not try this yourself.

Hope that helps,

Stewart

 

[rtipton12]

My cursor has about 34,000 records

Command: COPY TO file01 TYPE XL5
VFP Version: tried both VFP 3 and 7

My program copies a cursor to an excel file, opens the file, does numerous formatting commands and saves the excel file.

Through some research I have found that VFP 8 will copy the file properly (all 34,000). My problem is that my company does not own VFP 8.


Thanks to all of you for your suggestions. I will look at the knowledgebase again and see what I can find.

rt

 

[baltman]

The most straight forward way is to copy to file TYPE DIF, which Excel CAN open (and is usually the default app).

DIF stands for Data Interchange Format.

If you have VFP 7, type CSV works too.

You can automate an open/save with Excel if you have-to-have an XLS extension.

Brian

 

[baltman]

rtipton12,

I just noticed you mention that you open and format the export.

I can assure you that changing your program to pass mostly formatted information using HTML will be much faster than formatting in Excel, as long as you are using Office 2000 or higher.

About the only things I automate in Excel are fit columns/rows and set print range. Please look at FAQ184-4704 and let me know if you have any questions.

It isn't as hard as it looks!

Brian