getting an error trying to use office interop name space

Question

hi I'm not a .net person but i attempted what you see below on an existing spreadsheet and am getting an error before i even try to run. i added the office interop assembly to my references and a using statement as follows:

using Excel = Microsoft.Office.Interop.Excel;

i tried following the instructions at https://www.codeproject.com/Questions/1223797/How-to-edit-the-cell-content-of-excel-file-xlsx-in and https://social.msdn.microsoft.com/Forums/vstudio/en-US/1cc10111-0d3f-4dc9-a928-2fad50f68513/edit-cell-style-with-microsoftofficeinteropexcel?forum=csharpgeneral trying a number of different things including the name of the worksheet where you see the index but to no avail.

my goal is to add a couple of more statements that update a specific cell after i manually add a formula to one of the total cells that uses as a factor the cell that i am updating. and then open the spreadsheet to verify that the formula "kicks in" and the total changes..

            string fullFileName = @"C:\myfilename.xlsx";
            Excel.Application MyApp = new Excel.Application();
            MyApp.Visible = false;

            Excel.Workbook xlWorkBook = MyApp.Workbooks.Open(fullFileName);
            Excel._Worksheet xlWorkSheet = xlWorkBook.Sheets[1];//--<-----one or more types required to compile a dynamic expression
                                                                //        cannot be found.  Are you missing a reference?

            xlWorkBook.Close();
            MyApp.Quit();

            // CLEAN UP.
            System.Runtime.InteropServices.Marshal.ReleaseComObject(MyApp);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);

Answer 1

after adding the reference shown in the link provided by jacjjjun (Microsoft.CSharp), the error disappeared and the script ran. Unfortunately, i got a message box while debugging asking if i wanted to save the spreadsheet so i replied yes. what i learned was that by adding the second line shown below, that problem went away. Anyway i added the first line shown below and proved what i really set out to do which was to show that a formula in the spreadsheet which uses this cell also automatically updated another cell.

xlWorkSheet.Cells[21, 3].Value = 1;
xlWorkBook.Save();

BTW, i did this all inside an ssis script.

Answer 2

You didn't tell us what error you're getting. Is it a compiler error? If so then what line is it complaining about? If it is a runtime error then what line is it failing on and what is the stacktrace?

Answer 3

Below is some code I've used to past html tables or functions into a newly created Excel document. Also, you need to ensure that in your project Dependencies =>COM => Interop.Microsoft.Office.Interop.Excel properties; property settings you have "Embed Interop Types" set to Yes. I would also wrap the whole of the code below in a try/catch and ensure that the Excel instance is killed if an error occurs or you'll have a number of phantom Excel apps running that will have to be stopped via the task manager. Sorry the example is an image but the Azure Firewall kept telling me my request was too long and wouldn't let me send such a lengthy code example.