Placing formula in Excel sheet from VFP

Using the following code from VFP:

oExcel.Cells(6,14).FormulaR1C1 = "=AVERAGE(B6:M6)"

I get superfluous quotes put in to the formula which gives me an #NAME error in Excel. The formula gets received like this:

=AVERAGE('B6':'M6')

How can I prevent this?
LVL 3
AndrewJenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
CaptainCyrilConnect With a Mentor Founder, Software Engineer, Data ScientistCommented:
You have to use relative addressing:

xlapp = CREATEOBJECT("Excel.Application")
xlapp.Visible = .T.
xlwb = xlapp.Workbooks.Add
xlsheet = xlwb.Sheets(1)
FOR i = 1 TO 10
	xlsheet.Cells(i,1).Value = i
ENDFOR
xlsheet.Cells(11,1).FormulaR1C1 = "=AVERAGE(R[-10]C:R[-1]C)"

Open in new window

0
 
Gary2SevenConsultantCommented:

I have always used ;

oExcel.Range("A1").Formula="=AVERAGE(B6:M6)"

and not had any issues.
0
 
jrbbldrCommented:
This is just a guess, but every time I get an Excel error:   #NAME    it has been due to the contents of the cells themselves upon which the formula operates.

Try going into the Excel file manually (without any VFP involvement) and putting the formula into the desired cells and see what the result is.  

If you still get the error displayed, then it is the result of the cell contents that are being calculated - perhaps the Cell Type or the value within the cell(s).

Good Luck
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AndrewJenAuthor Commented:
Perfect, thank you!

Where might I find some documentation on this?
0
 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
You're welcome!

I get it from Excel's Help. Sometimes I record macros from Excel and see how it works.

If I get lucky, I might find a good website MSDN or some other forum that describes the things I am looking for.

When you get stuck, EE is your best pal! You can ask the question in FoxPro and Excel sections.
0
 
jrbbldrCommented:
"Sometimes I record macros from Excel and see how it works"

If I don't know how to do an Excel Automation, I ALWAYS do the work manually in Excel while recording the operations as a Macro.   Then, when done, examine the Macro to see how Excel did it.    That then 'tells' me what to do and how to do it in my VFP Automation.

AndrewJen - glad the Captain got you what you needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.