Importing an Excel spreadsheet in VFP using Excel

De native IMPORT function of Visual Foxpro doesn't allways do a good yob. For example, sometimes it converts numbers to date values (01-01-1900).
The code below uses office automation to let Excel itself export the spreadsheet to dBase format. Also, you don't have to know in which version of Excel the spreadsheet is created, like you have to with the IMPORT function.

The code does not work with Excel 2007, because Microsoft decided to say bye bye to the dBase export filters.

Run the .PRG file like:

DO XLS2DBF WITH 'MySpreadsheet.xls', 'MyTable.dbf'

* XLS2DBF.PRG
*
* Function accepts 2 parameters
* 1) cSheet is the Excel sheet that is to be exported
* 2) cExportFile is the filename of the exportfile

PARAMETERS cSheet, cExportFile

IF PCOUNT() = 1
* Create a temp export file
cExportFile = SYS(2023) + "\" + SYS(3) + ".tmp"
ENDIF

LOCAL lErrorCatched, oExcelWorkbook, nFileFormat

nFileFormat = 11 && Excel Saveas constant for dBase IV format. Not available in Excel 2007 though...
lErrorCatched = .F.

* Can we get to Excel or what?
TRY
oExcelObject = CREATEOBJECT('Excel.Application')
CATCH
lErrorCatched=.T.
ENDTRY

IF lErrorCatched
RETURN .F.
ELSE

oExcelWorkbook = oExcelObject.Application.Workbooks.Open(cSheet)

* When columns in Excel are to small to show all the data, Excel also cuts them off when exporting!
* The code below formats the columns to be wide enough...
oExcelObject.Cells.Select
oExcelObject.Selection.Font.Size = 11 && dBase IV
oExcelObject.Selection.Columns.AutoFit
oExcelObject.Selection.Font.Size = 8

oExcelObject.cells(1).select && makes sure the whole sheet is exported and not some selected range (or whatever goes wrong with that from time to time...)
oExcelObject.DisplayAlerts = .F. && prevents overwrite message
oExcelWorkbook.SaveAs(cExportFile, nFileFormat)
oExcelWorkbook.close

* When opening a .DBF file created by Excel, there is no codepage
* VFP opens up the codepage dialog in order to choose a codepage, but we don't want that
* SET CPDIALOG OFF won;t help, because then there stil is no codepage for the .DBF file
* Luckily, VFP provides CPZERO.PRG, with can update a .DBF with a codepage
* You could do: DO CPZERO WITH cExportFile, 850
* But instead, the code below is a little piece of the CPZERO code that does the job, so we don't
* have to include CPZERO.PRG itself.
LOCAL varcpbyte, varfp_in, varbuf
varcpbyte = 2
varfp_in = FOPEN(cExportFile,2)
IF varfp_in > 0
* First check that we have a FoxPro table...
varbuf=FREAD(varfp_in,32)
IF (SUBSTR(varbuf,1,1) = CHR(139) OR SUBSTR(varbuf,1,1) = CHR(203);
OR SUBSTR(varbuf,31,1) != CHR(0) OR SUBSTR(varbuf,32,1) != CHR(0))
=fclose(varfp_in)
RETURN .F.
ELSE
* now poke the codepage id into byte 29
=FSEEK(varfp_in,29)
=FWRITE(varfp_in,CHR(varcpbyte))
=FCLOSE(varfp_in)
ENDIF
ELSE
RETURN .F.
ENDIF

* If no exportfile is specified, open the data as a temporary table to undertake further action
IF PCOUNT() = 1
cAlias = "C"+SYS(3)
USE (cExportFile) IN 0 ALIAS (cAlias)
* BROWSE NORMAL NOWAIT && if you want
ENDIF

* clean up Excel object
oExcelObject = .NULL.
RELEASE oExcelObject