none
Read values from an excel cell with addin formula using Excel automation & C#

    Question

  • we are trying to retrieve a calculated value from a cell which has add-In (VBA) formulas in it. The sample add-in "myUtilityl.xla" is working properly in excel. It retrieves value for the addin function =ISOWEEKNUM(F9). But we are unable to retrieve the value in our application using C# & Microsoft Object Library. The add-In "myUtilityl.xla" is attached to Excel. Environment is VS2010
    I am providing the sample code here.

    string path = @"C:\Test.xls";
    Workbook theWorkbook;
    Worksheet theWorksheet;
    Range readRange;
    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
    theWorkbook = app.Workbooks.Open(path);
    Sheets theSheets = (Sheets)theWorkbook.Worksheets;
    theWorksheet = (Worksheet)theWorkbook.Worksheets.get_Item("Sheet1");
    readRange = theWorksheet.get_Range("B1");
    MessageBox.Show(Convert.ToString(readRange.Value));
    //theWorkbook.Save();
    app.Workbooks.Close();
    I am new to Microsoft Object library. Any help or clue will be very helpful.
    Regards,
    Jinto.

    B1 calling UDF in "myUtility.xla".

    • Moved by Cindy Meister MVPMVP Wednesday, January 04, 2012 2:43 PM excel-specific discussion (From:Visual Studio Tools for Office)
    Wednesday, January 04, 2012 2:34 PM

Answers

  • When you start Excel using automation it skips the automatic opening of addins: so try opening MyUtlity.xla as though it was an ordinary workbook before opening the workbook that uses it.
    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/
    • Marked as answer by Jinto Johnson Friday, January 06, 2012 10:34 AM
    Thursday, January 05, 2012 6:18 PM

All replies

  • Hi Jinto

    Neither your application nor the add-in is using VSTO technology. The better place to ask this question would therefore be the Excel for Developers forum where you'll find the application specialists. Someone there may have already encounterd this issue.

    I'm therefore moving your question to that forum.


    Cindy Meister, VSTO/Word MVP
    Wednesday, January 04, 2012 2:43 PM
  • I'm not usre if the addin is running or the problem is returning the value from the cell.  what error message are you getting?

     

    The C# laguage is prone to giving loats of errors.  I think the problem may be returning the text from the cell.  I think making this minor change may work (adding a 2)./  I also change some of the syntax to match the way I know works on my PC.

    From:

    MessageBox.Show(Convert.ToString(readRange.Value));

    To:

    MessageBox.Show(Convert.ToString(readRange.Value2));

     

    This is how I normally code your example.  I made some minor changes to the Interop refrences

    //change your using definition
    using Excel = Microsoft.Office.Interop.Excel;
    
    
    string path = @"C:\Test.xls";
    Excel.Workbook theWorkbook;
    
    Excel.Range readRange;
    Excel.Application app = new Excel.Application();
    theWorkbook = app.Workbooks.Open(path);
    
    Excel.Worksheet theWorksheet = (Excel.Worksheet)theWorkbook.Worksheets["Sheet1"];
    readRange = theWorksheet.get_Range("B1",Type.Missing);
    MessageBox.Show(Convert.ToString(readRange.Value2));
    //theWorkbook.Save();
    app.Workbooks.Close();
    
    

     


    jdweng
    Wednesday, January 04, 2012 4:04 PM
  • When I tried checking for the installed addins using the following code, it is showing the addin..

    foreach (AddIn addin in app.AddIns)

    {

       MessageBox.Show(addin.FullName);

    }

     

    Also the installed property is true. Another thing that I noticed is cells with inbuilt functions are working. i.e The cell with =SUM(A1:A4) is showing the value.

    Where as the cell with in built function which access cells containing VBA UDF  and the cells with VBA user-defined functions are not displaying values with Range.Value,Range.Value2 and Range.get_Value();


    My environment is VS2010, Office 2010, VSTO 2010. The excel format is 2003.
    Thursday, January 05, 2012 9:01 AM
  • When you start Excel using automation it skips the automatic opening of addins: so try opening MyUtlity.xla as though it was an ordinary workbook before opening the workbook that uses it.
    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/
    • Marked as answer by Jinto Johnson Friday, January 06, 2012 10:34 AM
    Thursday, January 05, 2012 6:18 PM
  • Well Thank you. This worked. Posting the code here..

    string path = @"C:\Test2.xls";
                string xlaPath = @"C:\Test2.xla";
                Workbook theWorkbook;
                Worksheet theWorksheet, theWorksheet2;
                Range readRange;
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                app.Workbooks.Open(xlaPath);

     theWorkbook = app.Workbooks.Open(path); theWorksheet2 = (Worksheet)theWorkbook.Worksheets.get_Item("Sheet2"); theWorksheet2.get_Range("A3").Value = 2; theWorksheet2.get_Range("A4").Value = 3; //theWorkbook.RefreshAll(); theWorksheet = (Worksheet)theWorkbook.Worksheets.get_Item("Sheet1"); readRange = theWorksheet.get_Range("A1"); Console.WriteLine(Convert.ToString(readRange.Value)); Console.ReadLine(); //theWorkbook.Save(); theWorkbook.Close(); app.Workbooks.Close();


    Friday, January 06, 2012 10:35 AM