AndrewJen
asked on
Placing formula in Excel sheet from VFP
Using the following code from VFP:
oExcel.Cells(6,14).Formula R1C1 = "=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?
oExcel.Cells(6,14).Formula
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?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect, thank you!
Where might I find some documentation on this?
Where might I find some documentation on this?
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.
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.
"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.
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.
I have always used ;
oExcel.Range("A1").Formula
and not had any issues.