INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel Automation - check for #n/a error

Excel Automation - check for #n/a error

(OP)
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.

RE: Excel Automation - check for #n/a error

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

Quote:

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

RE: Excel Automation - check for #n/a error

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 Smileing

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.

RE: Excel Automation - check for #n/a error

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

CODE -->

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

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

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Excel Automation - check for #n/a error

(OP)
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

RE: Excel Automation - check for #n/a error

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

RE: Excel Automation - check for #n/a error

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: Key Board not responding

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

Bye, Olaf.

RE: Excel Automation - check for #n/a error

(OP)
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

RE: Excel Automation - check for #n/a error

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:

CODE --> Excel

=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:

CODE --> VFP

? 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

RE: Excel Automation - check for #n/a error

(OP)
MikeLewis,

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

Thanks again.

RE: Excel Automation - check for #n/a error

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.

RE: Excel Automation - check for #n/a error

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

Regards

Griff
Keep Smileing

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.

RE: Excel Automation - check for #n/a error

Quote (Griff)

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

RE: Excel Automation - check for #n/a error

Ta

Regards

Griff
Keep Smileing

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close