Copy To xxx.xls XL5 truncating to 16384 rows 1

[robsuttonjr]

When I have a cursor/table larger than 16384 and try to copy to an excel file, it gets truncated to 16384. Is there something I need to do to fix this or is this just something I have to live with?

Command:
Copy To xxx.xls XL5

 

[Mike Gagnon]

FAQ184-2617
Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[rgbean]

Note: If you can't use Mike's Automation solution (everyone doesn't have Excel), consider using:
Copy To xxx.dbf FOX2X
or
Copy To xxx.txt CSV
Excel can load up either of these file types.

OR upgrade to VFP 8.0, it'll copy 65K records in XLS format.

Rick

 

[robsuttonjr]

Thanks to both of you (Mike and Rick). I apologize for not looking in FAQ. Mike is there any limitation on your program as far as records? Rick I have ordered Fox 8 but most of you guys out there beat me to the punch, so I have to wait a bit longer! (Backordered)

 

[rgbean]

Yes, even Excel XP (2002 - version 10.0) has a documented limit of 65,536 rows by 256 columns. So Mike's code can't exceed thoose limits.

Rick

 

[MrDontKnowNothing]

sorry mike,

but i've tried your "Copy2Xls.prg" in order to solve the 16384 problem.

1. thanks for offering a workaround
2. unfortunatelly, i can't use this due to an error at this line =>

loXls.ActiveSheet.saveAs(tcExcelFile, xlWorkbookNormal)

errocode: 0x800a03ec and something about com-stuff.

i call it with these parameters after a select statement witch puts the result into a cursor:
Copy2xls("c:\temp\xlstest.xls)


greetings

alex

 

[Mike Gagnon]

MrDontKnowNothing


Did you define it first?

#DEFINE xlWorkbookNormal -4143

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[jspellman]

Rob,

Try this procedure.

**********************
*procedure reccount
************************
procedure reccount

use (filename)
go bott
store recno() to m.reccount
if m.reccount >= 16,382
copy to file (filename).csv &&EXCEL WILL ONLY OPEN 65536 RECORDS
else
copy file to (filename).xls type xl5
endif
end proc

I posted this a while ago for a similar issue. This worked for me because I was working with table varying in size from 10k in records to 100k+.

Good luck.

John

 

[DanaAnderson]

Assuming that you do not have more records than the version of Excel allows, you can simple do this:

COPY TO Filename.xls type Fox2X. That way the file is assumed to be an Excel file by Windows, Explorer shows the Excel icon for the file, and the file is opened in Excel if you double click it or will show up in the Open dialog with no extra effort and Excel will understand the format and import it just fine.

As was mentioned by another, unfortunately Excel does have its limits, and they are far more restricting that the limits in VFP!

Dana

 

[torturedmind]

COPY TO Filename.xls type Fox2X. That way the file is assumed to be an Excel file by Windows, Explorer shows the Excel icon for the file, and the file is opened in Excel if you...

in my case, this is not true. resulting file still has a fox icon and opens up in VFP6? i guess Win98SE doesn't work as what OS you guys have. (i know, am still in the stone age)

kilroy

 

[MrDontKnowNothing]

morning folks ;-),

mike:
the "xlWorkbookNormal" was defined in your .prg, wasn't it? anyway, i tried it with hardcoding and got the same result.
sorry for my ignorance, but what is this #define standing for?
btw: i use various versions of excel, but non older than the officeXP release.

john:
i've tried it with "xl5", but without any difference. still those f***ing 16,382 records.

kilroy, dana:
unfortunatelly, i need this excel sheet for an automation process. therefore i can't youse your solution.

cheers

alex

 

[Mike Gagnon]

sorry for my ignorance, but what is this #define standing for?

Normally you would use a #DEFINE that a similar way you would use a Public variable (not recommended), but you would put it in a .h file (a header file) which sits outside of your application and every where you use that constant, VFP (at compile time) will replace the "xlWorkbookNormal" with the value you defined it with. The advantage would be that if you want to change the value and recompile, you only need to change it in one place.
Honestly I'm not sure why the code is not working for you, I tried it on XP with Excel XP (that is all I have access to) and it works for me.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[MrDontKnowNothing]

hi mike,

i was more intrested in where this value -4143 is comming from...
btw, i do have good hand for things, which work generally, but not with me.
therefore, i should become a tester. ;-)

thanks anyway, i gonna check this on monday. have a nice and relaxed weekend!

alex

 

[Mike Gagnon]

i was more intrested in where this value -4143 is comming from...

If you follow the instructions in faq184-4248 and retreive all the Excel constants, you will find that the "xlWorkbookNormal" is assign the value -4143, but since VFP knows nothing about Excel contants, you need to define it. As to why it is that value, I don't know.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.