CODEWITHSUNDEEP
c#excelinteropoffice-interop
Craig Key
Craig Key

Reputation: 151

C# Write to an open Excel file using Interop

I am having a ridiculously hard time with this, but I need to be able to connect to an open excel file using Interop and then write to that file.

The file is opened by an outside process and then this application comes in later to write to the workbook. I can get it to open a file and write to the active workbook. but I can't find a way to connect to a previous workbook and write.

I had been using Marshal.GetActiveObject but I will soon be running the application on a computer with multiple files open and need to write to one that will most likely not bee the active one.

Upvotes: 1

Views: 9390

Answers (3)

Slai
Slai

Reputation: 22876

Update :

System.Runtime.InteropServices.Marshal.BindToMoniker can be used to access file that is opened in Excel, or opens it if it is not already opened :

var wb = Marshal.BindToMoniker(@"C:\x.xlsx") as Microsoft.Office.Interop.Excel.Workbook;

https://blogs.msdn.microsoft.com/eric_carter/2009/03/12/attaching-to-an-already-running-office-application-from-your-application-using-getactiveobject-or-bindtomoniker/

Old answer :

GetObject can be used with reference to Microsoft.VisualBasic (it also opens the file if needed) : 

object o = Microsoft.VisualBasic.Interaction.GetObject(@"C:\x.xlsx", "Excel.Application");
var wb = o as Microsoft.Office.Interop.Excel.Workbook; 
if (wb != null) 
{ 
    Microsoft.Office.Interop.Excel.Application xlApp = wb.Application;
    // your code 
}

Reference to Microsoft.VisualBasic can probably be avoided by checking the GetObject source code https://github.com/Microsoft/referencesource/blob/master/Microsoft.VisualBasic/runtime/msvbalib/Interaction.vb#L1039

Upvotes: 5

ASH
ASH

Reputation: 20302

Try it this way.

Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
object misvalue = System.Reflection.Missing.Value;
try
{
    //Start Excel and get Application object.
    oXL = new Microsoft.Office.Interop.Excel.Application();
    oXL.Visible = true;

    //Get a new workbook.
    oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
    oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

    //Add table headers going cell by cell.
    oSheet.Cells[1, 1] = "First Name";
    oSheet.Cells[1, 2] = "Last Name";
    oSheet.Cells[1, 3] = "Full Name";
    oSheet.Cells[1, 4] = "Salary";

    //Format A1:D1 as bold, vertical alignment = center.
    oSheet.get_Range("A1", "D1").Font.Bold = true;
    oSheet.get_Range("A1", "D1").VerticalAlignment =
        Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

    // Create an array to multiple values at once.
    string[,] saNames = new string[5, 2];

    saNames[0, 0] = "John";
    saNames[0, 1] = "Smith";
    saNames[1, 0] = "Tom";

    saNames[4, 1] = "Johnson";

    //Fill A2:B6 with an array of values (First and Last Names).
    oSheet.get_Range("A2", "B6").Value2 = saNames;

    //Fill C2:C6 with a relative formula (=A2 & " " & B2).
    oRng = oSheet.get_Range("C2", "C6");
    oRng.Formula = "=A2 & \" \" & B2";

    //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
    oRng = oSheet.get_Range("D2", "D6");
    oRng.Formula = "=RAND()*100000";
    oRng.NumberFormat = "$0.00";

    //AutoFit columns A:D.
    oRng = oSheet.get_Range("A1", "D1");
    oRng.EntireColumn.AutoFit();

    oXL.Visible = false;
    oXL.UserControl = false;
    oWB.SaveAs("c:\\test\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
        false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    oWB.Close();

From here:

How to write some data to excel file(.xlsx)

Also, check this out.

using System;
using System.Drawing;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                System.Data.OleDb.OleDbConnection MyConnection ;
                System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
                string sql = null;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
                MyConnection.Open();
                myCommand.Connection = MyConnection;
                sql = "Insert into [Sheet1$] (id,name) values('5','e')";
                myCommand.CommandText = sql;
                myCommand.ExecuteNonQuery();
                MyConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.ToString());
            }
        }
   }
}

Upvotes: 0

user6996876
user6996876

Reputation:

This seems to be C# version

using Excel = Microsoft.Office.Interop.Excel;
Excel.Application excel = null;
try
{
  excel = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
}
catch (COMException exc)
{
// ....
}

obviously assuming that the file is opened by an excel application on the same machine.

But the point is that Marshal.GetActiveObject will always return the first instance it finds on ROT (running object table). This is because Office doesn't register new objects. You have to get the application from the child windows, like suggested in this more complicated answer.

Upvotes: 1

Related Questions