Copy and paste a row containing formulas in Excel

[JoeN]

I'm using Excel Report and the Excel Object Library.  Can someone tell me how to duplicate a row of Excel worksheet cells containing formulas?  My situation is that I'm writing raw data to several sheets in a workbook that has other, formula-driven worksheets that summarize the data (using VLOOKUP).  I realize that I can manually replicate a bunch of unused formula-rows to keep the summary sheets up to date, but the ideal solution is to have my program copy a formula row (or range) only when it is needed, so that the process will be 100% automated.  I wouldn't mind copying the cells one at a time, if that will work.

I have looked at examples and have combed through quite a few pages in this forum.  If my question has already been answered somewhere, please accept my apologies, and point me toward that source of information.  I also tried experimenting with some of the library functions (e.g. Excel_WorksheetCopy), but so far, without success.

Thanks for any help.

Joe

[Snowman 6008]

I'm familiar with the library, but never tried to copy a formula. When you try to copy the contents of a cell, as say a string, I take it that CVI just grabs the computed value and not the formula ?

[JoeN]

Hello Richard,

Yes, I believe so.  I didn't actually try to copy a cell that has a formula and a value.  So far, what I've tried is to copy a cell containing only a formula, which didn't work.  I think I got an exception. 

My strategy for the formula-driven worksheets is to have one row below the last row containing data, that contains just the vlookup formulas but is otherwise blank.  Then, when my code is ready to add data to a raw-data worksheet, it would first replicate the row containing the formulas.  Essentially, I want to programatically simulate the act of highlighting a cell and replicating it (and its formula) by grabbing the cell's corner handle with the mouse cursor and dragging it down to the cell below.  But if I copy & paste, I would have to increment the row number references in all the formulas.

Now that I think about it, I need to just try to write a formula from my program to the worksheet.  This is assuming that a string prefixed with an = sign will be recognized by Excel as a formula.  If that works, I can put my formulas in an ini file and copy them from there.  Seems like too much work, but I'm getting the feeling it may be the only way.

[Snowman 6008]

I was able to get the final value of a cell using GetCellValue, and setting type to a string. But if you want to get the actual formula from the cell, you will have to use the functions that Microsoft opens up via ActiveX. You can go to Tools » Create ActiveX Controller, click Next  then Select Microsoft Excel 12.0 Object Library, click next again and specify a file to save the data to, and click next twice (i know your allready using the Object Library, but others may find this useful). You can then use the generated .fp file to get the formula from a cell. I spent a little time attempting to generate an example, but it was taking quite a bit of time. Try checking out the Excel_RangeGetHasFormula and the Excel_RangeGetFormula functions in the associated object library. Have you taken a look atthese functions?

[JoeN]

Hello Richard,

Excel_RangeGetFormula certainly should do the trick.  Unfortunately, Excel 9.0, which I have does not implement that function as far as I can tell.  The only other Excel Object Library I have available in my ActiveX server list is 11.0.  That one offers Excel_IRangeGetFormula.  Do you know what the "I" signifies?

Unfortunately, I have to move on, at least for now.  Being able to automatically propagate the formulas as the workbook grows would make the application self-maintaining, which would be very nice.  But for now, I'll just have to make sure enough rows are pre-populated with VLOOKUPs in anticipation of new data.  I can't justify the additional time required right now, but at least I know that Excel_RangeGetFormula is very likely the solution I was looking for.  And, of course, this will be useful to anyone else looking here for a solution to the same problem.

Thanks very much for your suggestions.

Joe