Excel & C#

Question

I am trying to learn C# better.  Basically, I recorded a macro in Excel, put the results in a code translator, and copied/pasted that into my C# project.

Here is the code, in it's entirety:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            {
                // ~~> Define your Excel Objects
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
                Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
                // ~~> Opens an exisiting Workbook. Change path and filename as applicable
                xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\Excel\\Desktop\\Subtotals.xls");
                // ~~> Display Excel
                xlApp.Visible = true;
                xlWorkSheet = xlWorkBook.Sheets("Sheet1");
                // With...
                xlWorkSheet.Range("A1").Select();
                xlApp.Selection.Subtotal(1, Function = xlApp.xlSum, xlApp.Array(3), true, false, true);
                xlWorkSheet.Range("A1").Select();
            }
        }
    }
}

Here are my errors:

Error    1    Non-invocable member 'Microsoft.Office.Interop.Excel._Workbook.Sheets' cannot be used like a method.

Error    3    The name 'Function' does not exist in the current context

Error    4    'Microsoft.Office.Interop.Excel.Application' does not contain a definition for 'xlSum' and no extension method 'xlSum' accepting a first argument of type 'Microsoft.Office.Interop.Excel.Application' could be found (are you missing a using directive or an assembly reference?)

I added a reference to Excel, so I'm not sure why I get error messages saying that I am missing this reference (this is not the first time this has happened).  I'll guarantee that the reference is there!!

What am I doing wrong here?

Here is the recorded macro:

Sub Macro1()

    Range("A1").Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Range("A1").Select
    
End Sub

I guess I was (kind of) hoping C# could (somehow) run the macro in Excel, so I didn't have to convert the VBA to C#.  It's pretty easy to open an Excel file using C#; easy to save the file and close it too.  However, I don't know how to do Excel-like commands using C#.  That's the problem here.  Any ideas???

Thanks everyone!

Answer 1

1) use this definition to reduce the amout of typing.  You can use the substition it a number of spots in your code.
using Excel = Microsoft.Office.Interop.Excel;

2) Use square bracket in stead of parenthesis.  Add type cast
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["Sheet1"];

3) Use get_Range
xlWorkSheet.get_Range("A1", Type.Missing).Select();

4) Add missing paramters.  Use "_OPen"
xlWorkBook = xlApp.Workbooks._Open("c:\\temp\\book1.xls",
           Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
           Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
           Type.Missing, Type.Missing);

5) Not usre what you are trying to do with subtotal.  Is it your own function?

---

jdweng

Answer 2

Actually, I'm using VS 2010; I don't think I need all the 'Type.Missing' stuff.

Basically, I recorded a very simple macro in Excel; it subtotals a few items in ColumnA.  I'm just experimenting with this (non-work-related), to see if I can open an Excel file and run a macro (already in the WB), save the changes that are made, and close the Excel file.  That's all I'm trying to do for now.  basically, I'm trying to control Excel through C#.  I know how to do this using Access, for instance.  Since Excel/Access are all .COM, it's fairly easy.  What I don't understand is how to go from .NET to .COM. 

So, if I open the Excel file, run the code, then save the change, and close the file, my job is done.  Now, I'm trying to do essentially the same thing, but use C# to control the process.  I know how to do this in VB.NET.  Basically, I'm trying to figure out how to do this (as easy as possible) using C#.

Does it make sense?

Answer 3

C# requires all the parameter.  The union method is refered to in C# as the "Dreaded Union Method" which required 30 parameters.  C3 sharp doesn't make a lot of sense requiring all the parameters.  It is the only language I know that does this.  All other computer languages allow you to leave off the parameters at the end of the parameter list if they are optional.

---

jdweng

Answer 4

I'm pretty sure with VS 4.0, those optional parameters really are optional.  I've dropped all those parameters that are 'Type.Missing' and had no problem running all kinds of C# projects.  Anyway, is there any way to do the Excel/VBA part using C#?  I mean, can I use C# to (sort of)  run VBA, or do I need to buckle down and really learn how to convert that VBA into something C# can understand?  Even when I use those online code translators, all the VBA gets scrambled into something C# doesn't understand.

Thanks!!

Answer 5

The translators aren't doing a complete job if they are no replacing the parenthsis with square brackets in an array.  the problem is some parenthesis need to get replaced and others don't.  Iaso C# in some cases requires "open" and other cases requires "_open".  the translators become much more complicated to write when then have to handle large number of exception like the two I posted.

---

jdweng

Answer 6

Hi,

The code below works for me:

  private void button6_Click(object sender, EventArgs e)
        {
            Excel.Application oExcel = new Excel.Application();
            oExcel.Visible = true;
            Excel.Workbook oBook = oExcel.Workbooks.Open(@"C:\Source.xlsx");
            Excel.Worksheet oSheet1 = oBook.Worksheets["Sheet1"] as Excel.Worksheet;     
            Excel.Range rng = oSheet1.get_Range("A1", "D19");
            rng.Subtotal(2, Excel.XlConsolidationFunction.xlSum, new int[] { 4 }, true, false, Excel.XlSummaryRow.xlSummaryBelow);
        }

I hope this helps.

---

Calvin Gao[MSFT]
MSDN Community Support | Feedback to us

Answer 7

Thanks, Joel and Calvin.