Accessing an open Excel Workbook in C#

Question

I need to access an excel file that is already open. I thought just inspecting the .Workbooks property that it would be there but it isn't. What is the right way to get a reference to the open workbook?

var app = new Microsoft.Office.Interop.Excel.Application();

// the count is 0 =(
app.Workbooks.Count == 0;

EDIT

I can get a reference to the Excel Application via...

app = (Excel.Application)Marshal.GetActiveObject("Excel.Application");

but app.Workbooks.Count is still 0 why isn't it able to get a reference to the opened workbook?

Bolt

Answer 1

Instead of instantiating a new instance, check for an existing one:

try
{
  Microsoft.Office.Interop.Excel.Application app = 
      System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
}
catch
{
  // Excel is not running.
}

Liquefy

this seems to get a reference to the Excel application app != null but app.Workbooks.Count is still 0. Why isn't it getting a reference to the opened workbook? – Bolt

Excel interop can be annoying. The most important thing is to ensure that you always release the Excel application object. Otherwise, you will have ghost instances of EXCEL.exe running. So, close all Excel instances, then try the code again. Finally, read this article on how to properly release Excel support.microsoft.com/default.aspx?scid=kb;EN-US;317109 – Liquefy

Also, see this thread for proper cleanup and discussion: #159206 – Julide

I don't think my issue is in regards to release the application, although that link helped... but I can't even access the opened workbook.. I can get to the Excel Application itself, but I can't find the workbook – Bolt

To verify - Did you check how many instances of Excel are opened in task manager? – Liquefy

Jon, I am trying to do the same. Did you ever manage to link to an already open worksheet? – Raseda

I'm also trying to do this, did you ever find a solution? – Barahona

Looks like as of 2012 no solution for the Workbooks.Count equal to 0? – Diggings

Still looking for a solution for the he Workbooks.Count equal to 0 – Glenn

If the opened Excel is in PROTECTED VIEW you may find this answer helpful #58458688 – Publicly

Answer 2

Try this code:

using Excel = Microsoft.Office.Interop.Excel;

public Excel.Application xlApp;
public Excel.Workbook xlWorkBook;
public Excel.Worksheet xlWorkSheet;

public void ExcelTransferData()
{
   xlApp = new Microsoft.Office.Interop.Excel.Application();
   xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

   foreach (Excel.Workbook item in xlApp.Workbooks)
   {
        //Select the excel target 'NAME'
        if (item.Name == "Template.xlsx")
        {
            xlWorkBook = item; 
            break;
        }

        //Select the target workbook
        xlWorkSheet = xlWorkBook.Sheets[1] as Excel.Worksheet;
        //Set cell value
        xlWorkSheet.Cells[5, 1] = "davinceleecode";
   }
}

Sedition

Answer 3

 String constr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+txtSourceFile.Text+";Extended Properties='Excel 8.0;HDR=YES;';";
        String constr2 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtLibrary.Text + ";Extended Properties='Excel 8.0;HDR=YES;';";

        OleDbConnection con = new OleDbConnection(constr);
        OleDbConnection con2 = new OleDbConnection(constr2);
        OleDbConnection con3 = new OleDbConnection(constr);

        OleDbCommand oconn = new OleDbCommand("Select * From [eudra$]", con);
        OleDbCommand oconn2 = new OleDbCommand("Select * From [Sheet1$]", con2);
        OleDbCommand oconn3 = new OleDbCommand("Select * From [eudra$] where EXAMPARM in ('with one or more serious adverse events','with one or more non-serious adverse events that met the incidence cutoff')", con);

        if (txtSourceFile.Text != "")
        {
            con.Open();
            con2.Open();
            con3.Open();

            OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
            OleDbDataAdapter sda2 = new OleDbDataAdapter(oconn2);
            OleDbDataAdapter sda3 = new OleDbDataAdapter(oconn3);


            DataTable data = new DataTable();
            sda.Fill(data);

            DataTable data2 = new DataTable();
            sda2.Fill(data2);

            DataTable data3 = new DataTable();
            sda3.Fill(data3);

            var test = JoinDataTables(data, data2, (row1, row2) => (row1.Field<string>("BODY_SYS").ToUpper() == row2.Field<string>("Term").ToUpper() ));

            data3.Merge(test, true);

            dgvImp.DataSource = data3;
            con.Close();
        }

Miran

Answer 4

The first function IsXlFileOpen will tell you whether the excel file is open or not.

The second function GetXlSheet will get you the Excel application, workbook and worksheet output, from where you can start coding.

using System.Collections.Generic;
using System.IO;
using System.Linq;
using wf = System.Windows.Forms;
using xl = Microsoft.Office.Interop.Excel;

public static class ExcelTest
{   
    public xl.Application xlApp = null;
    public xl.Workbook xlWb = null;
    public xl.Worksheet xlWs = null;

    public static bool IsXlFileOpen(string xlFileName)
    {       
        try
        {       
            if (!File.Exists(xlFileName))
            {
                wf.MessageBox.Show("Excel File does not exists!");
                return false;
            }

            try
            {
                xlApp = (xl.Application)Marshal.GetActiveObject("Excel.Application");
            }
            catch (Exception ex)
            {
                return false;
            }

            foreach (xl.Workbook wb in xlApp.Workbooks)
            {
                if (wb.FullName == xlFileName)
                {
                    xlWb = wb;
                    return true;
                }
            }

            return false;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public static void GetXlSheet(string xlFileName,
                                    string xlSheetName)
    {
        try
        {
            if (!File.Exists(xlFileName))
            {
                wf.MessageBox.Show("Excel File does not exists!");
                return false;
            }

            xlApp = (xl.Application)Marshal.GetActiveObject("Excel.Application");
            foreach (xl.Workbook wb in xlApp.Workbooks)
            {
                if (wb.FullName == xlFileName)
                {
                    if (!xlWb
                        .Sheets
                        .Cast<xl.Worksheet>()
                        .Select(s => s.Name)
                        .Contains(xlSheetName))
                    {
                        wf.MessageBox.Show("Sheet name does not exist in the Excel workbook!");
                        return;
                    }
                    xlWs = xlWb.Sheets[xlSheetName];
                }
            }
        }
        catch (Exception ex)
        {
            // catch errors
        }
    }   
}

Sesame

Answer 5

To add to Armbrat's solution; this worked for me:

// To use currently running instance of Excel 
Excel.Application objApp = 
(Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
    Excel.Workbooks objBooks;    
    Excel._Workbook objBook;
            
objBooks = objApp.Workbooks;    // Adds to the workbooks collection - objBooks.Count will now == 1

objBook = objBooks["Name of workbook"]; // No filename extension, just the name shown in the workbook window title bar

Levin

Answer 6

i know its old question but i just post my solution my solution was

1. do "Marshal.GetActiveObject" at excel application object
2. if there is no workbook found
3. then, release excel application hwnd and do get active object again i hope it helpful

Skillful

Answer 7

// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

// see the excel sheet behind the program
app.Visible = true;

// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;

// changing the name of active sheet
worksheet.Name = "Exported from gridview";

try
{
    // storing header part in Excel
    for (int i = 1; i < dgvRESULTS.Columns.Count + 1; i++)
    {
        worksheet.Cells[1, i] = dgvRESULTS.Columns[i - 1].HeaderText;
        worksheet.Cells[1, i].Interior.Color = System.Drawing.Color.LightYellow;
    }

    // storing Each row and column value to excel sheet
    for (int i = 0; i < dgvRESULTS.Rows.Count - 1; i++)
    {
        for (int j = 0; j < dgvRESULTS.Columns.Count; j++)
        {
            if (dgvRESULTS.Rows[i].Cells[j].Value != null)
            {
                worksheet.Cells[i + 2, j + 1] = dgvRESULTS.Rows[i].Cells[j].Value.ToString();
                //worksheet.Cells[i + 2, j + 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(dgvRESULTS.Rows[i].DefaultCellStyle.BackColor);
            }
            else
            {
                worksheet.Cells[i + 2, j + 1] = "";
            }
        }
    }
}
catch(NullReferenceException ne)
{
}
string filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
// save the application
workbook.SaveAs(filePath +"\\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

// Exit from the application
app.Quit();

Miran

This answer seems to have nothing to do with the question. – Judges