How to set Excel footer with Excel-Add-In

Question

I've been told to write up an Excel-Add-In which pastes a specific footer into every worksheet in a workbook.

After reading up the documentation of the Excel.Interop namespace I ended up with this junky piece of code:

 public partial class Ribbon1
{
    Excel.Application _excelApp;
    private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
    {
        _excelApp = new Excel.Application();
    }

    private void button1_Click(object sender, RibbonControlEventArgs e)
    {
        var filename = _excelApp.GetSaveAsFilename();
        Excel._Worksheet worksheet = (Excel._Worksheet)_excelApp.ActiveSheet;
        worksheet.PageSetup.CenterFooter = filename;
    }
}

I have a problem in pinning the active worksheet. How can I actually use this object? - Right now it is null. I find the msdn articles related to this topic just plain stupid.

score 2 · Accepted Answer · answered Jan 22 '13 at 19:24

I finally found the information I was looking for at: VSTO

First of all I set those 2 lines:

using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Tools.Excel;

As a second step I check if the workbook is not null, then if the worksheet is not null. If this is the case, set the footer to the document. It is not pretty, but I can refine it tommorow at the office.

Excel.Workbook Workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
        if (Workbook != null)
        {
            Office.Workbook vstoWorkbook = Globals.Factory.GetVstoObject(Workbook);

            Excel.Worksheet worksheet = Globals.ThisAddIn.Application.ActiveSheet;
            if (worksheet != null)
            {
                Office.Worksheet vstoSheet = Globals.Factory.GetVstoObject(worksheet);
                vstoSheet.PageSetup.CenterFooter = "testing the footer";
            }
        }

score 0 · Answer 2 · answered Jan 22 '13 at 15:30

0

A couple of notes. First, you do not have an active sheet because you have not created one, and Excel will not create one by default.

Try the (simplified) code in your button click event handler:

Excel.Workbook workbook = _excelApp.Workbooks.Add();
Excel.Worksheet worksheet = workbook.Worksheets[0];
worksheet.PageSetup.CenterFooter = filename;
// Do remaining work here

Also, the line

var filename = _excelApp.GetSaveAsFilename();

Only returns the filename selected by the user. It does not actually save the file. You will need to follow it up with:

workbook.SaveAs(Filename: filename);

answered Jan 22 '13 at 15:30

Brett Wolfington

6,587
4
32
51

I don't know if I understood you correctly, but if Excel does not create a workbook/sheet upon opening, there ought to be a blank screen. I do not want to create a new workbook, I want to use the one, from which this AddIn gets called. And up until now, there is no footer set upon clicking – Marco Jan 22 '13 at 15:43
If you are calling this from a COM Addin, you should not be creating a new instance of the Excel.Application class. That is probably your problem. You need to use the instance of the class that is obtained in `OnConnection`. – Brett Wolfington Jan 22 '13 at 15:51
Yes, this is my original question. How can I use the actual opened instance of Excel to set the footer and properties needed? – Marco Jan 22 '13 at 16:06
Post the rest of your Addin code please. There is not enough displayed here to be able to fully answer your question. You might also check here: http://stackoverflow.com/questions/2020234/creating-add-in-for-excel-using-c-sharp for some general advice. – Brett Wolfington Jan 22 '13 at 16:23
this is the whole custom code. Besides this, there are only the 2 empty builtIn methods for load and unload – Marco Jan 22 '13 at 17:30
There is no code here to actually create the Addin, so you must either be not creating the addin properly or using a library to manage the COM interop. I would point you to http://stackoverflow.com/questions/2020234/creating-add-in-for-excel-using-c-sharp for some instructions on how to actually create the addin. – Brett Wolfington Jan 22 '13 at 18:00
You definitly are not being helpful, as the VSTO links. Because everything MSDN is throwing at me is non working lines of code. Singular. `Excel.Workbook newWorkbook = this.Application.Workbooks.Add(missing);` does not work. Either I am not allowed to use 'this' or missing is not defined, which it never is or at last _Error 1 'addFooterToSheet.Ribbon1' does not contain a definition for 'Application' and no extension method 'Application' accepting a first argument of type 'addFooterToSheet.Ribbon1' could be found._ – Marco Jan 22 '13 at 19:11
The point is that there is a *lot* of additional code that you need to write in order to create a working addin. What you have here is only a tiny fragment. It is more than I can address in a single answer here. I would suggest Googling "Excel COM Addin C#" if the link that I posted is not sufficient. – Brett Wolfington Jan 22 '13 at 19:54
actually, there isn't. The code I posted as a solution is everything needed. Just for the sake of having a working example I put everything in the click eventhandler of the button in the ribbon. Nothing more to it. – Marco Jan 22 '13 at 20:27

How to set Excel footer with Excel-Add-In

2 Answers2