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]