0

we are trying to retrieve a calculated value from a cell which has add-In 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 programatically 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.

Community
  • 1
  • 1
jinto johnson
  • 21
  • 1
  • 7

2 Answers2

1

Well Brijesh its working now. The only thing that was missing was that we have to open the xla. app.Workbooks.Open(xlaFilePath); Then it started working.. Thank you very much. i am posting the code here anyways

        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 = 7;
        theWorksheet2.get_Range("A4").Value = 7;
        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();

Above code inputs two values into cells of sheet2 and the VBA UDF calculated value is retrieved.

jinto johnson
  • 21
  • 1
  • 7
0

you may add following in your code sample

        var addins = Application.AddIns.Add(xlaFilePath);

        if (!addins.Installed)
        {
            addins.Installed = true;                  
        }
Brijesh Mishra
  • 2,738
  • 1
  • 21
  • 36
  • 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); } – jinto johnson Jan 04 '12 at 12:26
  • how about installed property? is it true? – Brijesh Mishra Jan 04 '12 at 12:43
  • Installed Property is showing "True". Well My actual scenario is like this. I have a Excel which computes several UDF. I input in one cell in a particular sheet and reads the output from a cell in another sheet which calls the UDF function. In the above case Cell B1 calls the the function("=ISONUMWEEK(F9)"). The cell B1 displays the Value in excel but not in the code. – jinto johnson Jan 04 '12 at 12:59
  • 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(); – jinto johnson Jan 05 '12 at 09:23
  • I tried this, it works absolutely fine for me, what version of Excel are you working on? If it is not office 2010 you may uninstall vsto 2010 and install 2005 or 3.0 as per office version on your local machine – Brijesh Mishra Jan 05 '12 at 10:03
  • The UDF is in a seperate .xla file and the vsto is 2010 and the format of excel is 2003 – jinto johnson Jan 05 '12 at 11:35
  • Well I tried With excel 2010 format(.xlsx) and wrote the UDF in .xlam and added to the excel. still its not working. also can you send me the excel and UDF (in .xla) used for comparing – jinto johnson Jan 05 '12 at 12:19
  • the code works with the excel that you have sent. I cant possibly find a reason why it is not working with the excel that I created using office 2010. Also with the excel that you've sent i tried inserting the value in the cell A1 in sheet2 programatically and display the value. It was not working. – jinto johnson Jan 05 '12 at 16:05
  • can you mail me youur xla text and excel, will have a look at it tommorow – Brijesh Mishra Jan 05 '12 at 16:10
  • i have mailed you the xla and xls.please take a look. also when i added the code – jinto johnson Jan 06 '12 at 08:03
  • i have mailed you the xla and xls.please take a look. also when i added the code: theworksheet2 = (Worksheet)theWorkbook.Worksheets.get_Item("Sheet2"); theworksheet2.Range["A1"].Value = 50;
    did not return the proper result. theWorksheet.Calculate();     – jinto johnson Jan 06 '12 at 08:19
  • Well Brijesh its working now. The only thing that was missing was that we have to open the xla. app.Workbooks.Open(xlaFilePath); Then it started working.. Thank you very much. i am posting the code here anyways – jinto johnson Jan 06 '12 at 08:59