Trapping an Excel import error

[Mrall]

I have an import routine that imports an excel spreadsheet. It works just fine unless the user forgets to do a save as Excel 5.0/95 Workbook file format. The program hangs up and corrupts the database file. Is there a way to error trap for this or detect the file type before importing.

Thanks

 

[brigmar]

Without getting into verifying the excel file itself against the BIFF5/7 specifications, I'd suggest importing the file to a temporary cursor first with error trapping.

If the file imports to the temporary cursor without errors, then append to the main table.

  llOK = .T.
  TRY
    SELECT tempcursor
    APPEND FROM (lcXLSFile) XLS
  CATCH to oError
    llOK = .F.
  ENDTRY
  IF m.llOK
    *!* import from tempcursor to main table
  ELSE
    *!* There was an error importing to the tempcursor
  ENDIF

 

[JRB-Bldr]

It works just fine unless the user forgets to do a save as Excel 5.0/95 Workbook file format.

Since you mention doing a SaveAs, I assume that you are doing your Import through Excel Automation instead of by using APPEND FROM....

If you are using Excel Automation, then why should the user need to be involved in the SaveAs format? You can have your Excel Automation do that for you programatically.

If you are not using Excel Automation, then perhaps if you explain the process you are using for your Excel Import, we can better target our responses.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Mrall,

You asked if you can trap the error. Yes, you can:

lnError = 0
TRY
  SELECT MyTable
  APPEND FROM SomeFile.xlsx TYPE XL8
CATCH TO loErr
  lnError = loErr.ErrorNo
ENDTRY

IF lnError = 1661
  * Invalid format
ELSE
  * All is well
ENDIF

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mrall]

I would like to do the Excel Automation but have no clue as to how this is done. I have Excel 2003 but some of the users have Excel 2010. Does this have any effect on the outcome? The big question is, how do I do this?

Thanks

 

[Nigel Gomm]

start here

http://fox.wikis.com/wc.dll?Wiki~ExcelAutomation

and it may be worth your while investing in this

http://www.amazon.com/Microsoft-Office-Automation-Visual-FoxPro/dp/0965509303

some things may change between versions of excel... what are you trying to achieve?

nigel

 

[Mike Lewis]

Mrall,

I gave you an answer that doesn't involve Excel automation.

You asked how to trap the error, and that's what I gave you. If my solution doesn't answer your question, perhaps you could tell us why.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy