Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
LPARAMETER tcExcelFile, tlDbf, tlNoFieldNames
#INCLUDE FoxPro.h
#DEFINE xlWorkbookNormal -4143 && used by SaveAs() to save in current Excel version
#DEFINE ccErrorNoParameter "Parameter < tcExcelFile > : Parameter missing or wrong type (Expecting 'C')"
#DEFINE ccErrorNoTableOpen "No table is open in the current workarea"
#DEFINE ccErrorToManyRows "Number of records (" + ;
ALLTRIM(TRANSFORM(lnRecords, "999,999,999")) +;
") exceed max. number of Excel rows (" -;
ALLTRIM(TRANSFORM(lnXlsMaxNumberOfRows, "999,999,999"))+;
")"
*-- check parameter
IF VARTYPE(tcExcelFile) <> "C" OR EMPTY(tcExcelFile)
??CHR(7)
WAIT WINDOW NOWAIT ccErrorNoParameter
RETURN -1
ELSE
tcExcelFile = ForceExt(tcExcelFile, "XLS")
ENDIF
*-- make sure that we have a table/cursor in the selected workarea
IF EMPTY(ALIAS())
??CHR(7)
WAIT WINDOW NOWAIT ccErrorNoTableOpen
RETURN -2
ENDIF
LOCAL loXls, lnXlsMaxNumberOfRows, lnRecords, lnRetVal, lcTempDbfFile
loXls = CREATEOBJECT("excel.application")
*-- suppress Excel alerts and messages (similar to SET SAFETY OFF)
loXls.DisplayAlerts = .f.
*-- get number of max. rows from Excel. Before we can count the rows in a
*-- worksheet, we need to add a workbook.
loXls.workbooks.add()
lnXlsMaxNumberOfRows = loXls.ActiveWorkBook.ActiveSheet.Rows.Count - 1 && 1 header row
lnRecords = RECCOUNT()
*-- check if the number or records exceeds Excel's limit
IF lnRecords > lnXlsMaxNumberOfRows
??CHR(7)
WAIT WINDOW NOWAIT ccErrorToManyRows
*-- close Excel
loXls.application.quit()
RETURN -3
ENDIF
*-- respect SET SAFETY
IF SET("SAFETY") = "ON" AND FILE(tcExcelFile)
IF MESSAGEBOX(tcExcelFile + " already exists, overwrite it?",;
MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON2) = IDNO
*-- user selected < No > so we bail out
*-- close Excel
loXls.application.quit()
RETURN -4
ENDIF
ENDIF
IF tlDbf
lcTempDbfFile = AddBs(SYS(2023)) + SYS(3) + ".DBF"
COPY TO (lcTempDbfFile) TYPE FOX2X AS 850
ELSE
lcTempDbfFile = AddBs(SYS(2023)) + SYS(3) + ".CSV"
COPY TO (lcTempDbfFile) TYPE CSV
ENDIF
lnRetVal = _TALLY
*-- open exported CSV file
loXls.Application.Workbooks.Open(lcTempDbfFile)
IF tlNoFieldNames
loXls.ActiveSheet.Range("1:1").delete
ENDIF
*-- save as Excel file
loXls.ActiveSheet.saveAs(tcExcelFile, xlWorkbookNormal)
*-- delete CSV file
IF FILE(lcTempDbfFile)
DELETE FILE (lcTempDbfFile)
ENDIF
*-- close Excel
loXls.application.quit()
RETURN lnRetVal