locked
Interop Excel.GetRange does not work on some PCs RRS feed

  • Question

  • We are developing an Excel Interop based AddIn.
    We are using Interop to access data in Excel.

    colorRange = worksheet.Range["A1:A1,A2:A2,A3:A3"] as Range; // (Examplary ranges).


    On Our (Developers) PC it works perfectly fine. On Some - not all of them client PC it does not work it crashes with:

    Exception from HRESULT: 0x800A03EC at Microsoft.Office.Interop.Excel._Worksheet.get_Range
    This code is executed on some button click, so usually all addin works perfectly, and accessing Interop function works also.

    Everywhere Office is Installed

    Addin works and worked for people quite well. And we are doing some heavy lifting within the addin - basically its business scale application within an excel. We had no problem so far. However we kind of functionality which was not used on client side - just server side.

    AddIn is loaded on 'StartUp' - this functionality is working on demand.
    We never fetched the data using the .Range["A1:A2...."] - we always did it using the workshet.Cells or get_values methods.
    Any Idea what could be happening there?

    • Edited by ntszar Tuesday, December 22, 2015 10:27 AM
    • Moved by David_JunFeng Friday, December 25, 2015 3:16 AM
    Monday, December 21, 2015 4:05 PM

Answers

  • I suggest you post this question to Excel for Developers
    • Marked as answer by ntszar Monday, December 21, 2015 4:56 PM
    Monday, December 21, 2015 4:52 PM
  • >>>As a workaround for now we are using NamedRanges instead, but its a burden on our code - we have to invoke Excel macro in order for VBA to name the range that we want to get.. so we are sending this "A1:A1,..." string to VBA and then VBA uses Names.Add(Range[".."]) and back in C# we use Application.Range("name").<<<

    According to your description, you could use Worksheet.Names property that a collection of all the Name objects in the Worksheet. Each Name object represents a defined name for a range of cells.
    You could refer to below code:

    string sheetName = wsh.Name;
    wsh.Names.Add("TestRange", "="+sheetName+"!$A$1:$A$2:$A$3");
    
    foreach (Excel.Range r in wsh.Range["TestRange"])
    {
         MessageBox.Show(r.Text);
    }

    For more information, click here to refer about Worksheet.Names Property


    Friday, December 25, 2015 5:31 AM

All replies

  • Are the PIA's installed on the machines where the crashes take place?
    Monday, December 21, 2015 4:32 PM
  • Yes. Everywhere there is Office Installed.

    Addin works and worked for people quite well. And we are doing some heavy lifting within the addin - basically its business scale application within an excel. We had no problem so far. However we kind of functionality which was not used on client side - just server side.

    AddIn is loaded on 'StartUp' - this functionality is working on demand.

    We never fetched the data using the .Range["A1:A2...."] - we always did it using the workshet.Cells or get_values methods.


    • Edited by ntszar Monday, December 21, 2015 4:46 PM
    Monday, December 21, 2015 4:44 PM
  • I suggest you post this question to Excel for Developers
    • Marked as answer by ntszar Monday, December 21, 2015 4:56 PM
    Monday, December 21, 2015 4:52 PM
  • OOps. 

    I did not notice that it was outlook dev. 

    I will move the question, thank you.

    Monday, December 21, 2015 4:56 PM
  • Hello,

    What error message do you get? Where and when do you run the code?

    Please remember that Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

    If you are building a solution that runs in a server-side context, you should try to use components that have been made safe for unattended execution. Or, you should try to find alternatives that allow at least part of the code to run client-side. If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully. Additionally, you will be taking risks with the stability of your overall solution. Read more about that in the Considerations for server-side Automation of Office article.

    Monday, December 21, 2015 7:34 PM
  • Hello,

    What error message do you get? Where and when do you run the code?

    Please remember that Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

    If you are building a solution that runs in a server-side context, you should try to use components that have been made safe for unattended execution. Or, you should try to find alternatives that allow at least part of the code to run client-side. If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully. Additionally, you will be taking risks with the stability of your overall solution. Read more about that in the Considerations for server-side Automation of Office article.

    Monday, December 21, 2015 7:34 PM
  • I am getting general error

    Exception from HRESULT: 0x800A03EC and from stacktrace the top function is: Microsoft.Office.Interop.Excel._Worksheet.get_Range

    You misunderstood the server side ( its complicated.. :) )- its normal desktop application. We use Excel.DNA to communicate between VBA and C# - but just for that, in other cases we use Excel.Interop. Everything is run on Excel thread - Invocation starts on Excel.

    I would like to emphasize that its working perfectly fine on few machines and some machines just show this behavior - Error on get_Range.

    The flow is like this: User clicks the button, and then we use Excel Interop to reference some Excel Range, and for example set their color or change value via the reference.

    Monday, December 21, 2015 10:07 PM
  • >>>Exception from HRESULT: 0x800A03EC and from stacktrace the top function is: Microsoft.Office.Interop.Excel._Worksheet.get_Range

    According to your description, I have create one Excel 2013 Workbook project to try to reproduce your issue, unfortunately, I can't. So I suggest that you could find out what difference is between working machine and no working machine, otherwise you could make sure VBA code can work fine on machine get Error on get_Range, you could refer to below code:

    Dim rng As Range
      Set rng = Range("A1:A1,A2:A2,A3:A3")
      For Each r In rng
        Debug.Print r.Text
    Next r 

    Tuesday, December 22, 2015 6:35 AM
  • Hello,

    Within Excel Application using VBA it works Perfectly on all machines. the problems happens when we use Excel.Interop - COM to access Excel.

    And How can we find related difference  between these machines?

    Tuesday, December 22, 2015 10:31 AM
  • Why this was merged into Outlook for Developer? When I just asked it for the 2nd time on Excel for Developers ( there is no delete question button ).

    It looks completely awful. My question is ruined ( thanks David JunFeng). I've reasked the question on Excel For Developer forum and you move it back here? C'mon.. and you merged it wrong.. duplicated posts etc.. 

    Tuesday, December 22, 2015 2:29 PM
  • Try to use the late biding technology for calling properties and methods that fire exceptions. See Type.InvokeMember for more information.

    Also I'd suggest setting the culture information before calling Excel methods or accessing properties as described in the following article:

    HowTo: Fix “Old format or invalid type library” error (0x80028018)

    Wednesday, December 23, 2015 9:04 AM
  • Thanks for answer.

    We are already enforcing the culture information as it was essential to use interop on ( Macro invocation did not work without it.)

    As a workaround for now we are using NamedRanges instead, but its a burden on our code - we have to invoke Excel macro in order for VBA to name the range that we want to get.. so we are sending this "A1:A1,..." string to VBA and then VBA uses Names.Add(Range[".."]) and back in C# we use Application.Range("name").

    But its not a good long term solution as it requires macros.

    We will attempt to use the Type.Invoke Member, however after holidays as we no loner have working hours... I will respond back in 2 weeks.

    Wednesday, December 23, 2015 6:37 PM
  • Let me know your results in any case.
    Thursday, December 24, 2015 12:08 PM
  • >>>As a workaround for now we are using NamedRanges instead, but its a burden on our code - we have to invoke Excel macro in order for VBA to name the range that we want to get.. so we are sending this "A1:A1,..." string to VBA and then VBA uses Names.Add(Range[".."]) and back in C# we use Application.Range("name").<<<

    According to your description, you could use Worksheet.Names property that a collection of all the Name objects in the Worksheet. Each Name object represents a defined name for a range of cells.
    You could refer to below code:

    string sheetName = wsh.Name;
    wsh.Names.Add("TestRange", "="+sheetName+"!$A$1:$A$2:$A$3");
    
    foreach (Excel.Range r in wsh.Range["TestRange"])
    {
         MessageBox.Show(r.Text);
    }

    For more information, click here to refer about Worksheet.Names Property


    Friday, December 25, 2015 5:31 AM