Excel Automation - check for #n/a error 1

[theresakad]

Within Visual Foxpro 9.0 SP2 I am reading from an excel file and need to check a cell for #N/A. Any suggestions? If #N/A exists in a cell then I need to write to another cell to fix the problem, if not, then I can move on.

Also, I have been searching for a website that would show me all the available properties and methods while working with excel automation with VFP but haven't found exactly what I am looking for. Do you know of any good sites I could visit?

Any help would be greatly appreciated.

 

[Mike Lewis]

I can't answer your first question, but ....

I have been searching for a website that would show me all the available properties and methods while working with excel automation with VFP

You cand find this in Excel. Go to the Visual Basic Editor. Call up the Object Browser (in my version of Excel, you do that by hitting F2, but other versions might vary.)

In the Object Browser, select the Excel library (might be pre-selected). You should now see a list of all the Excel classes. Click on a class to see a list of its members (that is, its properties, events and methods). Clicking on a member tells you its data type or its parameter sequence. Or click the Help button to get more information about the member.

All those classes and members are available in VFP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

I usually record macros in Excel, and then use them for reference when developing code to automate
in VFP.

Is you #N/A a literal string or an error status?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Of course, you can also see the members within VFP, via Intellisense. For example, if you do this in the command window:

ox = CREATEOBJECT("excel.automation")
owb = ox.Workbooks.Add("MyWorkbook.xls")

you can then type, for example, [tt]owb.[/tt] to see all the members of the workbook object.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[theresakad]

GriffMG,

#N/A is an error status from a lookup formula. I can't change the existing excel documents formulas.

Referencing the field that holds this value now produces an Error 11.

Error: 11
Function argument value, type, or count is invalid.
, Program: FRMPRICEQUOTES.BTNGENERATE.CLICK
, Line: 99
Code: Foxcode table not found, is not correct version or is incompatible.
06/20/2017 09:57:55 AM

 

[Mike Lewis]

Your error message is to do with Intellisense. It has got nothing to do with Excel or Automation.

Did you try my first suggestion?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

If excel displays #N/A, the cells(row,col).Value will be .F. or .NULL.

The error you post either comes from using .F. or .NULL. in a function expecting a numeric or text value, thus Error 11.

To fix this, you can't run code before checking values are of expected type in prereading or change code in whatever VFP side function or method making use of the cell values.

Besides that, an error message about Foxcode table surely is not code, so how does this come out where? Seems to be an error handler, which wanted to show the code of the erroring line and instead output the error message?!

There are several things wrong here. I wouldn't expect an error about foxcode from a button click event. Foxcode is used when you write code interactively at stages intellisense acts. Just see a recent post about a problem: thread184-1778311

There I explain how to repair the foxcode.dbf to solve that part of the issue.

Bye, Olaf.

 

[theresakad]

MikeLewis,

I did try your suggestion but wasn't able to get any results or find what I am looking for.

The excel file I am reading already has #N/A in the cell I am reading.

The value of this cell doesn't evaluate to .F. or NULL. When I use this code to check the value I get the Error 11: WAIT WINDOW MainSheet.Cells(7,5).value

 

[Mike Lewis]

One possible way to detect #N/A is with Excel's ISERROR() function.

Place that function in a spare cell of the spreadsheet. For example, put this in cell B2:

=ISERROR(A1)

where A1 is the cell that you want to test for #N/A.

Then, in your VFP code, look for the value of cell B2:

? oSheet.Cells(2,2).Value

If that returns .T., then A1 contains an error value. Unfortunately, that error could be #N/A, #VALUE, #NAME or anything similar, and I don't know how to distinguish between them.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[theresakad]

MikeLewis,

Thank you. I should have thought of that. This will work perfectly.

Thanks again.

 

[Olaf Doschke]

It's unimportant to know the error, if you expect a number, the .value will not be.

It's easy to get at the error message, that's in cell(row,col).text

Notice, the message is locale specific, eg in german #n/a (not available) is #nv (nicht verfügbar).

Bye, Olaf.

 

[GriffMG]

From Olaf's description above, I reckon you could just test for if .Cell(row,col).text = "#N/A" perhaps?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

From Olaf's description above, I reckon you could just test for if .Cell(row,col).text = "#N/A" perhaps

Yes. I just tried that and it appears to work. Sorry I didn't think of it before.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Ta

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​