Using late binding to Excel, how do I move from the active cell to the first cell in the next row?

Question

I am using late binding (in C#, using Reflection, etc.) to access Excel via COM. In other words, I start by getting the Excel.Application object using

Type excelType = Type.GetTypeFromProgId("Excel.Application");
object excelApplication = Activator.CreateInstance(excelType);

(Actually, it's more generic than that, but that gives the idea).

As many people have noted in StackOverflow, one good way to see how to use the COM interfaces to Office is to record a macro in Excel and then look at the VBA code to see which class members to invoke.

When I do that, I see that if you have a reference to the active cell (obtained from the ActiveCell property of the Excel.Application object), then VBA references ActiveCell.Offset(x,y) to reference a cell relative to that ActiveCell (x and y being the row and column indices). However, if I try to access the Offset method of a Cell with late binding, the InvokeMember method fails with an exception saying "Member not found".

If I use the Visual Studio Object Browser to inspect the Microsoft.Office.Interop.Excel namespace and the ApplicationClass class (which I assume gives a good look into the COM interface obtained by the above C# code getting it via the ProgId), it shows that ApplicationClass has an ActiveCell property which is of type Microsoft.Office.Interop.Excel.Range. And inspecting that class shows that indeed it does NOT have a member named "Offset", hence the "Member not found" when I try to Invoke it.

That seems to mean that the VBA macros are using a different object model for Excel from that which is exposed via COM! Is that right, or am I missing something? And if it is the case, how can I get at that same VBA object model via late binding in COM?

Or, is there some way, using methods other than Offset, to be able to move the ActiveCell to the beginning of the next row, which is really what I want?

Answer 1

You could try this:

dim i as integer

i = ActiveCell.Row + 1

Excel.ActiveSheet.Cells(i,1).value = "Whatever your value is."

Answer 2

The answer is, as usual, easy when you know how.

My code a bit further down from the sample in my question looked a bit like this:

object activeCell;
object result;
// in here code to get the correct value into activeCell
if(activeCell.GetType().GetProperty("Offset") == null){
  result = activeCell.GetType().InvokeMember("Offset", BindingFlags.InvokeMethod, null, activeCell, new object[] {1, 0});
}

It was that InvokeMember that threw an exception with the message "Member not found".

When I changed the parameters to InvokeMember to read as follows:

result = activeCell.GetType().InvokeMember("Offset", BindingFlags.InvokeMethod | BindingFlags.GetProperty, null, activeCell, new object[] {1, 0});

then it worked.

Just why it works is still a mystery, which I am sure will earn someone some reputation if they answer it.