[VB.Net]HRESULT : 0x800A03EC with Excel

2016-07-29

Question

_{Friday, July 29, 2016 9:59 AM}

Hello guys,

Thanks to you, I'm slowly solving my problems but now I'm on a problem I can't solve because I don't understand

I'm getting a HRESULT : 0x800A03EC with no more explainations.

The code is like this:

        Dim conts As Excel.Range
        Dim cont As Object
                    If (ouvrirFichierXLSCATIA(nomFichierXLS)) Then

                        xlws = doc.ActiveSheet
                        elemList = xlws.UsedRange
                        conts = xlws.UsedRange.Columns.Item(1)
                        For i = 0 To conts.Count - 1
                            cont = conts.Item(i).Value2

And I've got the error on the last line.
As I checked the error on google, it's telling me that it's taking a too big range.

Any idea to help me?

Thanks for the help though

Best Regards

All replies (6)

_{Friday, July 29, 2016 12:20 PM ✅Answered}

Ok I found the where the error comes from:

http://stackoverflow.com/questions/12714626/exception-from-hresult-0x800a03ec-error

So I changed my loop, from i = 1 to conts.count

_{Friday, July 29, 2016 10:34 AM}

Hello,

The following is an example of working with UsedRange, not done for this question but even so gives you the pattern to follow for reading data. In this case the data is read into a DataTable but could be any container that you want e.g. a strong typed class, a Dictionary etc.

Option Strict On
Imports Excel = Microsoft.Office.Interop.Excel
Module ExcelDemoIteratingData_2
    Public Sub DemoGettingDates()


        Dim dt As DataTable = OpenExcelAndIterate(
            IO.Path.Combine(
                AppDomain.CurrentDomain.BaseDirectory,
                "GetDatesFromB.xlsx"),
            "Sheet1",
            "B1",
            "B10")

        Dim SomeDate As Date = #12/1/2013#

        Dim Results =
            (
                From T In dt
                Where Not IsDBNull(T.Item("SomeDate")) AndAlso T.Field(Of Date)("SomeDate") = SomeDate
                Select T
            ).ToList

        If Results.Count > 0 Then
            For Each row As DataRow In Results
                Console.WriteLine("Row [{0}] Value [{1}]",
                                  row.Field(Of Integer)("Identifier"),
                                  row.Field(Of Date)("SomeDate").ToShortDateString)
            Next
        End If

    End Sub
    Public Function OpenExcelAndIterate(
        ByVal FileName As String,
        ByVal SheetName As String,
        ByVal StartCell As String,
        ByVal EndCell As String) As DataTable

        Dim dt As New DataTable

        If IO.File.Exists(FileName) Then

            Dim Proceed As Boolean = False
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)

            xlApp.Visible = False

            xlWorkSheets = xlWorkBook.Sheets


            '
            ' For/Next finds our sheet
            '
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next

            If Proceed Then

                dt.Columns.AddRange(
                    New DataColumn() _
                    {
                        New DataColumn With {.ColumnName = "Identifier", .DataType = GetType(Int32), .AutoIncrement = True, .AutoIncrementSeed = 1},
                        New DataColumn With {.ColumnName = "SomeDate", .DataType = GetType(Date)}
                    }
                )

                Dim xlUsedRange = xlWorkSheet.Range(StartCell, EndCell)

                Try

                    Dim ExcelArray(,) As Object = CType(xlUsedRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault), Object(,))

                    If ExcelArray IsNot Nothing Then
                        ' Get bounds of the array.
                        Dim bound0 As Integer = ExcelArray.GetUpperBound(0)
                        Dim bound1 As Integer = ExcelArray.GetUpperBound(1)

                        For j As Integer = 1 To bound0
                            If (ExcelArray(j, 1) IsNot Nothing) Then
                                dt.Rows.Add(New Object() {Nothing, ExcelArray(j, 1)})
                            Else
                                dt.Rows.Add(New Object() {Nothing, Nothing})
                            End If
                        Next
                    End If
                Finally
                    ReleaseComObject(xlUsedRange)
                End Try

            Else
                MessageBox.Show(SheetName & " not found.")
            End If

            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlCells)
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        Else
            MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
        End If

        Return dt

    End Function
    Private Sub ReleaseComObject(ByVal sender As Object)
        Try
            If sender IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sender)
                sender = Nothing
            End If
        Catch ex As Exception
            sender = Nothing
        End Try
    End Sub
End Module

There is a good deal of code where the reasoning is to ensure objects are properly disposed of. See this thread for more on disposing of objects.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Friday, July 29, 2016 11:02 AM}

Okay, I know how to use UsedRange, but I'm asking about the Error, and why it is showing...

_{Friday, July 29, 2016 11:16 AM}

Okay, I know how to use UsedRange, but I'm asking about the Error, and why it is showing...

There is no one definitive reason for this so the best method to determine the issue is to set a break point prior to the line that is throwing the exception, step through the code and examine objects to see if they are in their proper state. Also, is the iterator used, are you sure it starts with 0 rather than 1? Lastly, I have done alot with Excel automation and never need to use Value2, that might be an problem.

Last resort, load the file into Microsoft OneDrive and post the link back here, I will look at it tonight.

_{Friday, July 29, 2016 11:51 AM}

If I put the "Value2" in commentary, I still have the error showing, any idea?

_{Friday, July 29, 2016 12:50 PM}

I indicated you should had used 1 as the Iterative in my last reply so there was no need to look past that.

שתף באמצעות

[VB.Net]HRESULT : 0x800A03EC with Excel

Question

All replies (6)

משאבים נוספים