LOCAL; lcFileToImport AS CHARACTER,; lcDBF AS CHARACTER *we will call a func, means nothing to code the file inside, hard to reuse :) lcFileToImport = '"c:\colin.xlsx"' *or *lcFileToImport = GETFILE('XLS, XLSX') *DO maybe, maybe not *DO xls_import *I prefer: lcDBF = lxls_import(lcFileToImport) FUNCTION xls_import *Import: Excel Filename *Export: DBF of table imported, EMPTY() if failed to import *LPARAMETER instead of PARAMETERS declares params as local to the function LPARAMETERS; tcFile ** Replaces VFP Import Command for XLS. VFP Command does not accept xlsx file formats ** First try the standard import. ** If it failes with error code 1661 (Microsoft Excel file format is invalid.) then open Excel and resave with format 39( xlExcel5, xlExcel7 ) ** Try to import again. ** If it succeeds, return the name of the table ** If it still fails, return an empty string. Calling program will check for EMPTY() to determine success LOCAL; lcReturn lcReturn = "" *replace trycatch for better example *Try .. EndTry bei IntelliScript LOCAL; loException AS EXCEPTION TRY *To test IMPORT FROM (tcFile) XLS lcReturn = DBF() CATCH TO loException WHEN loException.ERRORNO=1661 * Error to catch LOCAL; lcNewFile AS CHARACTER,; tmpoxl AS OBJECT tmpoxl=CREATEOBJECT("excel.application") tmpoxl.DisplayAlerts = .F. tmpoxl.APPLICATION.WorkBooks.OPEN(tcFile) *nope * lcNewFile = "c:\tempdl\" + JUSTFNAME(tcFile) *better lcNewFile = ADDBS(SYS(2023)) + JUSTFNAME(tcFile) tmpoxl.activeworkbook.SAVEAS(lcNewFile,39) && avoid magic numbers, declare tmpoxl.QUIT tmpoxl= .NULL. TRY IMPORT FROM (lcNewFile) XLS lcReturn = DBF() CATCH *catch anything FINALLY *do it anyway DELETE FILE (lcNewFile) ENDTRY CATCH TO loException * other error FINALLY * ENDTRY RETURN lcReturn *end the function, just for better style ENDFUNC &&xls_importanyway, if you have it open with M$ Excel its so easy to import (assuming you have a table open to import) :
LOCAL; lcFileToImport AS CHARACTER,; lcDBF AS CHARACTER *we will call a func, means nothing to code the file inside, hard to reuse :) lcFileToImport = '"c:\colin.xlsx"' *or *lcFileToImport = GETFILE('XLS, XLSX') *DO maybe, maybe not *DO xls_import *I prefer: lcDBF = lxls_import(lcFileToImport,'Add your taget table here') FUNCTION xls_import *Import: Excel Filename *Import TableName *Export: number of records imported, ISNULL on error *LPARAMETER instead of PARAMETERS declares params as local to the function LPARAMETERS; tcFile,; tcTable ** Replaces VFP Import Command for XLS. VFP Command does not accept xlsx file formats ** First try the standard import. ** If it failes with error code 1661 (Microsoft Excel file format is invalid.) then open Excel and resave with format 39( xlExcel5, xlExcel7 ) ** Try to import again. ** If it succeeds, return the name of the table ** If it still fails, return an empty string. Calling program will check for EMPTY() to determine success LOCAL; lnReturn AS INTEGER,; tmpoxl AS OBJECT LOCAL ARRAY; laData(1,1) lnReturn = .NULL. *replace trycatch for better example *Try .. EndTry bei IntelliScript LOCAL; loException AS EXCEPTION TRY *To test *Edit: removed superflous CATCH next line *!* CATCH TO loException WHEN loException.ERRORNO=1661 * Error to catch LOCAL; tmpoxl=CREATEOBJECT("excel.application") tmpoxl.DisplayAlerts = .F. tmpoxl.APPLICATION.WorkBooks.OPEN(tcFile) #DEFINE xlLastCell 11 &&last cell loLastCell = loExcel.ActiveSheet.Cells.SpecialCells(xlLastCell) laData = loExcel.ActiveSheet.RANGE(loExcel.ActiveSheet.Cells(1,1),m.loLastCell).VALUE *maybe some loop trough the data to kill formating problems .... here INSERT INTO (tcTable); FROM ARRAY laData lnReturn = _TALLY CATCH TO loException * other error FINALLY * ENDTRY RETURN lnReturn *end the function, just for better programming ENDFUNC &&xls_import