SUM Excel Automation VFP

[mikeisvfp]

Hello Experts,

is is possible to SUM an entire column, lets say the column is (D) and i want to display the result right underneath
I know you can specify in range Example F2:F44
But the problem is i cannot have a set row like for example F45 to display the result, only because tommorow there may be more than 45 records to SUM up.

please help

 

[Dan Freeman]

Yes. If you can do it manually in Excel then you can certainly do it through code.

In fact, that's a good way to figure out how to do it! Record a macro in Excel doing what you want and then open the code for the macro. The code will need some fiddling but it'll get you started.

One of the things you'll want to check out (that likely won't be in the macro) is the .UsedRows property, which is pretty much what it sounds like. Put your sum in .UsedRows+1.

 

[Mike Lewis]

Hi Mike,

This is really no different from creating any other formula.

For example, let's assume you want to sum the first three rows of col. A, and put the result at the foot of that column:

loSheet.Range("A4").Formula = "=SUM(A1:A3)"

assuming that loSheet is an object reference to the worksheet.

Obviously, you can adjust the cell addresses (A4, etc.) to suit your needs.

Note the equals sign at the start of the formula (inside the double quotes). This tells Excel that what follows is a formula.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[mikeisvfp]

Hi Dan

thank you for your response, I tried recording macros but its not much help because as you said I would need to do some fidding to the code, and at this point I dont know.

Would you be able to give me an example using the .UsedRows property as ive never used it before?

 

[mikeisvfp]

Hi Mike

This I understand, but lets say Charlie made 5 sales this week

so in column A lets display the 5 sales

loSheet.Range("A6").forumla = "=SUM(A1:A5)"

and then tommorow Charlie has 10 more sales, where is that information going to display when i am SUMing A1:A5 and the result is to be displayed in A6?

What i want is for the result to be displayed right underneath the number of sales whether it would be 10 sales or 20 sales

 

[Dan Freeman]

Oops. Typo! It's not UsedRows. It's UsedRange.

ActiveSheet.UsedRange.Rows.Count

You should really familiarize yourself with the Excel object browser. (Open a macro, then press F2) It will allow you to dig around in all of Excel's VAST object model.

 

[JRB-Bldr]

First I would ask if you looked at this forum area's FAQ's?
Specifically Excel - How to do Automation from VFP faq184-4428

Next I would advise you to find out what your Excel Automation needs to do by initially not involving VFP at all.
* Manually go into Excel
* Go into Tools - Macro - Record New.
* Do whatever it is that you want your Automation to do
* Go into Tools - Macro - Stop Recording.
* Go into Tools - Macro - Macros - <select your new macro> - Edit

Now review the Macro code to determine what your Excel Automation code should do and, in many cases, the VFP code will appear similar to the Excel VBA Macro code (although not exactly the same).

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

If you put the data into excel you know at what row you put the last number, the sum goes one row below.

If your table with sales is called sales, also see RECCOUNT("sales") or COUNT TO lnSales FOR salesman = ...
You'll get the number of rows in very many ways.

Bye, Olaf.

 

[Mike Lewis]

Mike,

The code I gave you was meant to be an example, to show you the general technique. I didn't expect you always to want to sum exactly the the first three rows and to put the sum in the fourth.

In your actual code, you will need to change the row numbers to reflect the number that actually applies in your situation. So, if, at a given point, Charlie has lnSales sales, where lnSales is a variable that holds the relevant number, then your code would look something like this:

loSheet.Range("A" + TRANSFORM(lnSales+1)).Formula = "=SUM(A1:A" + TRANSFORM(lnSales) + ")"

Obviously, it is up to you to keep track of the relevant number of sales within your application.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy