Steve,
Here's the code I wrote to work around the limitation. Not pretty and much slower than the native command but it works.
Hope this helps.
Daniel
* Program...........: Copy2Xls.prg
* Author............: Daniel Gramunt
*) Description.......: Wrapper to the native COPY TO TYPE XL5 command in order to handle the
*) : VFP limitation of max. 16'383 records. If the number of records exceed
*) : this limitation, we COPY TO individual excel files and "assemble" the
*) : destination sheet using Automation.
*) :
*) : Assumes that MS Excel (Excel 97 or higher) is installed on the
*) : user's machine.
*) :
*) : Returns the number of exported records if successful
*) : otherwise -1 = 1st parameter missing or wrong type
*) : -2 = no table open in current workarea
*) :
* Calling Samples...: Copy2Xls("c:\temp\bidon.xls")
* :
* Parameter List....: tcExcelFile - Path\Filename of the Excel file to be created
* Major change list.:
*--------------------------------------------------------------------------------------------------
LPARAMETER tcExcelFile
*-- check parameters ------------------------------------------------------------------------------
IF VARTYPE(tcExcelFile) <> "C"
??CHR(7)
WAIT WINDOW NOWAIT "Parameter : Parameter missing or wrong type (Expecting 'C')"
RETURN -1
ENDIF
LOCAL lcAlias, lnRecords, lnFields, lnRetVal, loXls, lnNumberOfCopies, i, loDestinationWorkbook
LOCAL loDestinationSheet, lnCopyPos, loTempWorkBook, loTempSheet, loDestinationSheet
lcAlias = ALIAS()
IF EMPTY(lcAlias)
??CHR(7)
WAIT WINDOW NOWAIT "No table is open in the current workarea"
RETURN -2
ENDIF
#DEFINE cnXlsRecordLimitation 16383
lnRecords = RECCOUNT()
lnFields = FCOUNT()
*-- check if number of records exceed VFP limitation
IF lnRecords <= cnXlsRecordLimitation
*-- we're within the limit, so we simply perform the native COPY TO command
COPY TO (tcExcelFile) TYPE XL5
lnRetVal = _TALLY
ELSE
*-- we need to loop through and COPY TO in chunks of cnXlsRecordLimitation.
*-- After that we "assemble" the individual Excel files
loXls = CREATEOBJECT("excel.application")
loXls.DisplayAlerts = .f. && avoid confirmation messages from excel (similar to SET SAFETY)
lnNumberOfCopies = CEILING(lnRecords / cnXlsRecordLimitation)
#DEFINE cnTempExcelFile 1
#DEFINE cnNumberOfRecords 2
DIMENSION laExportFiles[lnNumberOfCopies, 2]
GO TOP
*-- first we iterate through the table and COPY TO TYPE XL5
FOR i = 1 TO lnNumberOfCopies
IF i = 1
*-- the first COPY TO will be to the destination file
laExportFiles[i, cnTempExcelFile] = tcExcelFile
ELSE
*-- all other copies go into a temp excel file
laExportFiles[i, cnTempExcelFile] = AddBs(SYS(2023)) + SYS(3) + "_" + TRANSFORM(i) + ".XLS"
ENDIF
*-- copy the data to a temp workbook
COPY TO (laExportFiles[i, cnTempExcelFile]) TYPE XL5 NEXT cnXlsRecordLimitation
*-- store the number of copied records to array
laExportFiles[i, cnNumberOfRecords] = _TALLY
*-- move pointer to next record
IF NOT EOF()
SKIP
ENDIF
ENDFOR &&* i = 1 TO lnNumberOfCopies
*--------------------------------------------------------------------------------------------------
*-- step 2
*-- merge the created Excel files
*-- We open each worksheet and copy/paste the contents to the destination worksheet
*-- get an object reference and open first workbook
loDestinationWorkbook = loXls.Workbooks.open(laExportFiles[1, cnTempExcelFile])
*-- get an object reference to the active worksheet
loDestinationSheet = loDestinationWorkbook.activeSheet
*-- pointer to the row in destination sheet where the data will be pasted
lnCopyPos = laExportFiles[1, cnNumberOfRecords] + 2
FOR i = 2 TO lnNumberOfCopies
*-- open the temp workbook
loTempWorkBook = loXls.Workbooks.open(laExportFiles[i, cnTempExcelFile])
loTempSheet = loTempWorkBook.activeSheet
*-- activate temp worksheet
loTempSheet.Activate()
*-- select the data (without first row)
loTempSheet.Range(loTempSheet.Cells(2, 1), loTempSheet.Cells(laExportFiles[i, cnNumberOfRecords]+1, lnFields)).Select
*-- copy selection into clipboard
loXls.Selection.Copy
*-- activate destination sheet
loDestinationSheet.Activate()
*-- select destination cell
loDestinationSheet.Range(loDestinationSheet.Cells(lnCopyPos, 1 ), loDestinationSheet.Cells(lnCopyPos, 1)).Select
loDestinationSheet.Paste
*-- update pointer to next destination cell
lnCopyPos = lnCopyPos + laExportFiles[i, cnNumberOfRecords]
loTempWorkbook.close()
*-- delete temp workbook
IF FILE(laExportFiles[i, cnTempExcelFile])
DELETE FILE (laExportFiles[i, cnTempExcelFile])
ENDIF
ENDFOR &&* i = 1 TO lnNumberOfCopies
#DEFINE xlWorkbookNormal -4143 && used by SaveAs() to save in current version
*-- save Excel file
loDestinationSheet.saveAs(tcExcelFile, xlWorkbookNormal)
*-- close Excel
loXls.application.quit()
lnRetVal = RECCOUNT(lcAlias)
ENDIF
RETURN lnRetVal
Daniel