Copy To Command to XLS File Type

[davepaugh]

Is there still a maximum record number when using Copy To command to a XLS file type in Visual FoxPro 6.0? I can't get more than 16383 records to copy.

 

[CAE]

Hi Dapper.<br>
<br>
Does EXCEL (.XLS) have a maximum record size? That would<br>
be my question. Good luck and let us know if you find<br>
out anything.

 

[mmhorky]

I believe that Excel will only accept 65536 rows.

I've received error stating that as the limit.

I do not think you should have problem with 17000 rows.

I am not familar with FoxPro and any limitations it might have with the Copy To command.

Hope that helps.

 

[chpicker]

You're right, Dapper. I just tried the command with a table that has 24k records, and I got exactly 16383 plus the header row for a total of 16384 (exactly 16k). Excel 2000 (version 9) can support up to 65536 rows (64k), but the format that FoxPro exports is only version 5. It's possible that the format for XL5 could only support up to 16k.

Does anyone know if VFP 7 addresses this, or are we still stuck in version 5 compatibility? (I don't have 7 yet)

Ian

 

[fluteplr]

Just checked the 7.0 docs. it still indicates 5.0 format so I assume the 16383 limit is still in place.

 

[marsh]

The limit only applies to .xls files, so the way around the limit is to create .csv files instead. Unfortunately, if you need the workbook/worksheet functionality you're out of luck. Also, you may need to train your users in how to open a .csv in excel... Of course, once it is opened in excel, you can save it as an xls.

 

[DEDMOD]

A guy my wife works with had a similar problem and with some help from the local FoxPro users group came up with a solution which you might or might not find useful

Hey Gang, Thanks for all your input to my spreadsheet problem. Below is the final code that works. It is a combination of Cindy's and Gail's code with a few changes. It assumes temp1 and temp2 already exist and are filled with the data for the two pages of the workbook. _ccFile is the full path and name you want the workbook saved as.

*!* Now create the spreadsheet
oExcel = CREATEOBJECT([Excel.Application])
WITH oExcel
*!*		.Visible = .T.	&& Do while testing, remove in live app
	.workbooks.add

	*!* Put data in first sheet
	.Sheets([Sheet1]).Select()
	.ActiveSheet.Cells(1, 1).Select()

	*!* Get the data and paste it in
	SELECT Temp1
	counter = 1000
	oldcounter = 0
	loopcnt = 2	&& added to make space for the headers each pasting has
	DO WHILE counter <= RECCOUNT()
		SELECT * FROM temp1 WHERE recno() > oldcounter and recno() <= counter INTO CURSOR temp3
		_Vfp.DATATOCLIP( , , 3)
		.ActiveSheet.Paste()
		.ActiveSheet.Cells((counter + loopcnt), 1).Select()
		SELECT temp1
		DO CASE
			CASE counter < RECCOUNT() - 1000
				oldcounter = counter
				counter = counter + 1000
			CASE counter < RECCOUNT() 
				oldcounter = counter
				counter = RECCOUNT()
			OTHERWISE
				counter = RECCOUNT() + 1
		ENDCASE
		loopcnt = loopcnt + 1
	ENDDO

	*!* Put data in second sheet
	.Sheets([Sheet2]).Select()
	.ActiveSheet.Cells(1, 1).Select()

	SELECT Temp2
	counter = 1000
	oldcounter = 0
	loopcnt = 2
	DO WHILE counter <= RECCOUNT()
		SELECT * FROM temp2 WHERE recno() > oldcounter and recno() <= counter INTO CURSOR temp3

	*!* Get the data and paste it in
		_Vfp.DATATOCLIP( , , 3)
		.ActiveSheet.Paste()
		.ActiveSheet.Cells((counter + loopcnt), 1).Select()
		SELECT temp2
		DO CASE
			CASE counter < RECCOUNT() - 1000
				oldcounter = counter
				counter = counter + 1000
			CASE counter < RECCOUNT() 
				oldcounter = counter
				counter = RECCOUNT()
			OTHERWISE
				counter = RECCOUNT() + 1
		ENDCASE
		loopcnt = loopcnt + 1
	ENDDO

	*!* Save and close
	.ActiveWorkbook.SAVEAS (_ccFile)
	.ActiveWorkbook.CLOSE()

ENDWITH

oExcel.Quit()
oExcel = .NULL.
RELEASE oExcel

Tom Achtenberg
Database Administrator
Food for the Hungry/US


Dave Dardinger