Error #NAME? after putting formula from Visual FoxPro into the Excel cell

[josefidalgo]

I try to introduce a cell from Excel to the following formula :

mf='=CONTAR.SE(k11:K'+transform(linha-2)+',"*")'

loWorkSheet.RANGE("k"+transform(linha)).select
loWorkSheet.RANGE("k"+transform(linha)).formula=[&mf]

but in cell appears #NAME ? why?
If select the cell and click F2 in Excel, already appears the result of the formula

 

[JRB-Bldr]

You have a formula that you want in an Excel cell.

=CONTAR.SE(k11:K'+transform(linha-2)+',"*")​

What happens when you manually put (no VFP involved at all) the very same Formula into the cell?

If everything works, then the problem is with how you are creating the Formula in VFP.
If the same problem occurs when VFP is not involved, then your Formula is bad and will not work -- so fix it first.

Good Luck,
JRB-Bldr

 

[Tore Bleken]

AFAIK Excel doesn't support transform(). In other words, Excel doesn't understand VFP code.

 

[Mike Lewis]

You have a formula that you want in an Excel cell.
=CONTAR.SE(k11:K'+transform(linha-2)+',"*")

What happens when you manually put (no VFP involved at all) the very same Formula into the cell?

I don't think that will work. If I'm not mistaken, [tt]linha[/tt] is a VFP variable, which Jose is building into the formula. Placing the formula in Excel as it stands won't get him anyhwere. He would have to substitute the actual value of the variable.

That said, your overall approach is obviously the right way to go. In other words, debug the formula in Excel first, and only when you are sure it will work, think about the VFP side of the equation.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, what JRB-Bldr suggest is not to be taken literal, whatever number linha is has to be put there manually in the manual test, which is still a valid and good idea.
Most probably just some line or row number, so a concrete value would be [tt]=CONTAR.SE(k11:K8,"*")[/tt]. Most probably this is missing quotation marks, eg should be [tt]=CONTAR.SE([/tt]"[tt]k11:K8[/tt]"[tt],"*")[/tt]

Bye, Olaf.

 

[JRB-Bldr]

Olaf - true.
The Formula is Not to bu put in in its EXACT VFP code representation.

Instead it needs to be put in in the resultant representation After VFP does its work by 'knowing' what the value of linha is and then performs the TRANSFORM(),

And Olaf is again most likely correct in that it is likely a missing quote mark that is messing things up.
You might check this out by just putting the Formula part into its own separate String Variable outside of the VFP Automation code. Then look it over to examine if it is what you are expecting it to be.

When I do VFP Automation of Excel I ALWAYS begin by doing what I want manually (no VFP involved) in Excel to make sure that all Formulas, Formatting, etc. work.
And I most often do it which capturing the work as an Excel Macro.
Once I have the Macro I can then examine its VBA code to see what VFP needs to do and most often how to do it.

Good Luck,
JRB-Bldr

 

[GriffMG]

I'm thinking that the quotes may not be necessary, Excel is pretty good at identifying range references.

I am wondering if the OPs macro expression is causing the problem, maybe there is a problem reading it
I would try terminating the substitution with a period and see if that helps?

mf='=CONTAR.SE(k11:K'+transform(linha-2)+',"*")'

 loWorkSheet.RANGE("k"+transform(linha)).select 
 loWorkSheet.RANGE("k"+transform(linha)).formula=[&mf.]

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. ​

 

[Olaf Doschke]

A space or ] are as good a separator as a dot. You only need a dot, when you want a dot next, eg &lcTablename.&lcFieldname don't work, you need &lcTablename..&lcFieldname, as the single dot is "consumed" as end of macro expansion variable name, thus the dot before field name is missing.

I have another suspicion, if it's not quotes around a range string: Automation does not work with locale function names. CONTAR seems to me like a spanish function name. In OLE automation you need to use the english function/method names. The locale function names are a GUI interface only feature. You'd need COUNT instead of CONTAR, as far as

http://www.piuha.fi/excel-function-name-translation/index.php?page=english-spanish.html

tells. CONTAR.SE? Perhaps COUNTIF?

Bye, Olaf.

 

[atlopes]

As Olaf, I think it's a localization issue. CONTAR.SE is the Portuguese localized version of COUNTIF (in Spanish, CONTAR.SI - close enough, again, Olaf ;-) ). Since José said that Excel is complaining with a #NAME error, and not with #NOME (the error that a Portuguese version of Excel would report back), I believe he's trying to run the code against an English Excel or the locale for the COM interface is set to English.

Anyway, there would also be a problem with the formula in a straight Portuguese environment, since the formula arguments must be separated with a semicolon, not with a comma.