Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
COPY TO TYPE XL5 limitation?
Message
From
26/10/2000 03:26:59
 
 
To
25/10/2000 15:03:54
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Miscellaneous
Thread ID:
00422500
Message ID:
00434446
Views:
15
I wrote the following wrapper to work around the limitation. Not pretty and much slower than the native command but it works.
Hope this helps.

* 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
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform