Is it possible to write to an existing Excel file when it is **open** on the desktop?

Question

I am tying to create a code that will write data to an existing Excel file. It will read/write to a file only when the file is closed. If I attempt to write to the file while it is open on the desktop it will not change or save the document. I also cannot close the workbook (using .close()) or quit the application (using .quit()) when the Excel file is opened before or while the code is running.

Is there a way I can write to an excel file while it is open on my desktop and actually show the changes? Or can I at least close an already open file read/write to it, save it and open it again with a C# code? Here is the code I am using; it is a bit unorganized but if you run it you can see what I am essentially trying to do. Please not you must change the genera address in which you are going to save the file in order for the code to work (general address is saved as a string variable called excelsource). the code will first create a file named with todays month and date (MM_YY). it will continue to write to it everytime you initialize the code. If you attempt to write to the file while the newly created file is open no changes will by applied to the file (only writes to the excel file when the file is closed).

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Diagnostics;
using System.Threading;

namespace ConsoleApplication4
{
class Program
{
    static public string excelsource = @"\user\desktop\generic\";
    // excelsource is the "general" address of where excel file wil be saved.
    static public bool truth;
    static public bool truth1;
    static public bool scan_thru = false;
    static public int range2;
    static public int index = 1;
    static Excel.Application excel = new Excel.Application();
    static Excel.Workbook workbook;
    static Excel.Worksheet sheet;
    static Excel.Range range;
    static FileInfo file;

    static void Main(string[] args)
    {
        DateTime current_time = DateTime.Now;
        string file_name = excelsource + current_time.Month.ToString() + "_" + current_time.Year.ToString() + ".xlsx";
        string curfile = file_name;
        truth = File.Exists(curfile);
        // truth determines if file exists if truth == true file exists and does not need to be created, if false a new file is created.
        if (truth == false)
        {
            workbook = excel.Workbooks.Add();
            sheet = workbook.Sheets[1];
            sheet.Name = (current_time.Month.ToString() + "_" + current_time.Day + "_" + current_time.Year);
        }
        else
        {
            file = new FileInfo(file_name);
            truth1 = IsFileinUse(file);
            // truth1 determines if the existing file is open; truth1 == false if the file is currently closed and is true when it is open.
            workbook = excel.Workbooks.Open(file_name);
            sheet = workbook.Sheets[current_time.Month.ToString() + "_" + current_time.Day + "_" + current_time.Year];
            if (truth1 == true)
            {
                excel.Visible = false;
                excel.DisplayAlerts = false;
                workbook.Save();
            }
            while (scan_thru == false & truth1 == false)
                {
                string box = "A" + index.ToString();
                    range = sheet.get_Range(box, Missing.Value);
                    string range1 = range.Text;
                    bool judge = int.TryParse(range1, out range2);
                    if (judge == true)
                    {
                        index++;
                    }
                    else
                    {
                        scan_thru = true;
                    }
                }
                scan_thru = false;

        }
            int i = index;
            while (i < (index + 100) & truth1 == false)
            {
                sheet.Cells[i, "A"].Value2 = i.ToString();                   
                i++;
            }


            if (truth == false)
            {
                workbook.SaveAs(file_name);
            }
            if (truth == true & truth1 == false)
            {
                excel.DisplayAlerts = false;
            }
            index = 1;
            workbook.Close(true);
            excel.Quit();

    }
    protected static bool IsFileinUse(FileInfo file)
    {
        FileStream stream = null;

        try
        {
            stream = file.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
        }
        catch (IOException)
        {
            return true;
        }
        finally
        {
           if (stream != null)
                stream.Close();
        }
        return false;
    }

}

}

Answer 1

There is a very good chance this is not possible to do from a C# application. While a file is open in Excel, it blocks write access from other applications to maintain data integrity within the file that is open. This means you should have read-only access.

Also, force-open and closing any application from an external application is very, very rarely the best method of updating a file. If you are looking to modify data from a currently open file, you should look into learning to write Excel macros.

Edit 1: After clarification of question:

You can't add to an open Excel file. However, if I understand your problem correctly, I think this will solve your problem (I tried it and it works for me):

1. Write to a comma-separated .csv file that you control read/write access to from your C# application.
2. Add a "External Data Source" in your excel spreadsheet "From Text" that references your .csv file. Source: https://support.office.com/en-us/article/Import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba under the "Import a text file by connecting to it" section.
3. Under the "Connections" heading in the "Data" tab, you want to change your "Properties" to refresh every so often or when opening the file. I would uncheck the "Prompt for file name on refresh" box if you won't be changing your filename every time.

Hopefully this works for you. Comment again if you need more clarification.

Answer 2

This thread is extremely old. But I was facing the same problem. I wanted to write on a .csv file while it was open externally on desktop. The problem with this was Excel locking the file for its use in Read/Write mode, hence my application was throwing the "File already in Use Error" . Therefore, to solve this the file has to be opened in Read Only mode when accessed externally on desktop. If you are creating a .csv file from your application create it in Read only mode. And when you want to write on it, convert it to Read-Write mode for that instance and convert it back to Read Only after writing to it. Thus you can keep the .csv file open in the background which would display all the data. However, it has to reopened to get updated data you might have written to the file while it was open in the background. Code I used to convert to read/write and read-only:

// Convert to Read-Write
File.SetAttributes(FileName, File.GetAttributes(FileName) & ~FileAttributes.ReadOnly);                
// Write to the .csv File
// Convert back to Read only
File.SetAttributes(FileName, System.IO.FileAttributes.ReadOnly);