VFP. Trapping COM errors

0
votes

Trapping Excel COM errors from a VFP program.

A program is writing out an extract from several large tables to Excel using Automation. Typical instructions include :

lCell = "I1234”
.sSheet1.Range(lCell).value = cCust.Name

This generally works fine, but if cCust.name has a value such as ‘=’ (which Excel does not like), this causes an OLE COM error. Had tried using TRY .. . CATCH . . . ENDTRY to trap this error, but the OLE COM error still reports.

I suppose that I could find out all the possible errors in the data and clean them up, but is there any way of getting Automation to ignore the error and carry on (leaving the field unpopulated)? Thank you.

1
comolevisual-foxpro
Did you try using on error instead? – Cetin Basoz
Thanks for your response Cetin. I have tried that. But having an ON ERROR instruction does not inhibit the generation of the OLE COM error. If anyone knows of a way to get Automation to ignore the error, I would be most grateful. – Andrew_46
I added a sample showing on error works. As a side note, why do you need .Value = ...? If it is about transferring data it is done best using CopyFromRecordset (or QueryTables.Add if there would be a link to data always). – Cetin Basoz
Thanks Cetin. I was doing something wrong with ON ERROR. Modified your sample code to fit in my own program, and it worked (so, ignoring my invalid data, fine); no longer got the OLE COM error. Will investigate CopyFrom Recordset, for the future. Thanks again. – Andrew_46

1 Answers

0
votes

I am adding this as an answer, since it contains some code that would otherwise cause a mess. With ON error it is working perfectly fine for me. ie:

*** Constant Group: XlSaveAction
#Define xlDoNotSaveChanges                                2
#Define xlSaveChanges                                     1

Local loExcel As Excel.Application
Local lcValue
loExcel = Createobject('Excel.Application')
loExcel.Workbooks.Add()
loExcel.ActiveWorkbook.SaveAs('c:\temp\ExcelReadTest.xlsx')
loExcel.DisplayAlerts = .F.
*loExcel.Visible = .T.
On Error lcValue = .Null.
With loExcel.ActiveWorkbook.ActiveSheet
    .Range('A5').Value = '=Like me'
    lcValue = .Range('A5').Value
    ? m.lcValue
Endwith
On Error
loExcel.ActiveWorkbook.Close(xlSaveChanges)
loExcel.Quit()