c# How to access opened Excel file?

Question

please help me with this stuff!

In my new project I need to access some Excel files. The names of all of them are known. It's easy to open this files and write data in. If excel objects are stored to the list, then it's easy to re-access already opened files while program still running. Please, take a look to the example below.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelTutorial
{
    public partial class Form1 : Form
    {
        List<TextBox> textToExcel = new List<TextBox>();
        List<string> s_excelFiles = new List<string> { "Workbook1.xlsx", "Workbook2.xlsx", "Workbook3.xlsx", "Workbook4.xlsx", "Workbook5.xlsx" };
        List<Excel.Application> l_excelApplication = new List<Excel.Application>();
        List<Excel.Workbook> l_excelWorkbook = new List<Excel.Workbook>();
        List<Excel.Worksheet> l_excelWorksheet = new List<Excel.Worksheet>();
        bool fileOpened = false;

        public Form1()
        {
            InitializeComponent();
            textToExcel.Add(textBox1); textToExcel.Add(textBox2); textToExcel.Add(textBox3);
            textToExcel.Add(textBox4); textToExcel.Add(textBox5);

        }

        private void btn_Unopened_Click(object sender, EventArgs e)
        {

            string path = System.IO.Path.GetDirectoryName(Application.ExecutablePath);

            if (fileOpened != true)
            {

                foreach (var item in s_excelFiles)
                {
                    Excel.Application newExcelApp = new Excel.Application();
                    Excel.Workbook workbook = newExcelApp.Workbooks.Open(path + "\\" + item
                                                 , ReadOnly: false, Editable: true);
                    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet;
                    newExcelApp.Visible = true;
                    ((Excel.Range)worksheet.Cells[1, 1]).Value = textToExcel[s_excelFiles.IndexOf(item)].Text;
                    l_excelApplication.Add(newExcelApp);
                    l_excelWorkbook.Add(workbook);
                    l_excelWorksheet.Add(worksheet);      
                }
                fileOpened = true;
            }
            else
            {
                foreach (var item in l_excelWorksheet)
                {
                    ((Excel.Range)item.Cells[1, 1]).Value = textToExcel[l_excelWorksheet.IndexOf(item)].Text;

                }
            }
        }

        private void btn_Opened_Click(object sender, EventArgs e)
        {

        }
    }
}

However, there is possibility that user has workbook already opened at the moment when program runs. Obviously, Excel sends the message to the user asking for workbook reopening. This could cause data losing. Of course I can check if file is opened before accessing it how it was described here with following code:

try
{
    stream = file.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
}

And then just to ask user to close that file. But I believe there is another way to do it better.

So, my question is: HOW to access file which was already opened by user?

The ExcelTutorial solution is available here!

Answer 1

You can try this code for example to access the current active workbook:

   //Gets Excel and gets Activeworkbook and worksheet
   Excel.Application oXL;
   Excel.Workbook oWB;
   Excel.Worksheet oSheet;
   oXL = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); 
   oXL.Visible = true;
   oWB = (Excel.Workbook)oXL.ActiveWorkbook; 

   docProps = oWB.CustomDocumentProperties

Or maybe a better solution:

using Excel = Microsoft.Office.Interop.Excel;
using ExcelDna.Integration;

// Get the currect application instance
Excel.Application xlapp = (Excel.Application)ExcelDnaUtil.Application;

// Get active workbook
Excel.Workbook wbook = xlapp.ActiveWorkbook;

More info here: Get the current Workbook Object in C#