none
System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC using Range RRS feed

  • Question

  • I am writing a vsto addin (office 2013). I am stuck with setting the source data for a chart object. The code below works fine on a computer with locale "en-US", but xlsheet.Range("$B$1:$B$13,$D$1:$E$13") generates the COM exception on another computer with locale "nl-NL". Strangely, xlsheet.Range("A1:E13"), which I use earlier, does not give the same error on either language versions. Simply setting xlrange = xlsheet.Range("$B$1:$B$13,$D$1:$E$13") in the "nl-NL" locale generates the error.

               ' insert a bar chart
                xlsheet.Shapes.AddChart2(Style:=294, XlChartType:=Excel.XlChartType.xl3DColumnStacked)
                xlchart = xlsheet.ChartObjects(1)
                Try
                    xlchart.Chart.SetSourceData(Source:=xlsheet.Range("$B$1:$B$13,$D$1:$E$13"))
                Catch ex As Exception
                    MessageBox.Show(ex.ToString)
                End Try

                xlchart.Chart.ChartTitle.Text = "Directe en indirecte tijd/maand"

    I tried using the suggested solution, i.e. adding:

            Dim oldCI As System.Globalization.CultureInfo
            oldCI = System.Threading.Thread.CurrentThread.CurrentCulture
            System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

    before making the call, but although I verfied that the CultureInfo.Name was "en-US" when making the call to xlsheet.Range, the error message remained. Any suggestions to get around this would be greatly appreciated.

    Regards,

    Niels van Strien

    Saturday, May 21, 2016 11:07 PM

Answers

  • Deepak,

    It has been close to two years your senior engineers have been working hard on this issue.  I would like to share my solution for Niel's issue.   

    This should take a significant load off of the resources Microsoft has been dedicating to this very problem.  The senior engineers have been probably working long hours trying to solve this.

    I am sure they would have responded by now with a solution, or at least a notification of the incredible difficulty this problem has posed the senior engineers for the past two years.

    Issue Solution:

    'Instead of using a cell address string, which we now know is incompatible with certain languages,  we will use the Cell and numerical indexes to define our range

    'The trick is how to define a range if it not contiguous.  Here is your original line of code:

    'xlchart.Chart.SetSourceData(Source:=xlsheet.Range("$B$1:$B$13,$D$1:$E$13"))
       

    'Cell addresses "$B$1:$C$13" would be contiguous, but "$B$1:$B$13,$D$1:$E$13" is not.  This may have failed in Dutch or 'Turkish even if the range was contiguous or not,  the problem is how to add non-contiguous ranges together.

    'Luckily there is the 'Union' method, where multiple ranges can be combined.  This lets us use the Cells(rowindex,colindex) to 'define the range, thus getting around the language issue with string addresses:

    'Define the first range:

    Dim NewSeriesRange As Excel.Range = xlsheet.range(xlsheet.Cells(1,2),xlsheet.Cells(13,2))

    'Now we use 'Union' to combine the first range ("$B$1:$B$13") and the second range ("$D$1:$E$13"):

    NewSeriesRange = xlApp.Union(NewSeriesRange, xlsheet.Range(Cells(1, 4), xlsheet.Cells(13, 5)))

    'Finally the SetSourceData method should now work:

    xlApp.ActiveChart.SetSourceData(NewSeriesRange)

    Deepak,  I hope your senior engineers can rest as they can now concentrate on other less pressing issues.

    We want to thank Microsoft for its prompt and timely response to this matter.

    Regards,

    Jeff Weinmann

    Technical Services Manager 



    • Marked as answer by NM van Strien Wednesday, March 28, 2018 2:32 PM
    Wednesday, March 28, 2018 2:03 PM

All replies

  • Hi NM van Strien,

    did you try to pass the range in different way? is it giving the same error?

    it is also possible that worksheet get corrupted and because of that you get this error.

    so in that case you can try to make a new copy of that.

    please visit the link below it will also help you to solve this kind of issue.

    Locale issue

    BUG: "Old format or invalid type library" error when automating Excel

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 23, 2016 7:37 AM
    Moderator
  • Hi Deepak,

    Just some additional background info.

    - I am working in Visual Studio 2015 community edition.

    - I am working on a Word 2013 VSTO solution, but am writing some data to excel.

    - Both the EN and NL version of Excel are Excel 2013. The excel file type that I am working on is .xlsb (binary).

    I have a try/catch block around the line of code that doesn't work. This has shown me that the chart is in fact added to the sheet (in both the NL and EN version) with Source Range A1:E13. When skipped over the xlchart.Chart.SetSourceData line in the NL office version (again the EN version executes this without error), the title of the chart is set without problem (in the NL version), suggesting that the sheet is not corrupted since the chart object is accessible. I can also open the file manually without error, see the chart, manipulate the data in the sheet, etc.

    I have tried alternatives way to set the Range argument:

    xlchart.Chart.SetSourceData(.Range("$B$1:$B$13,$D$1:$E$13")) '(The .Range is valid because it is in a With block that refers to xlApp (= New Excel.Application, which I declare at the opening of the Sub)) I also left out Source=:

    xlchart.Chart.SetSourceData(.Range("B1:B13,D1:E13")) 'leaving out the absolute references $

    xlchart.Chart.SetSourceData(.Range("per_maand!B1:B13,per_maand!D1:E13")) ' added the sheet name, although the sheet was already set as the active sheet.

    I have also tried recording a VBA macro in the Dutch language version and 'translate' that to vb.net. This would give me something like:

    xlchart.Chart.SetSourceData(Source= .Range("per_maand!$B$1:$B$13,per_maand!$D$1:$E$13"))

    Do you have any suggestions what else I could try as different ways to define the Range? I tried setting the range to

    xlrange = xlsheet.Range("$B$1:$B$13,$D$1:$E$13") and then passing xlrange as an argument, but then the code fails on setting xlrange with the same error as before. The strange thing is that I can set xlrange to xlheet.Range("$A$1:$A$2") without problem in the Dutch Language version. But it seems to fail at a non-continuous range (column C is skipped).

    I also tried solution:

    Create a 1033 directory under Microsoft Office\Office11. Then, copy excel.exe to the 1033 directory, and rename it as xllex.dll.

    Although with office 2013 this solution is located in C:\Program Files\Microsoft Office 15\root\office15. It already had a 1033 subfolder on both installations, to which I added xllex.dll as suggested. (Although I was in doubt if the source excel.exe should come from an English office distro.

    I tried using the InvokeMember solution, but am unsure how to properly implement that. So far I have failed at it.

    As you can see I am stuck (((

    Any further thoughts on the matter are highly appreciated.

    Regards,

    Niels van Strien

    Monday, May 23, 2016 9:32 AM
  • Hi NM van Strien,

    I have forward this issue to our senior Engineers to look in to this issue and provide appropriate solution to you.

    so it will take some time to solve. As they have any updates regarding this issue they will let you know.

    Thanks for your understanding.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 2, 2016 6:34 AM
    Moderator
  • Thank you!
    Thursday, June 2, 2016 7:20 PM
  • Hi Deepak,

    Today I discovered that the problem can be reproduced on an English language version by switching the Thousands and Decimal separators (thereby mimicking a Dutch Language version).

    I hope this will contribute to finding a solution.

    Best,

    Niels

    Friday, July 1, 2016 7:56 AM
  • Hi NM van Strien,

    Thank you for updating the case status.

    Now the senior engineers will going to handle this issue as I already mentioned this in my last post.

    I had escalate this case to them.

    so they will view your update and for sure it will contribute to find the solution.

    As I told you it will take more time then the normal cases so please be patient.

    They will inform you regarding the solution.

    Thank for your understanding.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 4, 2016 9:13 AM
    Moderator
  • Deepak,

    It has been close to two years your senior engineers have been working hard on this issue.  I would like to share my solution for Niel's issue.   

    This should take a significant load off of the resources Microsoft has been dedicating to this very problem.  The senior engineers have been probably working long hours trying to solve this.

    I am sure they would have responded by now with a solution, or at least a notification of the incredible difficulty this problem has posed the senior engineers for the past two years.

    Issue Solution:

    'Instead of using a cell address string, which we now know is incompatible with certain languages,  we will use the Cell and numerical indexes to define our range

    'The trick is how to define a range if it not contiguous.  Here is your original line of code:

    'xlchart.Chart.SetSourceData(Source:=xlsheet.Range("$B$1:$B$13,$D$1:$E$13"))
       

    'Cell addresses "$B$1:$C$13" would be contiguous, but "$B$1:$B$13,$D$1:$E$13" is not.  This may have failed in Dutch or 'Turkish even if the range was contiguous or not,  the problem is how to add non-contiguous ranges together.

    'Luckily there is the 'Union' method, where multiple ranges can be combined.  This lets us use the Cells(rowindex,colindex) to 'define the range, thus getting around the language issue with string addresses:

    'Define the first range:

    Dim NewSeriesRange As Excel.Range = xlsheet.range(xlsheet.Cells(1,2),xlsheet.Cells(13,2))

    'Now we use 'Union' to combine the first range ("$B$1:$B$13") and the second range ("$D$1:$E$13"):

    NewSeriesRange = xlApp.Union(NewSeriesRange, xlsheet.Range(Cells(1, 4), xlsheet.Cells(13, 5)))

    'Finally the SetSourceData method should now work:

    xlApp.ActiveChart.SetSourceData(NewSeriesRange)

    Deepak,  I hope your senior engineers can rest as they can now concentrate on other less pressing issues.

    We want to thank Microsoft for its prompt and timely response to this matter.

    Regards,

    Jeff Weinmann

    Technical Services Manager 



    • Marked as answer by NM van Strien Wednesday, March 28, 2018 2:32 PM
    Wednesday, March 28, 2018 2:03 PM