COPY to XLS creates bad Excel worksheet

[ganj]

I have a cursor with 128+ columns that needs to go into an Excel worksheet.

This works fine but when the number of columns is 100+, I get an Excel file that is bad, i.e. data not placed in the column where it belongs but in an adjacent column. I am running into the same problem, regardless of Excel format (XLS vs. XL5).

I'm using VFP8 with SP1 applied, Excel XP with SP2.

Does this mean VFP cannot create Excel worksheets with more than 100 columns? Or what has gone wrong here?

 

[rgbean]

How are the columns in the cursor defined? (Structure) Do you have any Memo fields? How many records are in the cursor?

Rick

 

[ganj]

Rick,

The cursor is based on a remote view (I'm using SQL Server). There are no memo fields in the cursor; the number of records typically is between 2 and 20. Regardless of nr of records, I consistently run into this problem.

Thanks, Ganj

 

[Dan Freeman]

Have you tried copying to a DBF and then copying that to an XLS? (Of course, if you COPY TO ... TYPE FOXPLUS Excel can read the dbf natively so the copy to XLS may be superfluous.)

I could see SQL Server's VarChar (etc.) fields confusing things.

 

[ganj]

@danfreeman:

copying to a dbf is not really a good solution because this truncates field names to 8 chars. I really need the full names.
Yes, the SQL Server columns are all varchar type, but the Excel worksheet contains all field types: datetime, logical and numeric.

 

[baltman]

See faq184-4704 Export a Formatted Table to Excel using HTML. I'm sure a little tweaking and you'll be quite happy.

Brian