Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.
HOW TO CALL EXCEL WORKSHEET FUNCTIONS FROM VFP

In many cases users of VFP application need to implement calculations in the specific fields like finance or statistics, and ask for the 'special' mathematical functions for engineering calculations. Sadly enough VFP itself has limited ability to satisfy those needs.


However using Excel Automation it seems all the Excel Worksheet functions can be incorporated into the VFP application. Here is what I have learned from my experience.
 
1. There are included Excel Worksheet functions and so-called Add-ins, which are actually external libraries.
2. To use included functions, all you need is to call the function from Excel. As an example here are two functions with values from the Excel Help.

ObjExcel= CREATEOBJECT("Excel.Application")

* days360, that according to Excel Help, "returns the number of days between two dates based on a 360-day year *(twelve 30-day months), which is used in some accounting calculations"
*ObjExcel.Application.days360(StartDate, EndDate, nMethod)

?ObjExcel.Application.days360("1/30/93", "2/1/93") && equals 1

* IRR, Internal Rate of Return
dime aIRR[6]
aIRR[1]=-70000
aIRR[2]=12000
aIRR[3]=15000
aIRR[4]=18000
aIRR[5]=21000
aIRR[6]=26000

?ObjExcel.Application.IRR(@airr) &&equals 8.66%

ObjExcel.quit

3. Add-ins require some additional precautions because of the confirmed glitch when the specific add-ins is not always loaded/installed properly. According to MS articles Q121730, Q291058, Q182190 and Q192363 for all known Excel versions installation must be forced.

Below is the simplest way how it could be done from VFP. I tested it with Excel97 and Excel2000 so far.

ObjExcel= CREATEOBJECT("Excel.Application")
ObjExcel.DisplayAlerts = .f.

* check what Addins Titles are available
if ObjExcel.Application.Addins.Count<=0
    wait wind time 1.0 "Terminating. NO add_ins are available!"
    ObjExcel.quit
    return
endif

*for many financial and engineering functions the Title should be "Analysis ToolPak" or "Analys32". 
*Check Excel Help for other functions.

lnAddins=0
for ia=1 to ObjExcel.Application.Addins.Count
    if ObjExcel.Application.Addins(ia).Title $"Analysis ToolPak, Analys32"
        lnAddins=ia
    endif
endfor

if lnAddins=0
    wait wind time 1.0 "Terminating. 'Analysis ToolPak' or 'Analys32' addins are not available!"
    ObjExcel.quit
    return
endif

with ObjExcel.Application.Addins(lnAddins)

    llInstalled=.Installed && to return the default setup later
    .Installed=.f. && force installation of Add-ins
    .Installed=.t. && force installation of Add-ins

    * call functions, values were chosen from Excel Help
    ?ObjExcel.Application.Run("ACCRINT","2/28/1998","8/31/1998","5/1/1998",0.1,1000,2,0) && 16.94444
    ?ObjExcel.Application.Run("ACCRINTM","4/1/1998","6/15/1998",0.1,1000,3) && 20.54795

    ?ObjExcel.Application.Run("COUPPCD", "1/25/93","11/15/94",2,1)
    ?ObjExcel.Application.Run("YIELD","2/15/91","11/15/99",0.0575,95.04287,100,2,0)
    ?ObjExcel.Application.Run("PRICE","2/15/91","11/15/99",0.0575,0.065,100,2,0)

    * XIRR
    dime a1[5]
    a1[1]=-10000
    a1[2]=-2750
    a1[2]=2750
    a1[3]=4250
    a1[4]=3250
    a1[5]=2750
    dime ad[5]
    ad[1]="1/1/1998"
    ad[2]="3/1/1998"
    ad[3]="10/30/1998"
    ad[4]="2/15/1999"
    ad[5]="4/1/1999"

    ?ObjExcel.Application.Run("XIRR", @a1, @ad, 0.1) &&equals 0.374859 or 37.4859%

    .Installed=llInstalled && default
endwith

ObjExcel.quit

Add-ins installation is time consuming especially on the Network. Therefore I would recommend initiating the Excel object and Add-ins installation only once and when VFP application starts. Then functions may be called as needed later in the application. Sure Excel object must be destroyed when process is finished.


Comments, opinions, etc. are welcomed.

ABOUT THE AUTHOR: YURI RUBINOV

Yuri Rubinov I was born in the first half of the previous century in the beautiful city of St. Petersburg, Russia. I was lucky enough to witness ongoing changes in the programming world. As an application scientist in laser optics I spent many years frequently using programming as a tool and never thought it would become my everyday business and profession. I started with Mainframe and early Russian PC like "Iskra" ("spark") where Algol69, Fortran and Basic -like languages were used. That times my PC boxes were always open to be available for repair needed almost every day. And preparation to run the job on mainframe was so time consuming that (to avoid delays) I used razor to quickly fix punch cards made by technicians from the text of my program. Coming to USA in my middle ages I was lucky to find a steady job in programming (though news in optics and scientific publications continue to draw my attention and interest, and I still teach Math and Physics having students ages from 5 to 35). Working with C/C++, Oracle and Basic I also gradually became familiar with FoxPro where I was surprised with the language flexibility, resources and it's ability to be used in quite different areas. I like the FoxPro community and would like to say a special thanks to founders and supporters of forums like foxite.com where I am able to express my interest, became familiar with problems that other people meet, and offer the solutions. My best wishes to all of you.

FEEDBACK

Samar @ 6/5/2007 8:07:26 AM
Thanks alot , i have been searching for this code from long time , but if you can help me in having this code as a visual basic code i will appreciate it and you will help me alot .

my mail : samar888@yahoo.com

thanks

mario oviedo @ 6/29/2010 7:26:26 PM
te felicito por tus trabajos, nunca me emagine conocer que un ruso trabajara con nuestro querido foxpro, te felicito y muchos saludos, felicidades, perdona que no me expreso en ingles

Rajneesh Talwar @ 7/19/2011 11:44:33 AM
Dear All,

I am in the prob. again kindly get me out of this :

i have lines of code with vfp which get the value of a specified cell and save it to the dbf, because this xls file is allays open and the value of the cell is randomly change with in 2-3 seconds so before getting the cell value i shall be save the excel sheet for accuracy so i use the command as


for example A1: Cell value is 1000 right now and

i have put in A1: Cell Value 2000 but could not save the excel sheet and use the following command via :

oWorkbook.save()

but it could not refresh the cell value and only the old value is to be saved (1000) into dbf.

I have make another way to get the value i use

oworkbook.saveas("temp.xls",56)

command and close the excel application and then again open temp.xls file and take the cell value but still it is not saved refresh last value and give me the old value only.


I have not get the proper cell value until i manually saved the xls file.


Kindly give me the solution if anyone can have.

Thanks in Advance.


Raj...
rajneesh2350@yahoo.co.in
rajneesh2350@gmail.com
M-9999993560 (India)

Bhaskar @ 7/20/2017 11:06:33 AM
Dear Yuri,

Thanks a ton. You've made my day with ObjExcel function for VFP.

Simple and so very useful!



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: