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?

score 9 · Accepted Answer · answered Jul 13 '11 at 17:03

9

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.
}

answered Jul 13 '11 at 17:03

Armbrat

2,295
1
23
32

1

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? – Jon Erickson Jul 13 '11 at 17:20
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 http://support.microsoft.com/default.aspx?scid=kb;EN-US;317109 – Armbrat Jul 13 '11 at 17:24
1

Also, see this thread for proper cleanup and discussion: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c/159419#159419 – Bryan Crosby Jul 13 '11 at 17:34
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 – Jon Erickson Jul 13 '11 at 18:52
To verify - Did you check how many instances of Excel are opened in task manager? – Armbrat Jul 13 '11 at 18:59
Jon, I am trying to do the same. Did you ever manage to link to an already open worksheet? – ManInMoon Sep 26 '11 at 12:23
I'm also trying to do this, did you ever find a solution? – sigil Dec 10 '12 at 18:47
Looks like as of 2012 no solution for the Workbooks.Count equal to 0? – noztol Jul 26 '16 at 21:27
Still looking for a solution for the he `Workbooks.Count` equal to 0 – Chris R. Apr 25 '17 at 09:14
If the opened Excel is in PROTECTED VIEW you may find this answer helpful https://stackoverflow.com/questions/58458188/why-excelapplication-workbooks-count-is-showing-zero-even-when-i-have-an-excel-w/58458290#58458290 – Beginner Oct 19 '19 at 11:36

score 1 · Answer 2 · answered Oct 24 '18 at 12:25

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";
   }
}

score 0 · Answer 3 · answered Mar 09 '17 at 11:12

 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();
        }

Chandraprakash · Answer 4 · 2019-10-31T16:15:54.263

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
        }
    }   
}

score 0 · Answer 5 · answered Apr 05 '21 at 18:13

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

score 0 · Answer 6 · answered Sep 06 '21 at 08:19

0

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

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

answered Sep 06 '21 at 08:19

김영섭

1

score -1 · Answer 7 · edited Mar 09 '17 at 11:45

// 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();

This answer seems to have nothing to do with the question. – Automate This Apr 17 '17 at 21:25 — Automate This, Apr 17 '17 at 21:25

Accessing an open Excel Workbook in C#

7 Answers7

Linked