Excel Interop Load Template into Existing Workbook

Question

How can I load the sheets from a template into the existing Excel workbook (if there is no existing workbook then create a blank one first).

I have tried

Excel.Application.Workbooks.Open("path");

this works but every time it creates a new Excel session as it's creating a new workbook. I have not been able to find anything through Google on how to load the data as a new sheet into the existing book.

Answer 1

Answer (thanks to Jon49 @ NetOffice discussion board: http://netoffice.codeplex.com/discussions/434906):

Excel.Application.Sheets.Add(Type:="TemplateWorkbookPath")

You can do it from your workbook variable that you are currently working in:

wkb.Application.Sheets.Add(Type:="TemplateWorkbookPath")

Answer 2

Just calling Open() on the workbook will, as you have discovered, create a new Excel instance and not connect to the currently-running one. What you need is a lot more complicated. Below is the method that I use to connect to a workbook, the name of which you already know (you can also tweak this to allow the user to select which one they want to open):

private Excel.Workbook GetWorkbook(string workbookName)
{
    Excel.Window window = null;      // Excel window object from which application is grabbed
    Excel.Application app = null;    // Excel instance from which we get all the open workbooks
    Excel.Workbooks wbs = null;      // List of workbooks
    Excel.Workbook wb = null;        // Workbook to return
    EnumChildCallback cb;            // Callback routine for child window enumeration routine
    List<Process> procs = new List<Process>();           // List of processes

    // Get a full list of all processes that have a name of "excel"

    procs.AddRange(Process.GetProcessesByName("excel"));

    foreach (Process proc in procs)
    {
        // Make sure we have a valid handle for the window

        if ((int)proc.MainWindowHandle > 0)
        {
            // Get the handle of the child window in the current Excel process

            int childWindow = 0;
            cb = new EnumChildCallback(EnumChildProc);
            EnumChildWindows((int)proc.MainWindowHandle, cb, ref childWindow);

            // Make sure we got a valid handle

            if (childWindow > 0)
            {
                // Get the address of the child window so that we can talk to it and
                // get all the workbooks

                const uint OBJID_NATIVEOM = 0xFFFFFFF0;
                Guid IID_IDispatch =
                    new Guid("{00020400-0000-0000-C000-000000000046}");
                int res = AccessibleObjectFromWindow(childWindow, OBJID_NATIVEOM,
                    IID_IDispatch.ToByteArray(), ref window);

                if (res >= 0)
                {
                    app = window.Application;
                    wbs = app.Workbooks;

                    // Loop through all the workbooks within the current Excel window
                    // to see if any match

                    for (int i = 1; i <= wbs.Count; i++)
                    {
                        wb = wbs[i];

                        if (wb.Name == workbookName)
                        {
                            break;
                        }

                        wb = null;
                    }
                }
            }
        }

        // If we've already found our workbook then there's no point in continuing
        // through the remaining processes

        if (wb != null)
        {
            break;
        }
    }

    Release(wbs);
    Release(app);
    Release(window);

    return wb;
}

The Release() methods called above simply set the references to null and call Marshal.FinalReleaseComObject() on them, otherwise you end up with headless instances of Excel all over the place.

You'll also need the following to perform some of the functionality to grab windows:

private delegate bool EnumChildCallback(int hwnd, ref int lParam);

[DllImport("User32.dll")]
private static extern bool EnumChildWindows(int hWndParent, EnumChildCallback lpEnumFunc, ref int lParam);

[DllImport("Oleacc.dll")]
private static extern int AccessibleObjectFromWindow(int hwnd, uint dwObjectID, byte[] riid, ref Excel.Window ptr);

private bool EnumChildProc(int hwndChild, ref int lParam)
{
    // Get the name of the class that owns the passed-in window handle

    StringBuilder buf = new StringBuilder(128);
    GetClassName(hwndChild, buf, 128);

    // If the class name is EXCEL7 then we've got an valid Excel window

    if (buf.ToString() == "EXCEL7")
    {
        lParam = hwndChild;
        return false;
    }

    return true;
}

[DllImport("User32.dll")]
private static extern int GetClassName(int hWnd, StringBuilder lpClassName, int nMaxCount);