Excel Automation : How to use SetFormula function for the cell elements whose row & column were unknown but will be given by the program later on?

Question

I am working on the heritage codes which use C++ Excel Automation to output our analysis data in the excel spreadsheet. From the following article,

https://support.microsoft.com/en-us/topic/how-to-use-mfc-to-automate-excel-and-create-and-format-a-new-workbook-6f2450bc-ba35-a36a-df2f-c9dd53d7aef1

I knew we can use "range.SetFormula() function to calculate the formula results from some specific cells, for example:

  range = sheet.GetRange(COleVariant("C2"), COleVariant("C6"));  
  range.SetFormula(COleVariant("=A2 & \" \" & B2"));  

My question here is how can I use SetFormula function to point to some cell elements whose row & column are unknow but will be determined as the program runs. In specifically, I have a number of cell elements populated as my analysis runs. Different analysis will have different number of elements output to the excel spreadsheet. For example, if I have kw data, then the excel output will be populated in kw row 6 column and I also need to output some summary results based on these element underneath these populated elements. Something like this:

int kw = var_length; // the row changes depending on different analysis
DWORD numElements[2];
Range range;

range = sheet.GetRange(COleVariant(_T("A3")),COleVariant(_T("A3")));
numElements[0]= kw; //Number of rows in the range.
numElements[1]= 6; //Number of columns in the range.
saRet.Create(VT_R8, 2, numElements);
for(int iRow = 0;iRow < kw; iRow++)
{

	  for (iCol = 0; iCol < 6; iCol++)   
	  {  
		  index[0] = iRow;  
		  index[1] = iCol;  
		  saRet.PutElement(index, &somevalue);  
	  }  

}
range.SetValue2(COleVariant(saRet));

CString TStr;
TStr.Format(_T("A%d"), kw+2);

range = sheet.GetRange(COleVariant(TStr), COleVariant(TStr))

CString t1, t2;
t1.Format(_T("A%d"), kw/2);
t2.Format(_T("A%d"), kw);

range.SetFormula(COleVariant(L"=SUM(A&t1: A&t2)")); // Calculate the sum of second half of whole elements, Apparently, this didn't work, How can I fix this?

Here I want to sum the second half of whole elements but in the SetFormula function, I didn’t know exactly row number for these element, eg, A25 - A50. The row number is dependent on the kw which is given as input from program. Different analysis, kw is different. I attempted to use TStr format to get the row number but it CAN NOT be used inside SetFormula function. Ideally I want to use formula for my summary data output so that if I change my populated the element values, the summary data output can change accordingly. I searched in your MSDN website but couldn’t find any solution on how to resolve this.

Can someone help me with the issue?

Thanks in advance.

Answer 1

Probably you should write this: range.SetFormula(COleVariant(L"=SUM(A" + t1 + L":A" + t2 + L")")).

Answer 2

Thank you so much for your answer, Viorel-1, it works. Appreciate!