Click here to Skip to main content
15,885,032 members

Exception from HRESULT: 0x800A03EC (Excel 2007)

rajeshsha18 asked:

Open original thread
I am using a dataset to create an Excel spreadsheet. Here is my code.

VB.NET
Public Sub excelcheck()
        Try
            ' Create the Excel Application object
            Dim excelApp As New ApplicationClass()

            ' Create a new Excel Workbook
            Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing)

            Dim sheetIndex As Integer = 0
            Dim col, row As Integer
            Dim excelSheet As Worksheet

           
            ' Copy each DataTable as a new Sheet

            dsold = Utilities.exceldataset.Copy
            If dsold.Tables(0).Rows.Count > 65000 Then
                Dim i As Integer = 0
                Dim startind As Integer
                Dim endind As Integer
                startind = 0
                endind = 64999
                Dim totalcount As Double
                totalcount = dsold.Tables(0).Rows.Count
                Dim currentcount As Double
                currentcount = Math.Ceiling(totalcount / 65000)
                While (i < currentcount)

                    Dim dt As New System.Data.DataTable("Table" + Convert.ToString(i))
                    dt = splitrecords(dsold.Tables(0), dsold.Tables(0).Rows.Count, startind, endind)

                    If i = 0 Then
                        ds.Tables.Add(dt.Copy)
                    Else
                        dt.TableName = "Table" + Convert.ToString(i)
                        ds.Tables.Add(dt.Copy)
                    End If

                    startind = startind + 65000
                    endind = endind + 65000
                    i = i + 1
                End While
            Else
                ds = dsold.Copy
            End If


            For Each dt As System.Data.DataTable In ds.Tables

                sheetIndex += 1

                ' Copy the DataTable to an object array
                Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object

                ' Copy the column names to the first row of the object array
                For col = 0 To dt.Columns.Count - 1
                    rawData(0, col) = dt.Columns(col).ColumnName
                Next

                ' Copy the values to the object array
                For col = 0 To dt.Columns.Count - 1
                    For row = 0 To dt.Rows.Count - 1
                        rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
                    Next
                Next

                ' Calculate the final column letter
                Dim finalColLetter As String = String.Empty
                Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
                Dim colCharsetLen As Integer = colCharset.Length

                If dt.Columns.Count > colCharsetLen Then
                    finalColLetter = colCharset.Substring( _
                     (dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
                End If

                finalColLetter += colCharset.Substring( _
                  (dt.Columns.Count - 1) Mod colCharsetLen, 1)

                ' Create a new Sheet
                excelSheet = CType( _
                    excelWorkbook.Sheets.Add(excelWorkbook.Sheets(sheetIndex), _
                    Type.Missing, 1, XlSheetType.xlWorksheet), Worksheet)

                excelSheet.Name = dt.TableName

                ' Fast data export to Excel
                Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
                excelSheet.Range(excelRange, Type.Missing).Value2 = rawData

                ' Mark the first row as BOLD
                CType(excelSheet.Rows(1, Type.Missing), Range).Font.Bold = True
                excelSheet = Nothing
            Next
            Dim strDate1 As String = String.Format("{0:dd}", DateTime.Now).ToUpper
            Dim strMonth As String = String.Format("{0:MM}", DateTime.Now).ToUpper()
            Dim stryear As String = String.Format("{0:yyyy}", DateTime.Now).ToUpper()
            StrServerFile = Utilities.Jobid + " " + strDate1 + strMonth + stryear + ".xls"
            ' Save and Close the Workbook
            excelWorkbook.SaveAs("C:\Bcp\" + StrServerFile, XlFileFormat.xlWorkbookNormal, Type.Missing, _
             Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
            excelWorkbook.Close(True, Type.Missing, Type.Missing)
            excelWorkbook = Nothing
            ' Release the Application object
            excelApp.Quit()
            excelApp = Nothing
            insertexcelfile(StrServerFile)
            ' Collect the unreferenced objects
            GC.Collect()
            GC.WaitForPendingFinalizers()

            MessageBox.Show("Excel Report Genearted Sucessfully")
            SetLabelText(False)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

The exception details are:
System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.set_Value2(Object )
at Exceltest.Form1.excelcheck() in D:\exceltest\Exceltest\Form1.vb:line 118
Tags: Visual Basic (Visual Basic.NET 7.x (2002/03)), .NET

Plain Text
ASM
ASP
ASP.NET
BASIC
BAT
C#
C++
COBOL
CoffeeScript
CSS
Dart
dbase
F#
FORTRAN
HTML
Java
Javascript
Kotlin
Lua
MIDL
MSIL
ObjectiveC
Pascal
PERL
PHP
PowerShell
Python
Razor
Ruby
Scala
Shell
SLN
SQL
Swift
T4
Terminal
TypeScript
VB
VBScript
XML
YAML

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the http://www.codeproject.com/info/cpol10.aspx.



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900