How to name a new Excel sheet I made and place it at the end of all the sheets?

Question

I figured out how to make a new Excel sheet in C#. And I do it this way:

private static Microsoft.Office.Interop.Excel.ApplicationClass appExcel;
appExcel.Worksheets.Add();

But I have no idea how to give a name to the Excel sheet I made, nor how to place it at the end instead of the beginning.

I tried this:

sheet1 = appExcel.Worksheets.Add();
sheet1.name = "test";

But this didn't work. I also looked in the Add function, but I can NOT fill the name in this way: appExcel.Worksheets.Add("test");

My code:

excel_init("C:\\Users\\me\\Desktop\\excel2.xlsx");

private static Microsoft.Office.Interop.Excel.ApplicationClass appExcel;
        private static Workbook newWorkbook_First = null;
        private static _Worksheet objsheet = null;

static void excel_init(String path)
{
    appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();

    if (System.IO.File.Exists(path))
    {
        // then go and load this into excel
        newWorkbook_First = appExcel.Workbooks.Open(path, true, true,5); // does NOT make 5 excel sheet???
        objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
    }
    else
    {
        try
        {
            appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            appExcel.Visible = true;
            newWorkbook_First = appExcel.Workbooks.Add(1);
            objsheet = (Microsoft.Office.Interop.Excel.Worksheet)newWorkbook_First.Sheets[1];
        }
        catch (Exception e)
        {
            Console.Write("Error");
        }
        finally
        {
        }
    }
}

Does [this question](http://stackoverflow.com/questions/193092/c-sharp-how-to-add-excel-worksheet-programatically-office-xp-2003) help at all? Note that it looks like the 1 is how they choose the beginning, so replacing it with the number of sheets should give you the end. — Guvante, Jun 12 '15 at 07:18
Fixed the name: with this code in the function excel_init > else > try { `objsheet.Name = ("test");` — wouter, Jun 12 '15 at 07:45

rism · Accepted Answer · 2015-06-12T08:51:55.823

4

If you look at the documentation for Worksheets.Add you get:

Object Add(
    Object Before,
    Object After,
    Object Count,
    Object Type
)

So the Add method both creates the new worksheet and specifies various parameters about how to place it in the Worksheets collection.

Before Type: System.Object Optional Object. An object that specifies the sheet before which the new sheet is added.
After Type: System.Object Optional Object. An object that specifies the sheet after which the new sheet is added.
Count Type: System.Object Optional Object. The number of sheets to be added. The default value is one.
Type Type: System.Object Optional Object. Specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet.

So all you need to do is something like:

var newSheet = appExcel.Worksheets.Add(null, appExcel.Worksheets(appExcel.Worksheets.Count), 1, XlSheetType.xlWorksheet);    
newSheet.Name = "myWorkSheet";

This will create and return one worksheet after the last worksheet and then set the name.

Note: Worksheets are not 0 based.

Update:

So as per my hackery in the comments this should instead be:

 var newSheet = (Microsoft.Office.Interop.Excel.Worksheet)appExcel.Worksheets.Add(Type.Missing, appExcel.Worksheets[appExcel.Worksheets.Count], 1, XlSheetType.xlWorksheet);
 newSheet.Name = "myWorkSheet";

or just

var newSheet = appExcel.Worksheets.Add(Type.Missing, appExcel.Worksheets[appExcel.Worksheets.Count], 1, XlSheetType.xlWorksheet) as Worksheet;
newSheet.Name = "myWorkSheet";

edited Jun 12 '15 at 08:51

answered Jun 12 '15 at 08:10

rism

11,932
16
76
116

Got an error @ second Workseets: `Non-invocable member 'Microsoft.Office.Interop.Excel.ApplicationClass.Worksheets' cannot be used like a method.`' – wouter Jun 12 '15 at 08:21
Oh then change "(" to "[". appExcel.Worksheets[appExcel.Worksheets.Count] – rism Jun 12 '15 at 08:23
it runs now, but I get this error: Exception from HRESULT: 0x800A03EC – wouter Jun 12 '15 at 08:25
Uh. Its probably that null. COM doesn;t like it. Try leaving it out and just go with `appExcel.Worksheets.Add(, appExcel.Worksheets(appExcel.Worksheets.Count), 1, XlSheetType.xlWorksheet);` If that doesn't compile then hang tight while i look up the placeholder. – rism Jun 12 '15 at 08:27
I didnt compile, thanks for helping me out btw, helps me a lot – wouter Jun 12 '15 at 08:29
np. :) Try using `IntPtr.Zero`. So `appExcel.Worksheets.Add(IntPtr.Zero, appExcel.Worksheets[appExcel.Worksheets.Count], 1, XlSheetType.xlWorksheet);` Been a couple of years since I've done office interop and not in office. If that's not it then I'll have to look up the format. – rism Jun 12 '15 at 08:31
It doenst give an error anymore now, BUT it doenst create the new sheets with IntPtr.Zero? – wouter Jun 12 '15 at 08:34
Ah ok so as per https://msdn.microsoft.com/en-us/library/dd264739.aspx use `Type.Missing`. – rism Jun 12 '15 at 08:35
`appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass(); appExcel.Visible = true; newWorkbook_First = appExcel.Workbooks.Add(1); objsheet = (Microsoft.Office.Interop.Excel.Worksheet)newWorkbook_First.Sheets[1]; objsheet.Name = ("test"); var newSheet = appExcel.Worksheets.Add(IntPtr.Zero, appExcel.Worksheets[appExcel.Worksheets.Count], 1, XlSheetType.xlWorksheet);` I got this now, BUT it only creates 1 sheet named "test" – wouter Jun 12 '15 at 08:35
So `var newSheet = appExcel.Worksheets.Add(Type.Missing, appExcel.Worksheets[appExcel.Worksheets.Count], 1, XlSheetType.xlWorksheet);` – rism Jun 12 '15 at 08:36
Per-fect. Only now I also want to change the name of the sheet. newSheet.Name isnt working – wouter Jun 12 '15 at 08:38
So then just `newSheet.Name = "myWorkSheet";` as per answer. It's a 2 step operation. You don't do it in the `Add`. `Add` creates the object (in this case the sheet) and then you name it. By default it will have a name like "Sheet" + index from memory. – rism Jun 12 '15 at 08:40
Arrgh. My bad. We need to cast it because it's just returning an object which of course does not have a property called `Name`. So instead use `var newSheet = (Microsoft.Office.Interop.Excel.Worksheet)appExcel.Worksheets.Add(Type.Missing, appExcel.Worksheets[appExcel.Worksheets.Count], 1, XlSheetType.xlWorksheet);` or just `var newSheet = appExcel.Worksheets.Add(Type.Missing, appExcel.Worksheets[appExcel.Worksheets.Count], 1, XlSheetType.xlWorksheet) as Worksheet;` if you have the namespaces imported. – rism Jun 12 '15 at 08:44
Thank you very much for helping me out! I appriciate it :) Have a nice day sir! – wouter Jun 12 '15 at 08:49
One more question about something else: this gives an error saying : `Type expected` just right before the first `(` : `var newSheeet = new ((Microsoft.Office.Interop.Excel.Worksheet)appExcel.Worksheets.Add(Type.Missing, appExcel.Worksheets[appExcel.Worksheets.Count], 1 , XlSheetType.xlWorksheet))[4];` – wouter Jun 12 '15 at 09:47

How to name a new Excel sheet I made and place it at the end of all the sheets?

1 Answers1