Thanks, Richard. I may try that. Here's what I had for now:

WITH oExcel.ActiveSheet as EXCEL.Worksheet .Range("AA999996").Formula = "=COUNTA(R:R)" DetailClaims = .Range("AA999996").Value - 1 .Range("AA999997").Formula = "=SUM(Q:Q)" DetailCharges = .Range("AA999997").Value .Range("AA999998").Formula = "=SUM(R:R)" DetailCaid = .Range("AA999998").Value .Range("AA999999").Formula = "=SUM(S:S)" DetailCare = .Range("AA999999").Value ENDWITH && oExcel.ActiveSheet as EXCEL.Worksheet

My DetailClaims logic is wrong though; it's counting ALL rows in the column, and I just wanted to the populated count. Any ideas on that one? Honestly I saw

On 2017-04-26 10:15, Richard Kaye wrote:

Here's what I dummied up at the command line. You just need to get the proper cells in the range object.

lox=CREATEOBJECT([excel.application]) lox.Visible=.t. lox.Workbooks.Add() los=lox.ActiveSheet lor=los.Range([a1],[a10]) ?lox.WorksheetFunction.Sum(lor)

--

rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: Wednesday, April 26, 2017 10:05 AM To: profoxtech@leafe.com Subject: Excel automation to get the SUM("R:R")

Excel 2013, VFP9SP2

Range Object model: https://msdn.microsoft.com/en-us/library/office/ff838238.aspx

I've got my handy "Microsoft Office Automation With Visual Foxpro" book out from HWP, trying to see where I can get the value of a range. In this case, when I just select the entire column, Excel tells me the SUM of all of the values in the column at the bottom. I need that number. I don't want to do some sort of loop; rather, I want to basically say from VFP: 'return the SUM("R:R").' I've looked at the object model above and there doesn't appear to be a .Sum to tag onto the end of the Range object (like you might think of with DotNet stuff). Also, I don't want to create a formula in some other remote cell and then read that, although that's my Plan B for now.

Ideas?

tia! --Mike

[excessive quoting removed by server]

Looks to me like your current solution is storing a formula in a remote cell which you said you wanted to avoid.

--

rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: Wednesday, April 26, 2017 11:56 AM To: profoxtech@leafe.com Subject: RE: Excel automation to get the SUM("R:R")

Thanks, Richard. I may try that. Here's what I had for now:

WITH oExcel.ActiveSheet as EXCEL.Worksheet .Range("AA999996").Formula = "=COUNTA(R:R)" DetailClaims = .Range("AA999996").Value - 1 .Range("AA999997").Formula = "=SUM(Q:Q)" DetailCharges = .Range("AA999997").Value .Range("AA999998").Formula = "=SUM(R:R)" DetailCaid = .Range("AA999998").Value .Range("AA999999").Formula = "=SUM(S:S)" DetailCare = .Range("AA999999").Value ENDWITH && oExcel.ActiveSheet as EXCEL.Worksheet

My DetailClaims logic is wrong though; it's counting ALL rows in the column, and I just wanted to the populated count. Any ideas on that one? Honestly I saw

On 2017-04-26 10:15, Richard Kaye wrote:

Here's what I dummied up at the command line. You just need to get the proper cells in the range object.

lox=CREATEOBJECT([excel.application]) lox.Visible=.t. lox.Workbooks.Add() los=lox.ActiveSheet lor=los.Range([a1],[a10]) ?lox.WorksheetFunction.Sum(lor)

--

rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: Wednesday, April 26, 2017 10:05 AM To: profoxtech@leafe.com Subject: Excel automation to get the SUM("R:R")

Excel 2013, VFP9SP2

Range Object model: https://msdn.microsoft.com/en-us/library/office/ff838238.aspx

I've got my handy "Microsoft Office Automation With Visual Foxpro" book out from HWP, trying to see where I can get the value of a range. In this case, when I just select the entire column, Excel tells me the SUM of all of the values in the column at the bottom. I need that number. I don't want to do some sort of loop; rather, I want to basically say from VFP: 'return the SUM("R:R").' I've looked at the object model above and there doesn't appear to be a .Sum to tag onto the end of the Range object (like you might think of with DotNet stuff). Also, I don't want to create a formula in some other remote cell and then read that, although that's my Plan B for now.

Ideas?

tia! --Mike

[excessive quoting removed by server]

On 2017-05-01 10:20, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:

On 2017-04-26 16:08, Richard Kaye wrote:

...

Looks to me like your current solution is storing a formula in a remote cell which you said you wanted to avoid.

Good point. I had (temporarily) relented on that.

Just tried it and your code worked perfectly. THANKS!!!!!!

* Now open its child DETAILS table and confirm oExcel.Workbooks.Open(Detail,.F.,.T.) && false to UpdateLinks; true to open ReadOnly WITH oExcel.ActiveSheet as EXCEL.Worksheet DetailClaims = oExcel.WorksheetFunction.CountA(.Range("Details!R:R")) - 1 DetailCaid = oExcel.WorksheetFunction.Sum(.Range("Details!R:R")) DetailCharges = oExcel.WorksheetFunction.Sum(.Range("Details!Q:Q")) DetailCare = oExcel.WorksheetFunction.Sum(.Range("Details!S:S")) ENDWITH && oExcel.ActiveSheet as EXCEL.Worksheet oExcel.ActiveWorkbook.Close()