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?
 
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
 
Gary2SevenCommented:

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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
All Courses

From novice to tech pro — start learning today.